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? :-)