PostgreSQL Database Link to Oracle Database on Linux

I have seen question, like "How to make Database Link from PostgreSQL to Oracle?", always floats in PostgreSQL Community Forum. So, I thought to do some research on it and write a Blog.

Cybertec (One of The PostgreSQL Database Company) has released a PostgreSQL Module ODBC Link, using which user can make Database link to any other database, including Oracle, MS SQL Server, PostgreSQL etc, which have ODBC compliant Data source.

Lets see how you can make ODBC Connection from PostgreSQL to any ODBC compliant data source and fetch data.

Installation of this module is very simple. Following are the steps which user can follow:
1. Install the unixODBC driver on your linux machine. user can use following useful link for Downloading and Installing the unixODBC Driver:

http://www.unixodbc.org/

2. Download ODBC-Link from following location:

http://www.cybertec.at/download/odbc_link/ODBC-Link-1.0.4.tar.gz

3. Untar the downloaded file as given below:

tar -zxvf ODBC-Link-1.0.4.tar.gz

4. Compile the source code as given below:

make USE_PGXS=1
make USE_PGXS=1 install

Note:: Before running above command, don't forget to set pg_config binary's path in PATH Environment Variable.
5. Execute following odbclink.sql file in PostgreSQL database to create require functions to access the Other Database, as given below:

$ psql -f $PGSHARE/odbclink.sql dbname

After installing the ODBC-Link for PostgreSQL, User has to install the Oracle Instant Client which comes with Oracle ODBC Driver.
Since I have Linux System so, I have installed Oracle Instant Client for linux from following link:

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

After installing the ODBC Driver, User has to configure ODBC Data source. To get the unixODBC Configuration files, user can use following command:

$ ./odbcinst -j
unixODBC 2.2.12
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /root/.odbc.ini

Now update the following two UnixODBC Configuration Files:

File: /etc/odbc.ini
[XE]
Description  = Oracle ODBC
Driver   = XE
Trace   = yes
TraceFile  = /tmp/odbc_oracle.log
Database  = //ubuntu:1521/XE
UserID   = hr
Password  = hr
Port   = 1521

File: /etc/odbcinst.ini
[XE]
Description = Oracle ODBC Connection Driver
Driver  = /opt/Oracle_Client/lib/libsqora.so.10.1
Debug  = 0
CommLog  = 1

After making above entries for Oracle ODBC Data source. Now restart the PostgreSQL Instance with following environment variables:

export LD_LIBRARY_PATH=/opt/Oracle_Client/lib
export TWO_TASK=//ubuntu:1521/XE
pg_ctl -D "PostgreSQL data Directory" restart -mf

Now, we are setup for using ODBC Link from PostgreSQL to Oracle Database.

Following are some snapshots:
1. Create ODBC Link as given below:

edb=# select odbclink.connect('DSN=XE');
 connect 
---------
       1
(1 row)

edb=# 

2. Now, using the ODBC Link access the Oracle Database table:

edb=# select * from odbclink.query(1,'SELECT ename,empno FROM emp') as t( ename text, d numeric);
 ename  |  d   
--------+------
 SMITH  | 7369
 ALLEN  | 7499
 WARD   | 7521
 JONES  | 7566
 MARTIN | 7654
 BLAKE  | 7698
 CLARK  | 7782
 SCOTT  | 7788
 KING   | 7839
 TURNER | 7844
 ADAMS  | 7876
 JAMES  | 7900
 FORD   | 7902
 MILLER | 7934
(14 rows)

Similarly, user can make the ODBC link from PostgreSQL to Other Databases too. Interesting :).