Use oracle_fdw to connect to an Oracle database - ApsaraDB RDS

The oracle_fdw extension lets you query and write to Oracle tables directly from ApsaraDB RDS for PostgreSQL — without migrating data. Run SELECT, INSERT, UPDATE, and DELETE statements against Oracle tables as if they were local PostgreSQL tables, and bulk-import entire Oracle schemas with a single command.

Prerequisites

Before you begin, make sure you have:

An ApsaraDB RDS for PostgreSQL instance running PostgreSQL 12 with minor engine version 20200421 or later
Note
Run SHOW rds_supported_extensions; to check whether your instance supports oracle_fdw. If it does not, update the minor engine version first.
Oracle client version 11.2 or later. The Oracle server version must be compatible with the client version — see the Oracle client-server interoperability matrix.
The virtual private cloud (VPC) CIDR block of your RDS instance added to the Oracle database IP address whitelist. Example: 172.xx.xx.xx/16.
Note
Find the VPC CIDR block on the Database Connection page of the ApsaraDB RDS console.

Before you connect

Gather these Oracle connection details before starting — you will need them in the setup steps:

Detail	Description
Endpoint	Internal endpoint of the Oracle database
Port	Internal port of the Oracle database
Database name	Name of the Oracle database to connect to
Username and password	Oracle credentials with access to the target tables

How it works

oracle_fdw is an open-source foreign data wrapper (FDW) for PostgreSQL. It creates a bridge between PostgreSQL and Oracle so that PostgreSQL treats Oracle tables as local foreign tables.

Setup involves four objects:

Extension — enables oracle_fdw in the database
Server — stores the Oracle connection endpoint and port
User mapping — maps a PostgreSQL user to Oracle credentials
Foreign table — defines the Oracle table columns and OPTIONS visible to PostgreSQL

Once configured, PostgreSQL sends queries to Oracle transparently. The extension can push WHERE and ORDER BY clauses down to Oracle for execution, reducing the data transferred over the network.

For the full oracle_fdw specification, see the oracle_fdw GitHub repository.

Usage notes

To run UPDATE or DELETE on a foreign table, set key 'true' on all primary key columns when you create the foreign table.
Column data types in the foreign table must be compatible with oracle_fdw conversion rules. See Data types for the full conversion reference.
oracle_fdw supports pushdown of WHERE and ORDER BY clauses to Oracle.
JOIN pushdown is supported with the following limits:
- Both tables must be defined in the same database mapping.
- Pushdown applies only to two-table JOINs — three or more tables are not pushed down.
- The JOIN must appear inside a SELECT statement.
- Cross JOINs without a JOIN condition are not pushed down.
- When a JOIN is pushed down, ORDER BY is not pushed down.
After PostGIS is installed, oracle_fdw additionally supports these spatial data types: Point, Line, Polygon, MultiPoint, MultiLine, and MultiPolygon.

Enable oracle_fdw

Important

Verify that your RDS for PostgreSQL instance meets the version requirements in Prerequisites before running these commands.

Enable the extension:

CREATE EXTENSION oracle_fdw;

Remove the extension:

DROP EXTENSION oracle_fdw;

Connect to an Oracle database

Step 1: Create a server

Create a server object that stores the Oracle connection details. Two syntax forms are available:

Option A — connection string:

CREATE SERVER <server_name>
    FOREIGN DATA WRAPPER oracle_fdw
    OPTIONS (dbserver '//<oracle_endpoint>:<oracle_port>/<oracle_dbname>');

Option B — separate host, port, and database:

CREATE SERVER oradb
    FOREIGN DATA WRAPPER oracle_fdw
    OPTIONS (
        host '<oracle_endpoint>',
        port '<oracle_port>',
        dbname '<oracle_dbname>'
    );

Both forms produce the same result. Use Option A when you already have a connection string. Use Option B when you want to specify each parameter separately.

Expected output:

CREATE SERVER

Step 2: Create a user mapping

Map a PostgreSQL user to Oracle credentials:

CREATE USER MAPPING
FOR <pg_username> SERVER <server_name>
OPTIONS (user '<oracle_username>', password '<oracle_password>');

Example:

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

Note

If you do not store the Oracle user credentials in the PostgreSQL database, set user to an empty string and provide external authorization credentials.

Expected output:

CREATE USER MAPPING

Step 3: Create a foreign table

Define a foreign table that maps to an Oracle table. Column definitions must match the Oracle table schema.

CREATE FOREIGN TABLE oratab (
    id       integer                OPTIONS (key 'true') NOT NULL,
    text     character varying(30),
    floating double precision        NOT NULL
)
SERVER oradb
OPTIONS (
    table          'ORATAB',
    schema         'ORAUSER',
    max_long       '32767',
    readonly       'false',
    sample_percent '100',
    prefetch       '200'
);

The following table describes the OPTIONS parameters:

Parameter	Required	Default	Description
`table`	Yes	—	Oracle table name in uppercase. To use a SQL expression instead of a table name, wrap it in parentheses: `(SELECT col FROM tab WHERE val = ''string'')`. Do not use `schema` when using a SQL expression.
`schema`	No	—	Oracle username that owns the table, if it differs from the connected user. Must be uppercase.
`key`	No	`false`	Marks a column as a primary key. Set to `true` for all primary key columns to enable UPDATE and DELETE.
`max_long`	No	`32767`	Maximum length for LONG, LONG RAW, and XMLTYPE columns. Valid range: 1–1073741823.
`readonly`	No	—	When `true`, prevents INSERT, UPDATE, and DELETE on the foreign table.
`sample_percent`	No	`100`	Percentage of Oracle table blocks sampled to calculate PostgreSQL statistics. Valid range: 0.000001–100.
`prefetch`	No	`200`	Number of rows fetched per round-trip during a foreign table scan. Valid range: 0–1024. Set to `0` to disable prefetching.

Expected output:

CREATE FOREIGN TABLE

Step 4: Query and write data

After the foreign table is created, use standard SQL to read from and write to the Oracle table:

-- Read data
SELECT * FROM oratab;

-- Insert a row
INSERT INTO oratab (id, text, floating) VALUES (1, 'example', 3.14);

-- Update a row (requires key 'true' on primary key columns)
UPDATE oratab SET text = 'updated' WHERE id = 1;

-- Delete a row (requires key 'true' on primary key columns)
DELETE FROM oratab WHERE id = 1;

If the query fails with an error similar to the following, the Oracle database cannot be reached. Check that the VPC CIDR block is added to the Oracle IP address whitelist.

ERROR: connection for foreign table "oratab" cannot be established
DETAIL: ORA-12170: TNS:Connect timeout occurred

Import an entire Oracle schema

To create foreign tables for all tables in an Oracle schema at once, use IMPORT FOREIGN SCHEMA:

IMPORT FOREIGN SCHEMA <ora_schema_name>
FROM SERVER <server_name>
INTO <pg_schema_name>
OPTIONS (case 'lower');

The case option controls how Oracle object names are converted to PostgreSQL:

Value	Behavior
`keep`	Keeps Oracle names as-is (usually uppercase)
`lower`	Converts all names to lowercase
`smart`	Converts only all-uppercase names to lowercase

ApsaraDB RDS:Use the oracle_fdw extension to connect to an Oracle database

Prerequisites

Before you connect

How it works

Usage notes

Enable oracle_fdw

Connect to an Oracle database

Step 1: Create a server

Step 2: Create a user mapping

Step 3: Create a foreign table

Step 4: Query and write data

Import an entire Oracle schema

What's next