How to use oracle_fdw in PostgreSQL

Galaxy Glossary

How do I query Oracle data from PostgreSQL using oracle_fdw?

oracle_fdw lets PostgreSQL read and join Oracle tables through the Foreign Data Wrapper interface.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why choose oracle_fdw instead of ETL?

oracle_fdw streams Oracle rows on demand, eliminating nightly copies and keeping data fresh.

How do I install oracle_fdw?

Install the Oracle Instant Client, compile oracle_fdw from source, then run CREATE EXTENSION oracle_fdw; in the target database.

What permissions are required?

The PostgreSQL superuser installs the extension.Ordinary users need USAGE on the FDW server and SELECT on foreign tables.

How do I define an Oracle server?

Use CREATE SERVER with connection details, then CREATE USER MAPPING for credentials.

Example

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'ORCLPDB1'); CREATE USER MAPPING FOR app_user SERVER oradb OPTIONS (user 'ora_user', password 'secret');

How do I import Oracle tables?

Run IMPORT FOREIGN SCHEMA or create foreign tables manually to map specific columns and data types.

Example

IMPORT FOREIGN SCHEMA ecommerce FROM SERVER oradb INTO public OPTIONS (schema 'ECOM', import_default 'false');

Can I push down filters and joins?

oracle_fdw pushes WHERE clauses and some JOINs, reducing network traffic.Use EXPLAIN to verify pushdown.

How do I join Oracle and PostgreSQL data?

Create local tables alongside foreign tables, then write standard SQL joins.PostgreSQL handles execution.

Example query

SELECT c.name, o.total_amount FROM Customers c JOIN orders_from_oracle o ON o.customer_id = c.id WHERE o.order_date > CURRENT_DATE - INTERVAL '30 days';

Best practices for performance?

Index Oracle columns used in joins or filters, limit selected columns, and avoid large cross-database sorts.

How do I troubleshoot ORA- and FDW- errors?

Enable log_min_messages = debug1 in postgresql.conf to capture oracle_fdw diagnostics, and check Oracle listener logs.

Why How to use oracle_fdw in PostgreSQL is important

How to use oracle_fdw in PostgreSQL Example Usage


-- join local and Oracle data to find high-value recent purchases
SELECT p.name       AS product_name,
       c.email      AS customer_email,
       oi.quantity  AS qty,
       o.total_amount
FROM   OrderItems oi                -- local table
JOIN   Products    p  ON p.id = oi.product_id
JOIN   Orders_ora  o  ON o.id = oi.order_id   -- Oracle table
JOIN   Customers   c  ON c.id = o.customer_id
WHERE  o.total_amount > 500
  AND  o.order_date  > CURRENT_DATE - INTERVAL '7 days'
ORDER  BY o.total_amount DESC;

How to use oracle_fdw in PostgreSQL Syntax


CREATE EXTENSION oracle_fdw;

-- define remote Oracle database
CREATE SERVER oradb
  FOREIGN DATA WRAPPER oracle_fdw
  OPTIONS (dbserver 'ORCLPDB1');

-- map PostgreSQL role to Oracle user
CREATE USER MAPPING FOR app_user
  SERVER oradb
  OPTIONS (user 'ora_user', password 'secret');

-- import the Oracle table ECOM.ORDERS as Orders_ora
IMPORT FOREIGN SCHEMA ecommerce LIMIT TO (ORDERS)
  FROM SERVER oradb INTO public
  OPTIONS (schema 'ECOM');

Common Mistakes

Mistake: Forgetting to install Oracle Client libraries. oracle_fdw fails with "ORA-00000" errors. Fix: Download the matching Instant Client version and set LD_LIBRARY_PATH or PATH before compiling.
Mistake: Importing every Oracle table. Huge schemas slow planning. Fix: Use LIMIT TO in IMPORT FOREIGN SCHEMA or create only required foreign tables.