This topic describes how to use the oracle_fdw plug-in to connect to an Oracle database. You can also use this plug-in 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 plug-in. If the current minor engine version does not support the oracle_fdw plug-in, 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. The CIDR block of the VPC

Background information

The oracle_fdw plug-in is developed by PostgreSQL to manage foreign tables. The plug-in provides easy access to Oracle databases and allows you to synchronize data between PostgreSQL databases and Oracle databases.

For more information, see oracle_fdw.

Precautions

  • 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 plug-in. For more information about the conversion rules supported by the oracle_fdw plug-in, see Data types.
  • The oracle_fdw plug-in can push down the WHERE and ORDER BY clauses to Oracle databases.
  • The oracle_fdw plug-in 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 plug-in further supports the following spatial data types:
    • Point
    • Line
    • Polygon
    • MultiPoint
    • MultiLine
    • MultiPolygon

Procedure

  1. Execute the following statement to create an oracle_fdw plug-in:
    CREATE EXTENSION oracle_fdw;
  2. 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>');
  3. 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');
  4. 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.

    ParameterDescription
    keySpecifies 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.
    tableRequired. 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.
    schemaThe Oracle username for accessing a table that does not belong to the currently connected user. The value must be in uppercase.
    max_longThe 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.
    readonlySpecifies whether the Oracle table is read-only. If the value is true, you cannot execute the INSERT, UPDATE, or DELETE statements.
    sample_percentThe percentage of Oracle table blocks that are randomly selected to calculate PostgreSQL table statistics. Valid values: 0.000001 to 100. Default value: 100.
    prefetchThe 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.

Delete the oracle_fdw plug-in

Execute the following SQL statement to delete the oracle_fdw plug-in:

DROP EXTENSION oracle_fdw;