Connecting Oracle to PostgreSQL

The following instructions show you how to connect Oracle on Linux to PostgreSQL. For further information about DG4ODBC, refer to our DG4ODBC tutorial.

1. Download the 64-bit PostgreSQL ODBC driver for Linux.
2. Install and license the PostgreSQL ODBC driver on the machine where Oracle is installed.

   For installation instructions, refer to the ODBC driver documentation.

   Note You need the unixODBC Driver Manager installed on your machine. The Easysoft distribution includes a version of the unixODBC Driver Manager that the Easysoft PostgreSQL ODBC driver has been tested with. The Easysoft driver setup program gives you the option to install unixODBC.

3. Create an ODBC data source in /etc/odbc.ini that connects to the PostgreSQL database you want to access from Oracle. For example:

   [POSTGRES_SAMPLE]
   Driver=Easysoft ODBC-Postgres Server
   Description=Easysoft ODBC-Postgres Server
   Server=192.0.2.1
   Port=5432
   Database=employees
   User=postgres
   Password=p4550rd
   Logging=No
   LogFile=
   Encrypt=No

4. Use isql to test the new data source. For example:

   cd /usr/local/easysoft/unixODBC/bin
   ./isql.sh -v POSTGRESQL_SAMPLE

   At the prompt, enter help to display a list of tables. To exit, press Return in an empty prompt line.

   If you're unable to connect, refer to this article for assistance.

5. Create a DG4ODBC init file. To do this, change to the hs/admin subdirectory. For example:

   cd $ORACLE_HOME/product/11.2.0/xe/hs/admin

   Create a copy of the file initdg4odbc.ora. Name the new file initpostgresql.ora.

6. Ensure these parameters and values are present in your init file:

   HS_FDS_CONNECT_INFO = my_postgresql_odbc_dsn

   Replace my_postgresql_odbc_dsn with the name of a PostgreSQL ODBC driver data source that connects to the target PostgreSQL server. For example

   HS_FDS_CONNECT_INFO = "POSTGRES_SAMPLE"

7. Comment out the line that enables DG4ODBC tracing. For example:

   #HS_FDS_TRACE_LEVEL = <trace_level>

8. Add an entry to listener.ora that creates a SID_NAME for DG4ODBC. For example:

   SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC=
        (SID_NAME=postgresql)
        (ORACLE_HOME=$ORACLE_HOME)
        (PROGRAM=dg4odbc)
      )
    )

9. Add a DG4ODBC entry to tnsnames.ora that specifies the SID_NAME created in the previous step. For example:

   POSTGRESQL =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_host)(PORT = 1521))
       (CONNECT_DATA =
         (SID = postgresql)
       )
       (HS = OK)
     )

   Replace oracle_host with the host name of your Oracle machine.

10. Start (or restart) the Oracle Listener. For example:

    lsnrctl stop
    lsnrctl start

11. Connect to your Oracle database in SQL*Plus.
12. In SQL*Plus, create a database link for the target PostgreSQL server. For example:

    CREATE PUBLIC DATABASE LINK postgresqllink CONNECT TO
    "my_postgresql_user" IDENTIFIED by "my_postgresql_password" USING 'postgresql';
    SELECT * FROM employees@postgresqllink

Notes

• If you have problems connecting to PostgreSQL from Oracle, enable DG4ODBC tracing and check the trace files written to the hs/log directory. To enable DG4ODBC tracing, add the line HS_FDS_TRACE_LEVEL = DEBUG to initpostgresql.ora and then start or restart the Oracle listener. If the log directory does not exist, create it. For example:

  mkdir log
  chmod +w log

• If you get error "ORA-00997: illegal use of LONG datatype," add one of the following entries to your ODBC data source:

  DisguiseLong=1 # For non-Wide PostgreSQL long columns
  LimitLong=size in bytes # If this value is too big you will get ORA-00997

  –Or–

  DisguiseWlong=1
  LimitLong=size in bytes# If this value is too big you will get ORA-00997

• If you get "ORA-28562: Heterogeneous Services data truncation error," when working with PostgreSQL data in Oracle, we may have a workaround for you, but you need to understand the implications of using it. For more information, contact the Easysoft Support team ( ).