Today, organizations stores information(data) in different database
systems. Each database system has a set of applications that run against
it. This data is just bits and bytes on a file system - and only a
database can turn the bits and bytes of data into business information.
Integration and consolidation of such information(data) into one
database system is often difficult. Because many of the applications
that run against one database may not have an equivalent application
that runs against another. To consolidate the information into one
database system, we need a heterogeneous database connection. In this
post, I’ll demo on how you may connect PostgreSQL to one of
heterogeneous database Oracle using different methods.
Below are few methods to make connection to Oracle database in
- Using ODBC Driver
- Using Foreign DataWrappers
- Using Oracle Call Interface(OCI) Driver
Softwares used in demo(included download links) - CentOS 7 64bit,
EDB Postgres Advanced Server
unixODBC-2.3.4, Oracle Instant
Open DataBase Connectivity(ODBC) is a standard software API for using
DBMS. The ODBC driver/ODBC Data source(API) is a library that allows
applications to connect to any database for which an ODBC driver is
available. It’s a middle layer translates the application’s data queries
into commands that the DBMS understands. To use this method, an open
source unixODBC and Oracle ODBC driver(Basic/ODBC/Devel) packages
required. Along with a module in PostgreSQL that can communicate to DSN
created using unixODBC and Oracle ODBC driver. Few years back
CyberTec has released a module ODBC-Link, at
present it is obsolete, however, it has a dblink-style implementation
for PostgreSQL to connect to any other ODBC compliant database. We can
use this module for basic connectivity to Oracle. Let’s see.
Binary/Libraries/configuration files location:
Install Oracle ODBC Driver
Binary/Libraries location: /usr/lib/oracle/11.2/client64
Libraries and SQL files location:
Installation will create a ODBC-Link module SQL file in $PGHOME/contrib
directory. Load the SQL file, which will create a schema by name
“odbclink” with necessary functions in it.
At this point, we have installed unixODBC Drirver, Oracle ODBC driver
and ODBC-Link module for PostgreSQL. As a first step, we need to create
a DSN using Oracle ODBC.
Edit /etc/odbcinst.ini file and pass the drivers deifinition
Edit /etc/odbc.ini file and create the DSN with driver mentioned in
After creating DSN, load all Oracle & unix ODBC driver libraries by
setting environment variables and test the connectivity using OS command
line tool “dltest” & “iSQL”
Now, set the same environment variables for postgres user for loading
the libraries and restart the PostgreSQL cluster to take effect. Connect
to PostgreSQL and call odbclink functions to connect to Oracle
Cool right…!!!. For retrieving and manipulating data refer to
ODBC-Link README file.
An SQL/MED(SQL Management of External Data) extension to the SQL
Standard allows managing external data stored outside the database.
SQL/MED provides two components Foreign data wrappers and Datalink.
PostgreSQL introduced Foreign Data Wrapper(FDW) in 9.1 version with
read-only support and in 9.3 version write support of this SQL Standard.
Today, the latest version has a number of features around it and many
varieties of FDW available to access different remote SQL databases.
Oracle_fdw provides an easy and efficient way to access Oracle
Database. IMO,its one of the coolest method to access the remote
database. To compile Oracle_FDW with PostgreSQL 9.5, we need Oracle
Instant Client libraries and pg_config set in PATH. We can use the same
Oracle Instant Client libraries used for ODBC-Link. Let’s see how it
First, set environment variables with OIC libraries and pg_config
Unzip the oracle_fdw module and compile it with PostgreSQL 9.5
Now switch as ‘postgres’ user and restart the cluster by loading Oracle
Instant Client libraries required for oracle_fdw extension and create
the extension inside the database.
Now you can access the Oracle database.
Oracle Call Interface(OCI) a type-2 driver freely available on Oracle
site which allows the client to connect to Oracle database. EDB Postgres
Advanced Server (also called EPAS) a proprietary product has built-in
OCI-based database link module called dblink_ora, which connects to
Oracle database using Oracle OCI drivers. All you have to do to use
dblink_ora module, install EPAS(not covering installation) and tell
EPAS where it can find Oracle OCI driver libraries. We can make use of
same Oracle Instant Client by specifying its libraries location in
LD_LIBRARY_PATH environment variable and to take effect restart the
First, switch as “enterprisedb” user, load the libraries and restart the
cluster. That’s all, we are good to access Oracle database.
Note: EPAS connects to the Oracle Database using Oracle Instant Client
library “libclntsh.so”. If you won’t find the library in Oracle Client
Library location then create the symbolic link with libclntsh.so
pointing to the libclntsh.so.version.number. Refer to documentation.
In the example, dblink_ora_connect establishes a connection to an
Oracle database with the user-specified connection information. Later
using link name(‘oraconn’ in my case) we can perform operations like
SELECT,INSERT,DELETE,UPDATE & COPY using dblink_ora* functions. All
functions you can refer from the EnterpriseDB documentation
All the above methods will be very handy if you are working on migration
projects. Hope its helpful. Thank you