SUMMARY: This article discusses heterogeneous connections and how to set  up a connection from Oracle to PostgreSQL using one.

 

In this post, we are going to see what a heterogeneous connection is and how to set up a connection from Oracle to PostgreSQL via ODBC (open database connectivity).

Nowadays, lots of companies are using a mix of relational database management systems (RDBMS) on their data center — for example, some instances are running on Oracle and other instances are running on PostgreSQL, and if they want to access data (or even migrate) from X database (e.g., PostgreSQL) to Y database (e.g., Oracle), they’ll need to do it using a heterogeneous connection. Heterogeneous connections allow us to query data from non-Oracle databases using SQL.

System configuration 

Centos 7.X (where X is the latest minor release) 

1. Install PostgreSQL v11

Download the Repository package from yum.postgresql.org for Centos7 64bit machine.

Connect to root user and perform these steps: 

  • rpm -ivh pgdg-redhat-repo-latest.noarch.rpm
  • yum clean all 
  • yum makecache
  • yum install epel-release
  • yum install postgresql11*
  •  yum install unixODBC*

 

1.1. Initialize the cluster and start the service 

You need to connect to a non-root user to perform the following steps: 

-bash-4.2$ ./initdb -D /tmp/data

-bash-4.2$ ./pg_ctl -D /tmp/data/ start

 

1.2 Create a database user / table

-bash-4.2$ ./psql -p 5432 postgres

psql (11.6)

Type "help" for help.



postgres=# create user a with password 'a'  superuser;

CREATE ROLE

postgres=# create table t(n int);

CREATE TABLE

postgres=# insert into t values (1);

INSERT 0 1

postgres=# select * from t;

 n 

---

 1

(1 row)

 

2. Install Oracle 11g 

2.1. Download Oracle 11g Express edition (rpm) from Oracle website 

  •  unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
  •  rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm 
  •  /etc/init.d/oracle-xe configure 

 

2.2. Connect to Oracle database and create a Super user

[root@tushar-ldap-docker bin]# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/

[root@tushar-ldap-docker bin]# ./sqlplus sys/edb@xe as sysdba



SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 4 14:43:47 2020



Copyright (c) 1982, 2011, Oracle.  All rights reserved.



Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production



SQL> create user dblink identified by dblink ; 



User created.



SQL> grant dba to dblink;



Grant succeeded.

 

3. Edit the “odbcinst.ini” file

The file resides in the /etc directory. Add the following entry: 

[pg]

Description = PG

Driver          = /usr/pgsql-11/lib/psqlodbc.so

Setup           = /usr/lib64/libodbcpsqlS.so

Driver64        = /usr/pgsql-11/lib/psqlodbc.so

Setup64         = /usr/lib64/libodbcpsqlS.so

FileUsage       = 1

 

4. Run “isql -v pg”   

This will test DSN’s connectivity for PostgreSQL database.

[root@tushar-ldap-docker bin]# isql -v pg

[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified

[ISQL]ERROR: Could not SQLConnect

 

5. Edit the ‘odbc.ini’ file. 

This file resides in the /etc directory. Add the following entry: 

[pg]

Description = pg

Driver = /usr/pgsql-11/lib/psqlodbc.so

ServerName = localhost

Username = a

Password = a

Port = 5432

Database = postgres

Trace = yes

TraceFile = /tmp/odbctrace.txt

 

Note: If this file is not present then you can create this file your own.

 

6. Run “isql” 

This will test DSN’s connectivity for PostgreSQL database.

[root@tushar-ldap-docker bin]# isql -v pg

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL> select * from t;

+------------+

| n          |

+------------+

| 1          |

+------------+

SQLRowCount returns 1

1 rows fetched

 

7. Create a “initpg.ora” file 

This should reside under $ORACLE_HOME/hs/admin.

[root@tushar-ldap-docker bin]# cd /u01/app/oracle/product/11.2.0/xe/hs/admin/

[root@tushar-ldap-docker admin]# touch initpg.ora 

 

Add the values below in the “initpg.ora” file:

HS_FDS_CONNECT_INFO = pg

HS_FDS_TRACE_LEVEL = ON

HS_FDS_SHAREABLE_NAME = /usr/pgsql-11/lib/psqlodbc.so

HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9

set ODBCINI=/etc/odbc.ini

 

Save this file. 

 

8. Edit the “listener.ora” file 

This should reside under $ORACLE_HOME/network/admin.

Add these following contents for the  SID_LIST_LISTENER parameter:

  (SID_DESC =

      (SID_NAME = pg)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)

      (PROGRAM = dg4odbc)

    )

 

Now, the “listener.ora” file should look like this:

“listener.ora” Network Configuration File:

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (SID_NAME = pg)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)

      (PROGRAM = dg4odbc)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))

      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )

  )



DEFAULT_SERVICE_LISTENER = (XE)

 

9. Edit the “tnsnames.ora” file 

This should reside under $ORACLE_HOME/network/admin.

Add the following contents:

pg =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SID = pg)

    )

   (HS=OK)

  )

 

Now, the “tnsnames.ora” file should look like this:

“tnsnames.ora” Network Configuration File:

XE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = XE)

    )

  )



pg =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SID = pg)

    )

   (HS=OK)

  )



EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

 

10. Reload the listener 

Connect to “lsnrctl” and fire “reload” command:

[root@tushar-ldap-docker bin]# pwd

/u01/app/oracle/product/11.2.0/xe/bin

[root@tushar-ldap-docker bin]# ./lsnrctl 



LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 04-FEB-2020 16:58:35



Copyright (c) 1991, 2011, Oracle.  All rights reserved.



Welcome to LSNRCTL, type "help" for information.



LSNRCTL> reload

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))

The command completed successfully

LSNRCTL> exit

 

11.  Verify using “tnsping” 

“tnsping” is an Oracle utility to test if the listener is available or not. 

[root@tushar-ldap-docker bin]# ./tnsping pg



TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 04-FEB-2020 17:01:03



Copyright (c) 1997, 2011, Oracle.  All rights reserved.



Used parameter files:



Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1521)) (CONNECT_DATA = (SID = pg)) (HS=OK))

OK (10 msec)

 

12. Connect to the Oracle database and create a database link to the PostgreSQL database 

SQL> create database link test connect to "a" identified by "a" using 'pg';



Database link created.



SQL> select * from "t"@test;



n

----------

1

 

Hope it helps!