PolarDB:oracle_fdw

Usage

1. Install the oracle_fdw extension.

   CREATE EXTENSION oracle_fdw;

2. Create an external Oracle database server.

   Configure the connection information of the Oracle database and create a mapping from PolarDB for PostgreSQL (Compatible with Oracle) to Oracle. The following is an example:

   CREATE SERVER oradb
   FOREIGN DATA WRAPPER oracle_fdw
   OPTIONS (dbserver '//host:port/dbname');

   The parameters for creating an external Oracle database server are as follows:

   Parameter	Description	Required
   dbserver	The connection string of the Oracle database includes the IP address, port number, and database name.	Yes
   isolation_level	Transaction isolation level, the default is SERIALIZABLE. Optional parameter types are as follows: serializable: serializable. read_committed: read committed. read_only: read-only.	No
   nchar	Whether to enable character conversion on the Oracle side. Note Modifications may affect performance.	No

3. Create a user mapping.

   Map the user pguser of PolarDB for PostgreSQL (Compatible with Oracle) to the user orauser of the corresponding Oracle database. You need to configure the username and password of the Oracle database user. The example is as follows:

   CREATE USER MAPPING
   FOR pguser SERVER oradb
   OPTIONS (user 'orauser', password 'orapwd');

   The parameters for creating user mapping are as follows:

   Parameters	Description	Required
   user	User name of the Oracle database user.	Yes
   password	Password of the Oracle database user.	Yes

   Note

   If you do not want to store the Oracle user’s credentials in PolarDB for PostgreSQL (Compatible with Oracle), set user to an empty string and provide external authorization.

4. Create an external table mapping.

   Create an external table in PolarDB for PostgreSQL (Compatible with Oracle) and map it to an existing table in the Oracle database. The example is as follows:

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

   The parameters for creating an external table are as follows:

   Parameters	Description	Required
   table	The mapped table name in the Oracle database is usually uppercase.	Yes
   dblink	The database link of the mapped table must be exactly the same as that in the Oracle system catalog.	No
   schema	The Oracle user name, which needs to be uppercase, is used to access tables that do not belong to the currently connected user.	No
   max_long	Limit the maximum length of LONG, LONG RAW, and XMLTYPE type columns in Oracle tables.	No
   sample_percent	The proportion of Oracle table data that PostgreSQL uses to randomly select for statistics (The default is 100).	No
   prefetch	The number of rows of data transferred between PostgreSQL and Oracle at one time when scanning a foreign table.	No
   key	Whether the current column is included in the primary key constraint.	No
   strip_zeros	When transferring data, whether to remove the ASCII 0 character at the end of the string (legal in Oracle, illegal in PostgreSQL).	No

   Note

   • The structure definition of the external table must be completely consistent with the structure of the mapped table on the Oracle database.

   • For the data type mapping of oracle_fdw from PolarDB for PostgreSQL (Compatible with Oracle) to Oracle, see Type mapping table.

5. Usage.

   You can operate external tables in the same way as you operate ordinary PolarDB for PostgreSQL (Compatible with Oracle) tables to manipulate tables in a remote Oracle database.

6. Uninstall the plugin.

   DROP EXTENSION oracle_fdw;

Type mapping table

In the oracle_fdw plug-in, the data type mappings for PolarDB for PostgreSQL (Compatible with Oracle) are as follows:

Related references

For more advanced usage of oracle_fdw, see the official documentation of oracle_fdw.