PostgreSQL Foreign Data Wrapper for Oracle on Windows OS

The foreign data wrapper is responsible for fetching data from the remote data source and returning it to the PostgreSQL executor. If wrapper must support in updating foreign tables.

In this post, I'll demonstrate, how you may connect PostgreSQL database to Oracle database using oracle_fdw.

Before going ahead kindly ensure that PostgreSQL is installed in your system.

Let’s begin:

Step-1

Software’s used in demo - Windows, PostgreSQL 9.5, 9.6

 instantclient-basic-win-x86-64-11.2.0.1.0

instantclient-jdbc-win-x86-64-11.2.0.1.0

 instantclient-odbc-win-x86-64-11.2.0.1.0

instantclient-sqlplus-win-x86-64-11.2.0.1.0

& Oracle_FDW as per version

(

http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html  ,

https://pgxn.org/dist/oracle_fdw/

or version wise)

 Download all the files mentioned above, in a single folder and unzip entire files to this single folder.

Suppose, we create a folder Oracle in c: drive

Step-2

Set ENVIRONMENT VARIABLE like below:

Go to My Computer ---> Properties --->Advanced system setting---> Environment VARIABLE

Add new variable

TNS_ADMIN and give the path of that folder, where we unzip instantclient_11_2.

eg.

New..

Variable name  ---> TNS_ADMIN

Variable value ---> C:\oracle\instantclient_11_2

Step 3

Install oracle instant client

It will  be available within C:\oracle\instantclient_11_2 folder

eg.

odbc_install.exe                               [With administrative privilege double click on odbc_install.exe. In my                                     case it will be in  C:\oracle\instantclient_11_2 folder]

Step 4

Create tnsnames.ora files in instant client folder (C:\oracle\instantclient_11_2\ tnsnames.ora)

eg. Open tnsnames.ora

# tnsnames.ora Network Configuration File: D:\DevSuiteHome_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

#medsentryse.czpatbztfp5m.us-east-1.rds.amazonaws.com/ORCL

ORCL =

  (DESCRIPTION =

    (ADDRESS =

      (PROTOCOL = TCP)

      (HOST = <Host Name or IP >)             

      (PORT = 1521)

    )

    (CONNECT_DATA =

      (SERVER = dedicated)

      (SERVICE_NAME = ORCL)                   

    )

  )

 [Hint : Host Name or IP where oracle(remote DB) installed,In my case it is 192.168.1.10
 ORCL is name of oracle (remote DB) service name (sid)]

Step 5

Create sqlnet.ora files in instant client folder (C:\oracle\instantclient_11_2\ sqlnet.ora)

eg.

Open sqlnet.ora files and write like below

SQLNET.AUTHENTICATION_SERVICES= (NULL)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

TCP.CONNECT_TIMEOUT=5

Step 6

Download Oracle_FDW, where you installed your postgresql server.

If we installed postgreSQL9.5, then download oracle_fdw-1.5.0-pg95-win64 or 32 bit

Unzip this in a folder in the location, where you installed your postgresql server.

After unzip we will get four folders - (doc, lib, share, symbols)

                (i) Open lib folder and copy oracle_fdw.dll and paste it in main postgresql lib folder

                 (ii) Open  ..\share folder under it open \extension folder and copy three files   And paste it in                             main postgresql \share\extension folder 

                    oracle_fdw.control

                           oracle_fdw--1.0--1.1

                           oracle_fdw--1.1

And paste it in main postgresql \share\extension folder

Step 7

Restart your PostgreSQL server

Now you can access the Oracle database.

Connect with pg_Admin and open SQL pane or connect with command prompt SQL Shell(psql) and run below scripts:

(i)                 CREATE EXTENSION oracle_fdw;

(ii)               CREATE SERVER ORACLETEST1 FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.1.10/ORCL ');     [Hint : ORCL: It is main server’s  ORACLE INSTANCE name

     //192.168.1.10 is oracle (remote DB) host ip]

(iii)              GRANT USAGE ON FOREIGN SERVER ORACLETEST1 TO POSTGRES;

(iv)              CREATE USER MAPPING FOR postgres SERVER ORACLETEST1 OPTIONS (user ‘scott', password ‘tiger’);

Step 8:

Now map tables:

CREATE FOREIGN TABLE TEST_FR (ROLL NUMERIC) SERVER ORACLETEST1 OPTIONS(schema ‘scott’,table ‘test’);     

[Hint : Test_FR is postgreSQL table name and test is oracle’s Table name  under scott  schema]

Now you can check your Oracle's data in PostgreSQL Database.

SELECT * FROM public.TEST_FR;

INSERT INTO public.TEST_FR(ROLL) VALUES(2) ;  [ It will insert values in test table of Oracle                                                                                                     server’s]