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 pg_config bianry’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 ODBC 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 :).

23 Comments

  1. Hi V.K., nice post!
    I’m new at PG and I’m trying to migrate from Oracle.
    I have a test PostgreSQL 9.1.2 (with PostGIS) on a 32-bit windows box.
    Could you please help me create a dblink from that machine to an Oracle 11.2 db (the box already has a thick client on it)?
    Many thanks in advance!
    Greg

      1. Hi Vibhor, thanks for the prompt response!!
        Your link lead me to download the “oracle_fdw-0.9.2.tar.gz” file – but, as far as I know, this is a linux installable and my PG is in windows.
        What can I do?
        Many thanks once more.

      2. Hi Vibhor,

        I’m afraid this goes way beyond my knowledge – I’ll have to look for other alternatives…

        Any ideas?

  2. I appreciate this document. It’s proved to be very helpful. However, I stumbled into one problem which I ‘m not sure if it’s user error on my part. If I select from an Oracle table and one of the columns is of type ‘number’ I can’t get the value to come across unless I specifiy it to be of type ‘real’ as in the following:

    select * from odbclink.query(1,’select objid,title,due_date from table_task’) as t(objid real, task varchar(240), due_date timestamp);

    In this case the number value appears in scientific notation (e.g. 2.68438e+08). However if I specify numeric, smallint, largeint instead of real I get an error that the return and sql tuple descriptions are incompatible.

    When I look at the data in oracle the number appears as a nine digit number.

    Thanks again.

    1. You would like to check. Type of value you are storing. Oracle uses NUMBER data type for storing real and int value.
      Also, check the precision, and accordingly you would like to use NUMERIC(precision, scale).

      1. Dear
        select odbclink.connect(‘DSN=PRIM’);
        The connection to the server was lost. Attempting reset: Failed.

        please , help me resloved this problem.

  3. Hi Vibhor,

    I try execute link from postgresql9.1.9 to oracle database 11.2.0.2, but after configuration finish I missing error as :

    export LD_LIBRARY_PATH=/usr/local/pgsql/lib:/user/lib:/usr/lib64:/lib64:/usr/local/lib:$LD_LIBRARY_PATH
    $ export TWO_TASK=//192.168.72.98:1521/PRIMARY
    $ pg_ctl -D “/u02/data” restart -mf
    waiting for server to shut down…. done
    server stopped
    server starting
    $ psql -p 5432 -d test -U test
    psql (9.1.9)
    Type “help” for help.

    test=> select odbclink.connect(‘DSN=PRIMARY’);
    The connection to the server was lost. Attempting reset: Failed.

    please help me.

  4. Hi V.K. I get the next error…
    # select odbclink.connect(‘DSN=DESA’);

    ERROR: odbclink: unsuccessful SQLConnect call: [01000] [0] [[unixODBC][Driver Manager]Can’t open lib ‘/usr/lib/oracle/10.2.0.3/client/lib/libsqora.so.10.1’ : file not found]

    but the file is there….
    any clue?

    tks!

      1. Yes it works!, it was a permission issue!

        Now, i want to connect my postgres db with two oracle databases. Can i??
        I saw linuxodbc uses TWO_TASK variable… so i can’t define 2 difrent databases here….
        i tried using tns_admin and configure odbc.ini (add new entry with same driver)…but it dosn’t work
        is it possible?

        tks, again

  5. Hi Vibhor,
    I was trying to create this extension for my postgres install (9.2.3) to talk to oracle(11g). I am not seeing file odbclink.sql anywhere after a successful build of unixODBC. Where do you think the file should have been created?

    Here is my pg_config:

    BINDIR = /usr/pgsql-9.2/bin
    DOCDIR = /usr/share/doc/pgsql
    HTMLDIR = /usr/share/doc/pgsql
    INCLUDEDIR = /usr/pgsql-9.2/include
    PKGINCLUDEDIR = /usr/pgsql-9.2/include
    INCLUDEDIR-SERVER = /usr/pgsql-9.2/include/server
    LIBDIR = /usr/pgsql-9.2/lib
    PKGLIBDIR = /usr/pgsql-9.2/lib
    LOCALEDIR = /usr/pgsql-9.2/share/locale
    MANDIR = /usr/pgsql-9.2/share/man
    SHAREDIR = /usr/pgsql-9.2/share
    SYSCONFDIR = /etc/sysconfig/pgsql
    PGXS = /usr/pgsql-9.2/lib/pgxs/src/makefiles/pgxs.mk
    CFLAGS_SL = -fpic
    LDFLAGS = -L/usr/lib64 -Wl,–as-needed
    LDFLAGS_EX =
    LDFLAGS_SL =
    LIBS = -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lcrypt -ldl -lm
    VERSION = PostgreSQL 9.2.3

    Any help is appreciated.
    Arvind

    1. I figured out the issue it was because I had two postgres installs 8.x and 9.x, so I had to set my PATH according to 9.2 pg_config, so now I see odbclink.sql after new make.

      But now the issue is different, its about library “libodbc.so.2″

      psql -h localhost -p 5435 -f $PGSHARE/contrib/odbclink.sql dbname


      psql:/usr/pgsql-9.2/share/contrib/odbclink.sql:13: ERROR: could not load library “/usr/pgsql-9.2/lib/odbclink.so”: libodbc.so.2: cannot open shared object file: No such file or directory

      any clues…?

      Thanks
      Arvind

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s