Start  trial

    Start trial

      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 materialized 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

      1Load 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

      2Create 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;

      3Create 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)

      4Create 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 capitalization 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 capitalization 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 capitalization

      As mentioned above, the capitalization 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 capitalization 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');
      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 capitalization 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 an 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:

      1Execute 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)

      2Show 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)

      3Update 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 serialization 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;
      postgres=# IMPORT FOREIGN SCHEMA "ORA_USER" FROM SERVER ora_sv INTO imp_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.
      This article explains how PostgreSQL Foreign Data Wrappers can access external data sources such as Oracle, MySQL, and SQL Server among others.
      Explaining partitioning, the method for storing data in divided tables, based on declarative partitioning.

      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.


      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.