Installation of Oracle Foreign Data Wrapper for PostgreSQL

If you are working on both Oracle and PostgreSQL databases , sometimes you need to access each database from other. My project contains accessing Oracle database from PostgreSQL database and this took me to search how to do it and found foreign data wrappers.Foreign data wrapper based on accessing to data that is not in PostgreSQL database.

Following link contains all FDW information for PostgreSQL .

https://wiki.postgresql.org/wiki/Foreign_data_wrappers

In order to install Oracle FDW , below Oracle Instant Client rpm files and oracle_fdw extension are needed.

Oracle FDW - https://github.com/laurenz/oracle_fdw

Oracle Instant Client - http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

Install oracle-instantclient-basic, oracle-instantclient-sqlplus, oracle-instantclient-devel packages for your version.

After installation below envinroments are set.

export PATH=$PATH:/usr/pgsql-9.3/bin
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin:$LD_LIBRARY_PATH

Extract downloaded oracle_fdw.master.zip file and change into directory.Software installation is done with following commands

$make

$make install

Now oracle_fdw shared library has been installed in the PostgreSQL library directory and oracle_fdw.control and the SQL files are in the PostgreSQL extension directory.

Following step is done to install extension in a database with superuser.

$psql -d {Your database name}

$CREATE EXTENSION oracle_fdw;

ERROR:  could not load library "/usr/pgsql-9.3/lib/oracle_fdw.so": libclntsh.so.11.1: cannot open shared object file: No such file or directory

  

Above error can be seen while creating extension because of library missing which is actually exists.In order to solve this error, Oracle library path is added to system library list.

$echo /usr/lib/oracle/11.2/client64/lib> /etc/ld.so.conf.d/oracle.conf

$ldconfig

Executing same command for install extension completed successfully after library arrangement.

$CREATE EXTENSION oracle_fdw;

CREATE EXTENSION