OracleFDW: Oracle® Foreign Data Wrapper
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 RequisitesPlease 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.
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.
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? :-)