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:
2. Download ODBC-Link from following location:
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:
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 :).