PostgreSQL provides foreign data wrappers (FDW) as a mechanism for accessing various external data sources. This article explains how to access Oracle databases using oracle_fdw, a foreign data wrapper for Oracle databases.

What is oracle_fdw?

The oracle_fdw extension is a foreign data wrapper that allows you to access Oracle table and views (including materialised views) via foreign tables.

When a PostgreSQL client accesses a foreign table, oracle_fdw accesses the corresponding data in the foreign Oracle database via the Oracle Call Interface (OCI) library on the PostgreSQL server.

Accessing Oracle databases
Accessing Oracle tables and views using oracle_fdw

How to use oracle_fdw

This section describes specific preparation and procedure for using oracle_fdw. It assumes that the PostgreSQL, oracle_fdw, and the OCI library have already been installed in the PostgreSQL server. It also assumes that the external table already exists in the Oracle database, and that the listener settings for remote connection have been completed.

Set up environment variables

Set the environment variables below to access the OCI library from oracle_fdw:

  • LD_LIBRARY_PATH: Specify the directory where the OCI library is installed (for example, in Linux: /usr/lib/oracle/18.5/client64/lib)
  • NLS_LANG: Specify the same value as NLS_LANG of the linked Oracle database

Prepare access to the Oracle database

For oracle_fdw to access the Oracle database, the settings below need to be specified.

Configuring PostgreSQL to access an Oracle table
1 Load oracle_fdw
2 Create the foreign server
3 Create a user mapping to the foreign server
4 Create the foreign table

1. Load oracle_fdw

Load the oracle_fdw extension into PostgreSQL, and then list the foreign data wrappers to make sure it has been created correctly.

$ psql
postgres=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION postgres=# \dew A                    List of foreign-data wrappers
    Name    |  Owner   |      Handler       |      Validator
------------+----------+--------------------+----------------------
 oracle_fdw | postgres | oracle_fdw_handler | oracle_fdw_validator
(1 row)
A \dew lists foreign data wrappers

2. Create the foreign server

Create the foreign Oracle server specifying the connection details to access it, and then list the foreign servers to make sure it was created correctly. Make sure that the operating system is configured in advance so that the host name can be resolved correctly.

postgres=# CREATE SERVER ora_sv FOREIGN DATA WRAPPER oracle_fdw
postgres-# OPTIONS (dbserver 'host_ora:1521/XEPDB1');
CREATE SERVER postgres=# \des+ A                                                       List of foreign servers
  Name  |  Owner   | Foreign-data wrapper |  Access privileges  | |            FDW options            |
--------+----------+----------------------+---------------------+ +-----------------------------------+
ora_sv | postgres | oracle_fdw           | postgres=U/postgres |  | (dbserver 'host_ora:1521/XEPDB1') |
(1 row)
A \des+ lists foreign servers

Next, grant privileges so that the local user postgres used on the PostgreSQL side can use the foreign server.

postgres=# GRANT USAGE ON FOREIGN SERVER ora_sv TO postgres;
GRANT

3. Create a user mapping to the foreign server

Create a user mapping for the local user postgres to the foreign server ora_sv, specifying the remote user ora_user and its password, and then list the user mappings to make sure it was created correctly.

postgres=# CREATE USER MAPPING FOR postgres SERVER ora_sv
postgres-# OPTIONS ( USER 'ora_user', PASSWORD 'xxxx');
CREATE USER MAPPING postgres=# \deu+ A                 List of user mappings
 Server | User name |          FDW options
--------+-----------+-------------------------------------
 ora_sv | postgres  | ("user" 'ora_user', password 'xxxx')
(1 row)
A \deu+ lists user mappings (note that the + option may display the remote password)

4. Create the foreign table

Create the foreign table corresponding to the Oracle table or view in the associated foreign server ora_sv, and then list the foreign tables and display the definition to make sure it was created correctly.

