OracleFDW: Oracle® Foreign Data Wrapper

Overview

The Oracle Foreign Data Wrapper provides integration between PostgreSQL and Oracle databases by enabling remote Oracle tables to be queried as if they were tables within your PostgreSQL database. This robust FDW supports many advanced features such as read/write, predicate push down to the WHERE clause, and Import Foreign Schema. Take a look at the documentation to learn more.

The OracleFDW extension is available with BigSQL Windows and Linux distributions. Unfortunately, Oracle stopped supporting the instant client software after OSX 10.10, so we are no longer able to provide OracleFDW for OSX (macOS).

Pre Requisites

Please install the free Oracle® InstantClient and verify connectivity to your Oracle DB from your server that runs Postgres. We recommend you also install the SQL*Plus client so you can verify connectivity before proceeding with testing the FDW. We've tested successfully, from the various platforms, with a mixture of Oracle DB and Client versions 10, 11 & 12.

Tutorial

For the sake of the example below, we assume you can succesfully connect to Oracle, from the command line using sqlplus, from the user that runs Postgres. You'll need to do the normal things for using Oracle InstantClient like setting the PATH and TNSNAMES environment variables.

  sqlplus orauser/orapasswd@testdb
Our sample table & data in Oracle are defined like this:

CREATE TABLE sample_emp (
  empno     NUMBER(4)    NOT NULL PRIMARY KEY,
  ename     VARCHAR2(10),
  hiredate  DATE,
  sal       NUMBER(7,2)
);
INSERT INTO sample_emp VALUES (7369, 'Smith', '17-DEC-1980',  800);
INSERT INTO sample_emp VALUES (7782, 'Clark', '09-JUN-1981', 2450);
INSERT INTO sample_emp VALUES (7839, 'King',  '17-NOV-1981', 5000);
COMMIT;

Install the orafce extension using BigSQL Manager or PGC command line:

  ./pgc install oracle_fdw1-pg95

Then configure PostgreSQL, from psql as the superuser, as follows:


CREATE EXTENSION oracle_fdw;

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
  OPTIONS (dbserver 'testdb');

GRANT USAGE ON FOREIGN SERVER oradb TO postgres;

CREATE USER MAPPING FOR postgres SERVER oradb
  OPTIONS (user 'orauser', password 'orapasswd');

CREATE FOREIGN TABLE emp 
  (empno    integer   OPTIONS (key 'true') NOT NULL,
   ename    varchar(10),
   hiredate date,
   sal      numeric(7,2))
  SERVER oradb OPTIONS (table 'SAMPLE_EMP');

Now the remote oracle table SAMPLE_EMP can be used in PostgreSQL as the EMP foreign table. You can SELECT, INSERT, UPDATE and DELETE. How cool is that? :-)

From PostgreSQL 9.5 on you can also import all the tables from an entire schema, using IMPORT FOREIGN SCHEMA:

IMPORT FOREIGN SCHEMA my_ora_schema
    FROM SERVER oradb INTO orafdw_schema
    OPTIONS (CASE 'lower');

Notes:

As noted in the oracle_fdw documentation, since the Oracle client shared library is probably not in the standard library path, you have to make sure that the PostgreSQL server will be able to find it. How this is done varies from operating system to operating system; on Linux you can set LD_LIBRARY_PATH or use /etc/ld.so.conf.

Make sure that all necessary Oracle environment variables are set in the environment of the PostgreSQL server process (ORACLE_HOME if you don't use Instant Client, TNS_ADMIN if you have configuration files, etc.)

On linux, the FDW expects a specific version number in the Oracle libclntsh.so name. When running CREATE EXTENSION, if you get an error about not being able to load libclntsh.so.10.1 you need to create a symlink from the version of instant client you installed to the expected name. Adjust the following based on your installation paths:

cd /usr/lib/oracle/12.2/client64
  sudo ln -s libclntsh.so.12.1 libclntsh.so.10.1