oracle_fdw

pig repo add pgdg -u    # add pgdg repo and update cache

pig install oracle_fdw;		# install via package name, for the active PG version

pig install oracle_fdw -v 18;   # install for PG 18
pig install oracle_fdw -v 17;   # install for PG 17
pig install oracle_fdw -v 16;   # install for PG 16
pig install oracle_fdw -v 15;   # install for PG 15
pig install oracle_fdw -v 14;   # install for PG 14

CREATE EXTENSION oracle_fdw;

CREATE EXTENSION oracle_fdw;

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
  OPTIONS (dbserver '//dbserver.mydomain.com:1521/ORADB');

CREATE USER MAPPING FOR pguser SERVER oradb
  OPTIONS (user 'orauser', password 'orapwd');

CREATE FOREIGN TABLE oratab (
  id integer OPTIONS (key 'true') NOT NULL,
  text character varying(30),
  floating double precision NOT NULL
)
SERVER oradb
OPTIONS (schema 'ORAUSER', table 'ORATAB');

CREATE FOREIGN TABLE oraquery (
  id integer,
  text character varying(30)
)
SERVER oradb
OPTIONS (table '(SELECT id, text FROM ORAUSER.ORATAB WHERE id > 10)');

IMPORT FOREIGN SCHEMA "ORAUSER"
  FROM SERVER oradb INTO local_schema;

SELECT oracle_diag();              -- Show versions and environment info
SELECT oracle_diag('oradb');       -- Include Oracle server version
SELECT oracle_close_connections(); -- Close all cached Oracle connections
SELECT oracle_execute('oradb', 'TRUNCATE TABLE ORAUSER.ORATAB'); -- Execute arbitrary Oracle SQL