This topic describes how to use the oracle_fdw extension to connect to an Oracle database. You can also use this extension to synchronize data between tables in a PostgreSQL database and tables in an Oracle database.
Prerequisites
Your ApsaraDB RDS for PostgreSQL instance runs PostgreSQL 12 with the minor engine version of 20200421 or later.
Note
You can execute the SHOW rds_supported_extensions;
statement to check whether the current minor engine version of your RDS instance supports the oracle_fdw extension. If the current minor engine version does not support the oracle_fdw extension, you must first update the minor engine version.
The Oracle client version is 11.2 or later.
The Oracle server version is based on the Oracle client version. For more information, see Oracle documentation.
You must add the CIDR block of the virtual private cloud (VPC) in which your RDS instance resides to an IP address whitelist of the Oracle database that you want to connect. Example of a CIDR block: 172.xx.xx.xx/16
.
Note
You can view the CIDR block of the VPC in which an RDS instance resides on the Database Connection page of the ApsaraDB RDS console. 
Background information
The oracle_fdw extension is developed by PostgreSQL to manage foreign tables. The extension provides easy access to Oracle databases and allows you to synchronize data between PostgreSQL databases and Oracle databases.
For more information, see oracle_fdw.
Usage notes
If you want to execute the UPDATE or DELETE statements, you must set the key parameter to true for primary key columns when you create a foreign table. For more information, see the Create a foreign table section of this topic.
The data types of columns in the foreign table must be identifiable and convertible for the oracle_fdw extension. For more information about the conversion rules supported by the oracle_fdw extension, see Data types.
The oracle_fdw extension can push down the WHERE and ORDER BY clauses to Oracle databases.
The oracle_fdw extension can push down JOIN operations to Oracle databases. Pushdown has the following limits:
Both tables for a JOIN operation must be defined in the same database mapping.
JOIN operations on three or more tables cannot be pushed down.
JOIN operations must be included in a SELECT statement.
Cross JOIN operations without JOIN conditions cannot be pushed down.
If a JOIN operation is pushed down, ORDER BY clauses are not pushed down.
After PostGIS is installed, the oracle_fdw extension further supports the following spatial data types:
Point
Line
Polygon
MultiPoint
MultiLine
MultiPolygon
Create and delete the oracle_fdw extension
Important
Before creating the oracle_fdw extension, make sure that the major engine and minor engine versions of your RDS for PostgreSQL instance meet the requirements described in Prerequisites.
Create the extension
CREATE EXTENSION oracle_fdw;
Delete the extension
DROP EXTENSION oracle_fdw;
Use the the oracle_fdw extension
Execute one of the following statements to create an Oracle database mapping:
-
CREATE SERVER <Server name>
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//<The internal endpoint that is used to connect to the Oracle database>:<The internal port that is used to connect to the Oracle database>/<The name of the Oracle database that you want to connect>');
-
CREATE SERVER oradb
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (host '<The internal endpoint that is used to connect to the Oracle database>', port '<The internal port that is used to connect to the Oracle database>', dbname '<The name of the Oracle database that you want to connect>');
Execute the following statement to create a user mapping:
CREATE USER MAPPING
FOR <The username used to log on to the PostgreSQL database> SERVER <The name of the user mapping>
OPTIONS (user '<The username used to log on to the Oracle database>', password '<The password used to log on to the Oracle database>');
Note
If you do not store the Oracle user credentials in the PostgreSQL database, set the user parameter to an empty string and provide external authorization credentials.
Example:
CREATE USER MAPPING
FOR pguser SERVER oradb
OPTIONS (user 'orauser', password 'orapwd');
Execute the following statement to create a 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 (table 'ORATAB',
schema 'ORAUSER',
max_long '32767',
readonly 'false',
sample_percent '100',
prefetch '200');
Note
The schema of the foreign table must be consistent with that of the mapped Oracle table.
The following table describes the parameters in OPTIONS.
Parameter | Description |
key | Specifies whether to set a column as a primary key column. Valid values: true and false. Default value: false. If you want to execute the UPDATE and DELETE statements, you must set the value to true for all primary key columns. |
table | Required. The name of the Oracle table. The value must be in uppercase. You can also use an Oracle SQL statement to define the value of the table parameter. Example: OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')') . In this case, do not use the schema parameter. |
schema | The Oracle username for accessing a table that does not belong to the currently connected user. The value must be in uppercase. |
max_long | The maximum length of columns that have the LONG, LONG RAW, or XMLTYPE data type in the Oracle table. Valid values: 1 to 1073741823. Default value: 32767. |
readonly | Specifies whether the Oracle table is read-only. If the value is true, you cannot execute the INSERT, UPDATE, or DELETE statements. |
sample_percent | The percentage of Oracle table blocks that are randomly selected to calculate PostgreSQL table statistics. Valid values: 0.000001 to 100. Default value: 100. |
prefetch | The number of rows that are fetched for a single round-trip transmission between PostgreSQL and Oracle during a foreign table scan. Valid values: 0 to 1024. Default value: 200. The value 0 indicates that the prefetch feature is disabled. |
After you create the foreign table, you can use it to perform operations on the Oracle table. Basic SQL statements such as DELETE, INSERT, UPDATE, and SELECT are supported. Foreign table definitions can be imported. Sample statement:
IMPORT FOREIGN SCHEMA <ora_schema_name>
FROM SERVER <server_name>
INTO <schema_name>
OPTIONS (case 'lower');
Note
You can set the case parameter to one of the following valid values:
keep: uses the same object names as those in Oracle. In most cases, the names are in uppercase.
lower: converts all object names to lowercase.
smart: converts only the object names that are in all uppercase to lowercase.