oracle_fdw
A foreign data wrapper providing support for connecting PostgreSQL to Oracle databases
oracle_fdw
is a foreign data wrapper (FDW) providing support for connecting PostgreSQL to Oracle databases.
Release history
This is a summary of the major point releases; for a full and detailed list of releases, see the oracle_fdw release page and CHANGELOG.
Examples
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.
References
- GitHub: oracle_fdw
Useful links
- 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