How to oracle_fdw in PostgreSQL

How do I install and use oracle_fdw to query Oracle tables from PostgreSQL?

oracle_fdw installs a foreign data wrapper that allows PostgreSQL to query Oracle tables under an open-source license.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is oracle_fdw?

oracle_fdw is an open-source foreign data wrapper (FDW) that lets PostgreSQL run SQL against Oracle databases as if their tables lived locally. The code ships under Oracle’s Universal Permissive License (UPL), giving teams commercial flexibility.

Why use oracle_fdw instead of ETL?

FDWs avoid batch copies. Queries run in real time, reducing data staleness, storage cost, and pipeline maintenance. Developers can join Oracle data with local tables in one statement.

How do I install oracle_fdw?

On most Linux systems, compile from source with Oracle Instant Client headers, then run CREATE EXTENSION oracle_fdw; in PostgreSQL. Windows builds require MSVC and the same client libraries.

How do I connect PostgreSQL to Oracle?

After installing the extension, define a foreign server pointing at the Oracle listener, create a user mapping with Oracle credentials, and import or manually create foreign tables.

Step 1 – Create the server

CREATE SERVER oracle_srv FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//ora-host:1521/ORCLPDB1');

Step 2 – Map users

CREATE USER MAPPING FOR app_user SERVER oracle_srv OPTIONS (user 'ORA_APP', password 'secret');

Step 3 – Import tables

IMPORT FOREIGN SCHEMA HR LIMIT TO (PRODUCTS) FROM SERVER oracle_srv INTO public;

What does a cross-database join look like?

Once products is a foreign table, you can join it with local orderitems to calculate revenue on the fly.

Best practices for production

Use read-only Oracle accounts, tune oracle_fdw fetch_size, and index join columns on both sides. Monitor query plans to confirm Oracle predicates are pushed down.

Common mistakes and fixes

Missing Oracle client libraries: the extension will fail to build; install the 21c Instant Client first. Forgetting to set LD_LIBRARY_PATH: PostgreSQL cannot find libclntsh.so; export the path or add it to /etc/ld.so.conf.d/.

Can I query ecommerce tables?

Yes. Map Oracle’s PRODUCTS table, then join it with local Orders and OrderItems to enrich reports without ETL.

Why How to oracle_fdw in PostgreSQL is important

How to oracle_fdw in PostgreSQL Example Usage


-- Combine Oracle PRODUCTS with local ORDERITEMS to find low-stock bestsellers
SELECT p.id, p.name, p.stock, SUM(oi.quantity) AS units_sold
FROM Products p                -- foreign Oracle table
JOIN OrderItems oi ON oi.product_id = p.id  -- local table
GROUP BY p.id, p.name, p.stock
HAVING SUM(oi.quantity) > 100
ORDER BY units_sold DESC;

How to oracle_fdw in PostgreSQL Syntax


-- Install extension (superuser)
CREATE EXTENSION IF NOT EXISTS oracle_fdw;

-- Define remote Oracle server
CREATE SERVER oracle_srv
    FOREIGN DATA WRAPPER oracle_fdw
    OPTIONS (
        dbserver '//ora-host:1521/ORCLPDB1'
    );

-- Map PostgreSQL role to Oracle credentials
CREATE USER MAPPING FOR app_user
    SERVER oracle_srv
    OPTIONS (
        user 'ORA_APP',
        password 'secret'
    );

-- Import a single Oracle table
IMPORT FOREIGN SCHEMA HR
    LIMIT TO (PRODUCTS)
    FROM SERVER oracle_srv
    INTO public;

-- Example ecommerce join
SELECT o.id AS order_id,
       c.name AS customer,
       SUM(oi.quantity * p.price) AS order_total
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
JOIN OrderItems oi ON oi.order_id = o.id
JOIN Products p ON p.id = oi.product_id
GROUP BY o.id, c.name;

Common Mistakes

Skipping Oracle Instant Client during installation leads to linker errors. Fix by downloading the matching architecture client, setting ORACLE_HOME, and recompiling oracle_fdw.
Importing every Oracle table overwhelms PostgreSQL stats. Limit imports or create only the tables you need with IMPORT FOREIGN SCHEMA ... LIMIT TO.