This topic describes how to use the oracle_fdw plug-in to connect to an Oracle database. It also provides details about how to create a PostgreSQL table and synchronize data to an Oracle table.

Prerequisites

  • Your RDS instance runs PostgreSQL 12 with the kernel version of 20200421 or later.
    Note You can execute the show rds_supported_extensions; statement to check whether the current kernel version supports the oracle_fdw plug-in. If the current kernel version does not support the oracle_fdw plug-in, you must first upgrade the kernel 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.

Background information

oracle_fdw is a PostgreSQL plug-in that provides a Foreign Data Wrapper (FDW). It provides easy access to Oracle databases and allows you to synchronize data between PostgreSQL and Oracle.

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 Create a foreign table.
  • The data types of columns in the foreign table must be identifiable and convertible for oracle_fdw. For more information about the conversion rules supported by oracle_fdw, see Data types.
  • oracle_fdw can push the WHERE and ORDER BY clauses down to Oracle databases.
  • oracle_fdw can push down join operations to Oracle databases. Pushdown has the following limits:
    • Both tables for a join must be defined in the same database mapping.
    • Joins between three or more tables cannot be pushed down.
    • Joins must be included in a SELECT statement.
    • Cross joins without join conditions cannot be pushed down.
    • If a join is pushed down, ORDER BY clauses are not pushed down.
  • oracle_fdw supports PostGIS. After PostGIS is installed, oracle_fdw further supports the following spatial data types:
    • POINT
    • LINE
    • POLYGON
    • MULTIPOINT
    • MULTILINE
    • MULTIPOLYGON

Procedure

  1. Create an oracle_fdw plug-in. The statement is as follows:
    CREATE EXTENSION oracle_fdw;
  2. Create an Oracle database mapping. One of the following two statements can be used:
    • CREATE SERVER <Server name>
      FOREIGN DATA WRAPPER oracle_fdw
      OPTIONS (dbserver '//<Endpoint>:<Port>/<Database name>');

      Example:

      CREATE SERVER <Server name>
      FOREIGN DATA WRAPPER oracle_fdw
      OPTIONS (dbserver '//127.0.0.1:5432/oradbname');
    • CREATE SERVER oradb
      FOREIGN DATA WRAPPER oracle_fdw
      OPTIONS (host '<Endpoint>', port '<Port>', dbname '<Database name>');

      Example:

      CREATE SERVER oradb
      FOREIGN DATA WRAPPER oracle_fdw
      OPTIONS (host '127.0.0.1', port '5432', dbname 'oradbname');
  3. Create a user mapping. The statement is as follows:
    CREATE USER MAPPING
    FOR <PostgreSQL username> SERVER <Mapping name>
    OPTIONS (user '<Oracle database username>', password '<User password>');
    Note If you do not store the Oracle user credentials in your 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. Create a foreign table. Example:
    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 structure 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 The name of the Oracle table. The value must be in uppercase, and this parameter must be specified. 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 types 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, and 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 function 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. The statement is as follows:

IMPORT FOREIGN SCHEMA <ora_schema_name>
FROM SERVER <server_name>
INTO <schema_name>
OPTIONS (case 'lower');
Note case has the following 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 oracle_fdw

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

DROP EXTENSION oracle_fdw;