oracle_fdw and reading Oracle tables from PostgreSQL as foreign tables

What the extension actually gives you

oracle_fdw is a PostgreSQL extension that provides a Foreign Data Wrapper for easy and efficient access to Oracle databases, including pushdown of WHERE conditions and required columns as well as comprehensive EXPLAIN support.

That sentence lists the three things that matter about a foreign data wrapper, and it is worth taking them one at a time because they are what separates this from a generic data export.

Pushdown of required columns means that when you query a foreign table, only the columns you actually reference are fetched from Oracle. A query that reads two columns out of a forty-column table does not transfer forty columns. Pushdown of WHERE conditions means the filter is evaluated on the Oracle side, so the rows that cross the link are the ones that match rather than all of them. And comprehensive EXPLAIN support means you can see the SQL oracle_fdw actually generated for the remote side, which turns a federated query from a black box into something you can inspect.

The extension is written in C, the homepage is laurenz.github.io/oracle_fdw, and the licence is one GitHub cannot classify with a pointer to the LICENSE file. The last push was on 2026-08-24, and the recent releases are 2.9.0 from 2026-06-08, 2.8.0 from 2025-05-10 and 2.7.0 from 2024-08-01. The repository is not archived.

The author is Laurenz Albe, with contributions credited to Vincent Mora of Oslandia and Tatsuro Yamada of the NTT OSS Center, and with a special mention for Christian Ullrich for ongoing help with Windows. The repository layout shows the C sources split into oracle_fdw.c, oracle_utils.c and oracle_gis.c, the latter for the spatial support, plus the extension control file and the SQL scripts.

The audience is a PostgreSQL administrator or developer who has to work with data that lives in Oracle, and who would rather query it than export it. The most common reason to end up here is a migration in progress where the application has moved to PostgreSQL but the data has not, or a reporting requirement that spans both.

The cookbook: a table, a server, a user mapping, a foreign table

The README opens with a worked example, and it is the fastest way to understand the object model, because a foreign data wrapper setup in PostgreSQL has four distinct pieces.

The first assumption is that you can connect to Oracle as the operating system user postgres, with a command like sqlplus orauser/orapwd@//dbserver.mydomain.com:1521/ORADB. That means the Oracle client and the environment are set up correctly, and it is the check that tells you whether the rest of the setup can possibly work.

The second is the shape of the Oracle table, which the example describes with an ID column of NUMBER(5), a TEXT column of VARCHAR2(30) and a FLOATING column of NUMBER(7,2).

Then the extension and server, created as a PostgreSQL superuser:

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

The README recommends granting usage on the foreign server to a normal user rather than leaving everything to a superuser, and notes this is not required for the example to work.

Next the user mapping, which is where the Oracle credentials live:

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

The comment here points at the alternative: you can use external authentication to avoid storing Oracle passwords. That matters in a setup where the PostgreSQL host does not hold database credentials in its own catalogue, and the README covers it further down.

Finally the foreign table itself:

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

The key option marks the column as the Oracle key, the schema and table options give the remote object, and the readme flags the detail that catches people: table and schema names must normally be in uppercase, and the schema option is optional. After that the table is used like a regular PostgreSQL table.

The objects the extension creates

The extension installs a small set of objects, and knowing them makes the rest of the documentation easier to read.

There is a handler function oracle_fdw_handler() returning fdw_handler and a validator function oracle_fdw_validator(text[], oid) returning void. These are the two functions a foreign data wrapper requires, and they are wired into a FOREIGN DATA WRAPPER named oracle_fdw with HANDLER oracle_fdw_handler and VALIDATOR oracle_fdw_validator.

Normally that is all you need, and you can proceed to define foreign servers. The README does allow creating additional Oracle foreign data wrappers, for example if you need to set the nls_lang option, and it notes the catch: you can alter the existing wrapper, but all modifications will be lost after a dump or restore, so a new wrapper is the way to make an option persistent.

There is a utility function oracle_close_connections() that closes all open Oracle connections in the current session, and the Usage section describes when you would want it.

There is oracle_diag(name DEFAULT NULL) returning text, and the readme is explicit that it is for diagnostic purposes only. It returns the versions of oracle_fdw, the PostgreSQL server and the Oracle client. Called with no argument or NULL it also returns the values of some of the environment variables used for establishing Oracle connections, and called with the name of a foreign server it additionally returns the Oracle server version. This is the function to reach for when a connection behaves oddly, since it tells you which of the three version numbers is out of step.

And there is oracle_execute(server name, stmt text) returning void, which executes arbitrary SQL statements on the remote Oracle server. The readme is careful about its limits. It only works with statements that do not return results, typically DDL statements. You are told to be careful when using it since it might disturb the transaction management of oracle_fdw, and reminded that running a DDL statement in Oracle issues an implicit COMMIT. The advice is to use it outside of multi-statement transactions.

That last point is the one to hold on to. A remote COMMIT inside a PostgreSQL transaction is a genuinely surprising thing for a database to do to you, and the readme says so plainly.

The option surface: dbserver, isolation_level and nls_lang

Three option groups are documented, and each carries a warning worth reading.

On the foreign data wrapper itself there is one option, nls_lang, which is optional. It sets the NLS_LANG environment variable for Oracle, in the form language_territory.charset, for example AMERICAN_AMERICA.AL32UTF8, and it must match your database encoding. When the value is not set, oracle_fdw will automatically do the right thing if it can and issue a warning if it cannot. The instruction is to set it only if you know what you are doing.

