Using ORACLE_FDW to access Oracle in PostgreSQL

This article describes how to use ORACLE_FDW to access data on Oracle in PostgreSQL.

1. Installing ORACLE_FDW

Refer to: ORACLE_FDW in GitHub

Before compiling and installing ORACLE_FDW, you need to install the client program for Oracle, and you can usually install the Lightweight client package: Oracle Instant Client: Oracle Instant Client

To install ORACLE_FDW, you can download the installation package on the website: ORACLE_FDW in GitHub] (HTTPS://GITHUB.COM/LAURENZ/ORACLE_FDW "") or download the source code directly using Git clone:

cd <postgresql源码目录>/contribgit clone https://github.com/laurenz/oracle_fdw.git

Before compiling the installation, you need to set the environment variables for Oracle, as added in. Bash_profile:

export ORACLE_HOME=/opt/oracle/products/11.2.0export NLS_LANG=AMERICAN_AMERICA.AL32UTF8export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATHexport PATH=${PATH}:${ORACLE_HOME}/bin/

Then go to the ORACLE_FDW source directory to compile the installation:

makemake insttall

After installation, you need to restart the database to take effect.

2. Create ORACLE_FDW External Table

ORACLE_FDW is accessing ORACLE through the OCI interface, so you need to configure $oracle_home/network/admin/tnsnames.ora, which reads:

oratest =  (DESCRIPTION =    (ADDRESS_LIST =        (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))    )    (CONNECT_DATA =      (sid = oratest)      (SERVER = DEDICATED)    )  )

Above we have configured a connection string DSN as "Oratest", we will use this string later. Once configured, use Tnsping to test:

[email protected]:~$ tnsping oratestTNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 11-NOV-2014 22:28:06Copyright (c) 1997, 2009, Oracle.  All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (sid = oratest) (SERVER = DEDICATED)))OK (0 msec)

Assuming that there is a Scott user in the Oracle database with a password of Tiger, which has the table dept, below I have built an external table in PostgreSQL that can be accessed on this Dept table in Oracle:

In Psql, use the Super User:

CREATE EXTENSION oracle_fdw;CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw          OPTIONS (dbserver ‘oratest‘);GRANTUSAGEONFOREIGN SERVER oradb TO scott;

Note that "Oratest" in the above command "DBServer ' Oratest" is the tnsnames set in the preceding TNSNames. The above SQL assigns access to the Oracle external service to the user Scott, who is now logged on with the Scott User:

CREATEUSER MAPPING FORcurrent_user SERVER oradb          OPTIONS (user‘scott‘, password ‘tiger‘);CREATEFOREIGNTABLE dept(deptno smallint options(key‘true‘), dname varchar(14), loc varchar(13))    SERVER oradb OPTIONS (schema‘SCOTT‘, table‘DEPT‘);

To test, Query dept in Psql:

osdba=# select * from dept; deptno |   dname    |   loc    --------+------------+----------     10 | ACCOUNTING | NEW YORK     20 | RESEARCH   | DALLAS     30 | SALES      | CHICAGO     40 | OPERATIONS | BOSTON(4 rows)

You can see that the data has been queried, ORACLE_FDW external tables can also support INSERT, UPDATE, delete:

mydb=> insertinto dept values(50, ‘OSDBA‘, ‘HANGZHOU‘);INSERT01mydb=> select * from dept; deptno |   dname    |   loc    --------+------------+----------     10 | ACCOUNTING | NEW YORK     20 | RESEARCH   | DALLAS     30 | SALES      | CHICAGO     40 | OPERATIONS | BOSTON     50 | OSDBA      | HANGZHOU(5 rows)mydb=> update dept set loc=‘HZ‘where deptno=50;UPDATE1mydb=> select * from dept; deptno |   dname    |   loc    --------+------------+----------     10 | ACCOUNTING | NEW YORK     20 | RESEARCH   | DALLAS     30 | SALES      | CHICAGO     40 | OPERATIONS | BOSTON     50 | OSDBA      | HZ(5 rows)mydb=> deletefrom dept where deptno=50;DELETE1mydb=> select * from dept; deptno |   dname    |   loc    --------+------------+----------     10 | ACCOUNTING | NEW YORK     20 | RESEARCH   | DALLAS     30 | SALES      | CHICAGO     40 | OPERATIONS | BOSTON(4 rows)

Using ORACLE_FDW to access Oracle in PostgreSQL