postgres=# CREATE FOREIGN TABLE f_ora_tbl(
postgres(# id int OPTIONS (key 'true'), A
postgres(# name varchar(64),
postgres(# t_data timestamp)
postgres-# SERVER ora_sv OPTIONS (SCHEMA 'ORA_USER' , TABLE 'ORA_TBL'); B
CREATE FOREIGN TABLE postgres=# \det+ C                              List of foreign tables
 Schema |   Table    | Server |               FDW options              | Description
--------+------------+--------+----------------------------------------+-------------
 public | f_ora_tbl  | ora_sv | (schema 'ORA_USER', "table" 'ORA_TBL') |
(1 row) postgres=# \d f_ora_tbl D                            Foreign table "public.f_ora_tbl"
 Column |            Type             | Collation | Nullable | Default | FDW options
--------+-----------------------------+-----------+----------+---------+--------------
 id     | integer                     |           |          |         | (key 'true')
 name   | character varying(64)       |           |          |         |
 t_data | timestamp without time zone |           |          |         |
Server: ora_sv
FDW options: (schema 'ORA_USER', "table" 'ORA_TBL')
A Specify the key keyword in the primary key column
B Specify the same capitalisation as Oracle's schema and table names
C Lists foreign tables
D Displays definition of foreign tables

Note the following when creating a foreign table:

  • The columns must be created with the same data types as the Oracle table or view.
    While oracle_fdw automatically converts data types when accessing foreign tables, it is recommended to check the result if there is data type discrepancy between the data sources.
  • If you want to update data on the Oracle side, you must specify OPTIONS (key 'true') for all primary key columns.
  • For the database schema and table names, use the same capitalisation as used in the Oracle side.
    Note that Oracle databases identifiers defined without double quotes are created using uppercase.
  • The schema and table names must be enclosed in single quotation marks in OPTIONS.

Database identifier capitalisation

As mentioned above, the capitalisation of the schema and table names defined for the foreign table in PostgreSQL must match exactly their definition in Oracle. Note that while no error is raised if a different capitalisation is specified during creation of the foreign table, an error will be raised when trying to access it.

postgres=# CREATE FOREIGN TABLE f_ora_tbl2(
postgres(# id int OPTIONS(key 'true'),
postgres(# name varchar(64),
postgres(# t_data timestamp)
postgres-# SERVER ora_sv OPTIONS (SCHEMA 'ora_user', TABLE 'ora_tbl');
CREATE FOREIGN TABLE A
 
postgres=# SELECT * FROM f_ora_tbl2; B
ERROR:  Oracle table "ora_user"."ora_tbl" for foreign table "f_ora_tbl2" does not exist or does not allow read access
DETAIL:  ORA-00942: table or view does not exist.
HINT:  Oracle table names are case sensitive (normally all uppercase).
A No error occurs during definition even if capitalisation of schema name or table name differs between PostgreSQL and Oracle
B Error occurs during access

Access the Oracle database

Now that you are ready to use the foreign table, issue a query to confirm that you can access it.

Accessing on Oracle table via the OCI
1 Client sends a query for the foreign table
2 PostgreSQL requests oracle_fdw to acquire the execution plan and table data of the Oracle table
3 oracle_fdw reads foreign server and user mapping information and returns access information
4 oracle_fdw sends the query to Oracle database via the OCI library
5 oracle_fdw acquires the result and returns it to PostgreSQL
6 Client receives the result

You can verify that the foreign table created is accessing the Oracle table intended, as follows:

1. Execute a query on the foreign table

postgres=# SELECT * FROM f_ora_tbl;
 id | name |           t_data
----+------+----------------------------
  1 | abc  | 2020-01-17 21:26:01.129322
  2 | LMN  | 2020-01-17 21:26:07.786242
  3 | XYZ  | 2020-01-17 21:26:13.957481
(3 rows)

2. Show the query plan

Use EXPLAIN ANALYZE VERBOSE to display the execution plan and make sure you are accessing the table on the Oracle side.

postgres=# EXPLAIN ANALYZE VERBOSE SELECT * FROM f_ora_tbl;
                                      QUERY PLAN
-----------------------------------------------------------------------------------------
 Foreign Scan on public.f_ora_tbl
   Output: id, name, t_data
   Oracle query: SELECT r1."ID", r1."NAME", r1."T_DATE" FROM "ORA_USER"."ORA_TBL" r1 A
   Oracle plan: SELECT STATEMENT B
   Oracle plan:   TABLE ACCESS FULL ORA_TBL

 Planning Time: 1.719 ms
 Execution Time: 1.019 ms
(7 rows)
A SQL statement executed on the Oracle side
B Execution plan on the Oracle side

In the return execution plan:

  • The row starting with Foreign Scan displays the table accessed on the Oracle side.
  • The row starting with Oracle query displays the SQL statement executed on the Oracle side (because we specified ANALYZE)
  • The rows starting with Oracle plan displays the execution plan on the Oracle side (because we specified VERBOSE)

3. Update the foreign table

Update the foreign table and check the result to confirm that it has been updated.

postgres=# UPDATE f_ora_tbl SET name='pot', t_date=CURRENT_TIMESTAMP WHERE id=2;
UPDATE 1
postgres=# SELECT * FROM f_ora_tbl;
 id | name |           t_date
----+------+----------------------------
  1 | abc  | 2020-01-17 21:26:01.129322
  2 | pot  | 2020-06-20 16:58:32.359565
  3 | XYZ  | 2020-01-17 21:26:13.957481
(3 rows)

Notes about using oracle_fdw

Using oracle_fdw without understanding its mechanism may result in excessive access times and affect system performance. To make the best use of the wrapper, note the following:

  • Pushdown

    Pushdown is a mechanism that foreign data wrappers deploy to allow the remote side to execute partial processing such as WHERE clauses in the client's SQL query. The following clauses are supported by oracle_fdw pushdown and are executed on the Oracle side conditionally:

    • WHERE is pushed down to the Oracle side.
    • ORDER BY is pushed down to the Oracle side when there is no JOIN and the sort order is common between PostgreSQL and Oracle database, such as for numeric data types.
    • JOIN is pushed down to the Oracle side for joins of up to 2 tables on the same foreign server in the SELECT statement.

    Note that WHERE and JOIN have the effect of reducing the amount of data transferred between the local and remote servers, which reduces the bottleneck in communication. We discuss pushdown further in part 2 of this article.

  • Update transactions

    Transaction isolation level is SERIALIZABLE when querying an Oracle database from oracle_fdw. Therefore, be careful when you update the same foreign table with multiple concurrent transactions because an error may occur due to serialisation failure.

    Prepared statements (PREPARE) and two-phase commit (PREPARE TRANSACTION, etc.) are not supported.

  • Difference in data types

    As mentioned above, foreign tables must be created with the same column data types as the Oracle side. But even then, there are differences in data lengths and how fractions are rounded. A run-time error will occur if a character type column that exceeds the data length is accessed.

  • Foreign table constraints and default values

    It is recommended to match the constraints (CHECK, NOT NULL, etc.) and default values (DEFAULT) of the remote table. Otherwise, UPDATEs and DELETEs on the foreign table may not be possible in cases where the result would violate a constraint.

Bulk creation of foreign tables

PostgreSQL's foreign data wrapper has a convenient feature that allows you to create foreign tables in bulk.

As described above, you can create a foreign table using CREATE FOREIGN TABLE, where you need to define each column according to its definition on the Oracle side. This can be a very laborious task if the number of tables is large.

But with IMPORT FOREIGN SCHEMA you can create foreign tables for each table defined in the imported schema, with the option to only import some of the tables from that schema. Note that the DEFAULT clause is not imported, so it must be added separately to columns later.

The Oracle database schema name must be enclosed in double quotes, because it must be specified in the same way as the information defined in the Oracle database system catalogue (the default is uppercase).

postgres=# CREATE SCHEMA imp_schema;
CREATE SCHEMA
postgres=# IMPORT FOREIGN SCHEMA "ORA_USER" FROM SERVER ora_sv INTO imp_schema;
IMPORT FOREIGN SCHEMA
postgres=# \det *.* A      List of foreign tables B
   Schema   |   Table   | Server
------------+-----------+--------
 imp_schema | dept      | ora_sv
 imp_schema | members   | ora_sv
 imp_schema | ora_tbl   | ora_sv
 imp_schema | personal  | ora_sv
 public     | f_ora_tbl | ora_sv
(5 rows)
A \det *.* lists foreign tables
B Imported tables are listed in the imp_schema schema
Continue here
For more details on accessing Oracle databases from your PostgreSQL environment, please continue to part 2 of this article.
PostgreSQL can be linked with databases other than Oracle databases and data sources other than relational databases, thus greatly expanding the scale for PostgreSQL implementation. Check here for an overview on foreign data wrappers.

More PostgreSQL Insider articles

This article concludes our discussion of the use of PostgreSQL's foreign data wrapper oracle_fdw, and how to use it more effectively.
Read more
This article explains how PostgreSQL Foreign Data Wrappers can access various external data sources such as Oracle, MySQL, SQL Server, and MongoDB, among others.
Read more
Explaining partitioning, the method for storing data in divided tables, based on declarative partitioning.
Read more

FUJITSU Enterprise Postgres is designed for developers, data architects, and DBAs seeking the efficiency of a leading open source based database technology with the enhanced security and performance features plus support required in a serious business environment.

Brochure

See how FUJITSU Enterprise Postgres enhances PostgreSQL providing 5 key enterprise capabilities for your mission-critical applications.

1st  page brochure FUJITSU Enterprise Postgeres - Fujitsu's enhanced open source PostgreSQL
VIEW BROCHURE

Read our latest blogs

Read our most recent articles regarding all aspects of PostgreSQL and FUJITSU Enterprise Postgres.