ApsaraDB RDS:Use the oracle_fdw plug-in

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.

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

Delete the oracle_fdw plug-in

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

DROP EXTENSION oracle_fdw;