The automatic behaviour is worth understanding rather than trusting blindly. A charset mismatch between the Oracle session and the Oracle database is the classic cause of a query returning replacement characters instead of an error, so the option exists to pin the behaviour when the automatic detection cannot infer it. Setting it explicitly to a value that does not match the database encoding is the failure mode, and the readme's warning is aimed at exactly that.

On the foreign server, dbserver is required. It is the Oracle database connection string for the remote database, in any of the forms Oracle supports as long as the Oracle client is configured accordingly, and you set it to an empty string for local, BEQUEATH, connections.

The other server option is isolation_level, optional and defaulting to serializable. The allowed values are serializable, read_committed and read_only, and the readme is candid that READ ONLY is technically a transaction access mode rather than an isolation level, and that the distinction is ignored here.

The reason serializable is the default is explained immediately after. The Oracle table can be queried more than once during a single PostgreSQL statement, for example during a nested loop join. To make sure that no inconsistencies caused by race conditions with concurrent transactions can occur, the transaction isolation level must guarantee that the data does not change during the statement. That is a correctness requirement, not a conservatism, and it is the single most important thing to understand before you tune this option.

Building it: the Oracle client dependency in the Makefile

The Makefile is where the packaging reality of this extension shows, and it is worth reading even if you never build it yourself.

The module is oracle_fdw, the objects are oracle_fdw.o, oracle_utils.o and oracle_gis.o, and the extension name matches the module name. The data files are the SQL scripts for the initial version and the two upgrade paths, 1.0 to 1.1 and 1.1 to 1.2, and the documentation is README.oracle_fdw rather than the main README.

The regression tests are named oracle_fdw, oracle_gis, oracle_import and oracle_join, and the presence of oracle_import and oracle_join as separate suites tells you that importing data and joining across the link are the two operations most worth testing.

The build flags are where the dependency becomes explicit. The Makefile hunts for Instant Client installations in standard paths by looking for /usr/include/oracle/*/client64 and /usr/include/oracle/*/client, and for the matching library directories under /usr/lib/oracle/*/client64/lib and /usr/lib/oracle/*/client/lib. Alongside that it uses the classic ORACLE_HOME locations, sdk/include, oci/include, rdbms/public and the ORACLE_HOME root, and links against the Oracle shared library named by ORACLE_SHLIB.

That shared library name is resolved per platform: oci.dll on Windows and cygwin, libclntsh elsewhere. So the build expects the Oracle client libraries to be present, either a full client install or an Instant Client, and it will look in ORACLE_HOME first and then fall back to the standard system paths.

The build also disables LLVM byte code with override with_llvm := no, and supports both the PGXS build and a NO_PGXS build that drops the extension into the contrib directory of a PostgreSQL source tree, including the top-level Makefile.global and contrib-global.mk. That second path is what you want if you are building against a PostgreSQL source tree rather than installing against a system package.

There is a Makefile only at the top level of the listed entries, and the msvc/ directory is the Windows build support, which matches the readme's mention of ongoing help with Windows from Christian Ullrich.

Where oracle_fdw is the wrong tool

Three limits follow from what the extension is.

The first is that it is a read path with a thin write escape hatch. The documented way to run statements on the Oracle side is oracle_execute, and it only works for statements that return no results, typically DDL, and it can disturb the transaction management and trigger an implicit COMMIT in Oracle. A bidirectional data flow with conflict handling is not what this is for. If you need Oracle to be written to in a transactional, recoverable way, you want a replication or ETL tool rather than a foreign table.

The second is the dependency on the Oracle client on the PostgreSQL host. Every connection goes through OCI, which means the client libraries have to be installed, licensed appropriately and kept in step with the Oracle server version. In a container image for PostgreSQL, that means the image now contains an Oracle client, which is a heavier build and a licensing question. The alternative approaches do not have that property: an application-level approach talks to Oracle from the application process with its own driver, and a database-native approach uses Oracle's own federation, for example SQL Developer or the GoldenGate family of products, so that the Oracle side owns the data movement.

The third is the cost of the remote call. Every foreign table scan is a round trip to the Oracle server, and the isolation default of serializable exists because the same Oracle table can be read more than once in one statement. A join that would be fast inside Oracle can be slow when it is federated through PostgreSQL, and a nested loop join over a foreign table is exactly the case the readme warns about. Pushdown helps with filters and columns, but it does not turn a remote scan into a local one.

The right mental model is that oracle_fdw is for occasional cross-system queries and reporting, not for moving a workload onto PostgreSQL that belongs on Oracle. If the volume of cross-system traffic becomes a large part of your workload, the round trips will dominate whatever the pushdown saves.

Editorial conclusion

oracle_fdw is a good fit for a PostgreSQL installation that needs to read or join against Oracle data without writing application code for the Oracle side, because the join and filter pushdown means a federated query does not drag whole tables across the link, and the EXPLAIN output shows the remote SQL that was generated. It is a poor fit if you need to write to Oracle, since the documented remote execution function only works for statements that return no results, and a poor fit for a PostgreSQL host without the Oracle client libraries, since the build and the runtime both depend on them. Before adopting, read the isolation level section and decide between the default serializable behaviour and read_committed, and set nls_lang explicitly rather than relying on the automatic detection, because a mismatch between the character set and the database encoding is the failure that produces garbled data rather than an error.

Frequently asked questions

Official sources

1. Issues
2. laurenz/oracle_fdw on GitHub
3. Project website
4. README
5. Releases