oracle_fdw

Overview

require oracle-libs

Version

Install

You can install oracle_fdw directly. First, make sure the PGDG repository is added and enabled:

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

Install the extension using pig or apt/yum/dnf:

pig install oracle_fdw;          # Install for current active PG version

pig ext install -y oracle_fdw -v 18  # PG 18
pig ext install -y oracle_fdw -v 17  # PG 17
pig ext install -y oracle_fdw -v 16  # PG 16
pig ext install -y oracle_fdw -v 15  # PG 15
pig ext install -y oracle_fdw -v 14  # PG 14

dnf install -y oracle_fdw_18       # PG 18
dnf install -y oracle_fdw_17       # PG 17
dnf install -y oracle_fdw_16       # PG 16
dnf install -y oracle_fdw_15       # PG 15
dnf install -y oracle_fdw_14       # PG 14

apt install -y postgresql-18-oracle-fdw   # PG 18
apt install -y postgresql-17-oracle-fdw   # PG 17
apt install -y postgresql-16-oracle-fdw   # PG 16
apt install -y postgresql-15-oracle-fdw   # PG 15
apt install -y postgresql-14-oracle-fdw   # PG 14

Create Extension:

CREATE EXTENSION oracle_fdw;

Usage

oracle_fdw: Foreign data wrapper for Oracle access

Create Server

CREATE EXTENSION oracle_fdw;

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

Server Options: dbserver (required, Oracle connection string), isolation_level (serializable, read_committed, or read_only, default serializable), nchar (expensive character conversion, default off), set_timezone (sync timezone with Oracle, default off).

Create User Mapping

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

Use an empty string for user to enable external (OS) authentication.

Create Foreign Table

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');

Table Options: table (required, Oracle table name in uppercase), schema (table owner), dblink (Oracle DB link), max_long (max LONG column length, default 32767), readonly (default false), sample_percent (ANALYZE sampling, default 100), prefetch (rows per round-trip, default 50).

Column Options: key (mark as primary key, required for UPDATE/DELETE), strip_zeros (remove ASCII 0 from strings).

You can also use a query instead of a table name by enclosing it in parentheses:

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

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

Import Options: case (keep, lower, or smart, default smart), readonly, skip_tables, skip_views, skip_matviews, max_long, sample_percent, prefetch.

Utility Functions

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

Data Type Mapping