oracle_fdw

The following examples assume the PostgreSQL system user is able to connect to the Oracle server with the following connection string:

fdwtest/foo@//oracle.localhost:1521/orclpdb1

Create an Oracle table:

CREATE TABLE fdw_test (
  id INT NOT NULL PRIMARY KEY,
  val VARCHAR(128)
);

Set up oracle_fdw:

postgres=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
             OPTIONS (dbserver '//oracle.localhost:1521/orclpdb1');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER oradb
             OPTIONS (user 'fdwtest', password 'foo');
CREATE USER MAPPING

Define a foreign table:

postgres=# CREATE FOREIGN TABLE ora_fdw_test (
             id INT OPTIONS (key 'true') NOT NULL,
             val VARCHAR(128)
           )
             SERVER oradb
             OPTIONS (table 'FDW_TEST');
CREATE FOREIGN TABLE

Insert, update and delete data:

postgres=# INSERT INTO ora_fdw_test VALUES(1, 'foo'), (2,'bar');
INSERT 0 2

postgres=# SELECT * FROM ora_fdw_test;
 id | val 
----+-----
  1 | foo
  2 | bar
(2 rows)

postgres=# UPDATE ora_fdw_test SET val = 'baz' WHERE id = 2;
UPDATE 1

postgres=# DELETE FROM ora_fdw_test WHERE id = 1;
DELETE 1

postgres=# SELECT * FROM ora_fdw_test;
 id | val 
----+-----
  2 | baz
(1 row)

Finally, confirm the table has the expected data in Oracle:

SQL> SELECT * FROM fdw_test;

        ID
----------
VAL
--------------------------------------------------------------------------------------------------------------------------------
         2
baz

The following error may be encountered if inserting data into a newly created Oracle table:

ERROR:  error executing query: OCIStmtExecute failed to execute remote query
DETAIL:  ORA-08177: can't serialize access for this transaction

This is a known Oracle issue; see section Serialization errors in the README.

• GitHub: oracle_fdw

• Using Foreign Data Wrappers to access remote PostgreSQL and Oracle databases - January 2020 article by EnterpriseDB
• Migrating data from Oracle to PostgreSQL database - April 2020 blog article from Devart, with focus on Windows usage