Use the oracle_fdw extension to connect to an Oracle database

Updated at: 2025-02-25 11:26

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. The CIDR block of the VPC

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

  1. 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>');
  2. 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');
  3. 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

    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.

  • On this page (1)
  • Prerequisites
  • Background information
  • Usage notes
  • Create and delete the oracle_fdw extension
  • Use the the oracle_fdw extension
Feedback
phone Contact Us

Chat now with Alibaba Cloud Customer Service to assist you in finding the right products and services to meet your needs.

alicare alicarealicarealicare