Sunday, May 15, 2011

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 :).

14 comments:

  1. Thank you for this post - based on this I now have a working connection between pg and oracle. I encountered couple of stumbling blocks on Ubuntu 10.04 on x86_64:

    * Make sure your version of unixodbc is at least 2.2.12, or you can encounter the error "undefined symbol: SQLGetPrivateProfileStringW" when connecting to oracle. The version in Ubuntu 10.04 was 2.2.11.

    * Even the most recent packages of unixodbc in Ubuntu, 2.2.14, have a bug which requires patching and recompiling. Otherwise all odbc-queries will return "ERROR: odbclink: unsuccessful SQLAllocStmt call". The needed change, as discussed on OTN: http://forums.oracle.com/forums/thread.jspa?threadID=340030&start=15&tstart=-1

    In the unixodbc sources for version 2.2.14, you need to change two places, lines 1827 and 1855 of DriverManager/SQLConnect.c:

    old: SQLUSMALLINT supported;
    new: SQLUSMALLINT supported = SQL_TRUE;

    After recompiling and installing with the above change, and otherwise following along your post, I'm now at the point where I can "select * from dual" through odbc.

    ReplyDelete
  2. Hi. I'm using Ubuntu 10.04 LTS and Postgresql 8.4.8.

    HELP!

    When I run the make command I get error:
    postgres@dev-postgres-1:~/ODBC-Link-1.0.4$ make USE_PGXS=1
    Makefile:12: /usr/lib/postgresql/8.4/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory
    make: *** No rule to make target `/usr/lib/postgresql/8.4/lib/pgxs/src/makefiles/pgxs.mk'. Stop.

    pg_config:
    postgres@dev-postgres-1:~/ODBC-Link-1.0.4$ pg_config
    BINDIR = /usr/lib/postgresql/8.4/bin
    DOCDIR = /usr/share/doc/postgresql
    HTMLDIR = /usr/share/doc/postgresql
    INCLUDEDIR = /usr/include/postgresql
    PKGINCLUDEDIR = /usr/include/postgresql
    INCLUDEDIR-SERVER = /usr/include/postgresql/8.4/server
    LIBDIR = /usr/lib
    PKGLIBDIR = /usr/lib/postgresql/8.4/lib
    LOCALEDIR = /usr/share/locale
    MANDIR = /usr/share/postgresql/8.4/man
    SHAREDIR = /usr/share/postgresql/8.4
    SYSCONFDIR = /etc/postgresql-common
    PGXS = /usr/lib/postgresql/8.4/lib/pgxs/src/makefiles/pgxs.mk
    CONFIGURE = '--build=i486-linux-gnu' '--prefix=/usr' '--includedir=/usr/include' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--sysconfdir=/etc' '--localstatedir=/var' '--libexecdir=/usr/lib/postgresql-8.4' '--disable-maintainer-mode' '--disable-dependency-tracking' '--disable-silent-rules' '--srcdir=.' '--mandir=/usr/share/postgresql/8.4/man' '--with-docdir=/usr/share/doc/postgresql-doc-8.4' '--sysconfdir=/etc/postgresql-common' '--datadir=/usr/share/postgresql/8.4' '--bindir=/usr/lib/postgresql/8.4/bin' '--includedir=/usr/include/postgresql/' '--enable-nls' '--enable-integer-datetimes' '--enable-thread-safety' '--enable-debug' '--disable-rpath' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-krb5' '--with-gssapi' '--with-openssl' '--with-libxml' '--with-libxslt' '--with-ldap' '--with-ossp-uuid' '--with-gnu-ld' '--with-tclconfig=/usr/lib/tcl8.5' '--with-tkconfig=/usr/lib/tk8.5' '--with-includes=/usr/include/tcl8.5' '--with-system-tzdata=/usr/share/zoneinfo' '--with-pgport=5432' 'CFLAGS=-g -O2 -g -Wall -O2 -fPIC' 'LDFLAGS=-Wl,-Bsymbolic-functions -Wl,--as-needed' 'build_alias=i486-linux-gnu' 'CPPFLAGS='
    CC = gcc
    CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.5
    CFLAGS = -g -O2 -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g
    CFLAGS_SL = -fpic
    LDFLAGS = -Wl,-Bsymbolic-functions -Wl,--as-needed -Wl,--as-needed
    LDFLAGS_SL =
    LIBS = -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lkrb5 -lcom_err -lgssapi_krb5 -lz -lreadline -lcrypt -ldl -lm
    VERSION = PostgreSQL 8.4.8

    ReplyDelete
  3. You have to set pg_config location in PATH (env Variable) Before running "make USE_PGXS=1" as given below:
    export PATH=:$PATH
    make USE_PGXS=1

    ReplyDelete
  4. I have executed first few steps, but I am getting the error when I try to 'Execute following odbclink.sql file in PostgreSQL database to create require functions to access the Other Database'. I am getting the following error:
    CREATE SCHEMA
    CREATE TYPE
    psql:/usr/local/pgsql/share/contrib/odbclink.sql:13: ERROR: could not load library "/usr/local/pgsql/lib/odbclink.so": /usr/local/pgsql/lib/odbclink.so: undefined symbol: TextDatumGetCString
    psql:/usr/local/pgsql/share/contrib/odbclink.sql:17: ERROR: could not load library "/usr/local/pgsql/lib/odbclink.so": /usr/local/pgsql/lib/odbclink.so: undefined symbol: TextDatumGetCString
    psql:/usr/local/pgsql/share/contrib/odbclink.sql:21: ERROR: could not load library "/usr/local/pgsql/lib/odbclink.so": /usr/local/pgsql/lib/odbclink.so: undefined symbol: TextDatumGetCString
    psql:/usr/local/pgsql/share/contrib/odbclink.sql:25: ERROR: could not load library "/usr/local/pgsql/lib/odbclink.so": /usr/local/pgsql/lib/odbclink.so: undefined symbol: TextDatumGetCString
    psql:/usr/local/pgsql/share/contrib/odbclink.sql:29: ERROR: could not load library "/usr/local/pgsql/lib/odbclink.so": /usr/local/pgsql/lib/odbclink.so: undefined symbol: TextDatumGetCString
    psql:/usr/local/pgsql/share/contrib/odbclink.sql:33: ERROR: could not load library "/usr/local/pgsql/lib/odbclink.so": /usr/local/pgsql/lib/odbclink.so: undefined symbol: TextDatumGetCString
    psql:/usr/local/pgsql/share/contrib/odbclink.sql:37: ERROR: could not load library "/usr/local/pgsql/lib/odbclink.so": /usr/local/pgsql/lib/odbclink.so: undefined symbol: TextDatumGetCString
    GRANT
    psql:/usr/local/pgsql/share/contrib/odbclink.sql:48: ERROR: function odbclink.connect(text, text, text) does not exist

    ReplyDelete
    Replies
    1. Above error seems incompatibility with PG Version. What is version you are using?

      Delete
  5. Nice post. I was checking continuously this blog and I'm impressed! Very useful info specifically the last part :) I care for such information a lot. I was looking for this certain info for a very long time. Thank you and good luck.
    Feel free to surf my site :: view

    ReplyDelete
  6. Hi, What is the path we have to set pg_config binary's path in PATH Environment Variable

    ReplyDelete
    Replies
    1. Generally its in PostgreSQL Installation directory.
      /bin

      Delete
  7. I have too a mistake
    in ubuntu everything was established but in debian an error of TextDatumGetCString.
    postgresql 8.3 version
    and postgresql 8.4 version

    ReplyDelete
    Replies
    1. Are you sure, you have compiled this module properly. If this kind of incompatibility is coming, then would recommend to contact Cybertech.

      Delete
  8. We are getting the error on PG 8.1

    psql:odbclink.sql:13: ERROR: could not load library "/usr/lib/pgsql/odbclink.so": /usr/lib/pgsql/odbclink.so: undefined symbol: TextDatumGetCString
    psql:odbclink.sql:17: ERROR: could not load library "/usr/lib/pgsql/odbclink.so": /usr/lib/pgsql/odbclink.so: undefined symbol: TextDatumGetCString
    psql:odbclink.sql:21: ERROR: could not load library "/usr/lib/pgsql/odbclink.so": /usr/lib/pgsql/odbclink.so: undefined symbol: TextDatumGetCString
    psql:odbclink.sql:25: ERROR: could not load library "/usr/lib/pgsql/odbclink.so": /usr/lib/pgsql/odbclink.so: undefined symbol: TextDatumGetCString
    psql:odbclink.sql:29: ERROR: could not load library "/usr/lib/pgsql/odbclink.so": /usr/lib/pgsql/odbclink.so: undefined symbol: TextDatumGetCString
    psql:odbclink.sql:33: ERROR: could not load library "/usr/lib/pgsql/odbclink.so": /usr/lib/pgsql/odbclink.so: undefined symbol: TextDatumGetCString
    psql:odbclink.sql:37: ERROR: could not load library "/usr/lib/pgsql/odbclink.so": /usr/lib/pgsql/odbclink.so: undefined symbol: TextDatumGetCString

    Any ideas please?

    ReplyDelete
    Replies
    1. I believe this module is not compatible with 8.1

      Delete
  9. Hi Vibhor,

    How can I fetch data into Oracle DB from Postgres DB?

    ReplyDelete
    Replies
    1. You can use Oracle Heterogeneous Database link feature to fetch the data of Postgres DB in Oracle.

      Delete