Use oracle_fdw

Eugenia Kuzina

Overview

The oracle_fdw extension is a foreign data wrapper to access Oracle databases. It allows you to query Oracle tables and views directly from the PostgreSQL interface using standard SQL statements. The main capabilities of the extension include the following:

  • Read and write operations — you can read data (SELECT command) and modify it using INSERT, UPDATE, and DELETE commands on the Oracle side.

  • Connection caching — the extension keeps Oracle sessions open during long-running transactions to avoid repeated authentication overhead. All connections are closed when the ADP/PostgreSQL session ends.

  • Type conversion — the extension automatically maps Oracle data types to the corresponding PostgreSQL data types.

The most common use cases are:

  • Migration — when moving IT systems from Oracle to ADP/PostgreSQL, the extension allows you to set up two-way data exchange so that applications can work simultaneously with both databases during the transition period.

  • System integration — if a company stores some of its data in Oracle, and new services are developed for ADP/PostgreSQL, oracle_fdw makes it possible to build reports and combine tables from two DBMSs.

Installation

The package required for the installation of the oracle_fdw extension is shipped with ADP. To use oracle_fdw, execute the CREATE EXTENSION command:

CREATE EXTENSION oracle_fdw;
NOTE
If the oracle_fdw extension is created in the template1 database used as the default template, all subsequently created databases will have this extension installed.

ADP uses the oracle_fdw version 2.8.0 . To check it, call the oracle_diag function:

SELECT oracle_diag();
                      oracle_diag
---------------------------------------------------------------
oracle_fdw 2.8.0, PostgreSQL 16.13, Oracle client 23.26.2.0.0

The extension automatically creates a foreign data wrapper named oracle_fdw. Typically, you only need to create a foreign server to connect to Oracle.

Change wrapper options

The oracle_fdw data wrapper has an optional nls_lang parameter that allows you to set the NLS_LANG environment variable for Oracle. If you need this functionality, you can execute the ALTER FOREIGN DATA WRAPPER command to set nls_lang:

ALTER FOREIGN DATA WRAPPER oracle_fdw
    OPTIONS (ADD nls_lang 'AMERICAN_AMERICA.AL32UTF8');

Note that with this approach, all changes will be lost when creating a dump and restoring it. To avoid this, you can create a new foreign data wrapper. The oracle_fdw extension includes the handler and validator functions that are necessary to create a foreign data wrapper. You can define a new foreign data wrapper as follows:

CREATE FOREIGN DATA WRAPPER custom_oracle_fdw
    HANDLER oracle_fdw_handler
    VALIDATOR oracle_fdw_validator
    OPTIONS (nls_lang 'AMERICAN_AMERICA.AL32UTF8');

Create a foreign server

Using oracle_fdw involves operations on foreign tables. Call the CREATE SERVER command to create a foreign server. Replace <dbserver.address.com> with the Oracle server address and specify Oracle System Identifier instead of <SID>:

CREATE SERVER ora_server FOREIGN DATA WRAPPER oracle_fdw
    OPTIONS (dbserver '//<dbserver.address.com>:1521/<SID>');

In the OPTIONS field, the CREATE SERVER command also accepts additional parameters described below.

Foreign server options
Name Description Default value

dbserver

The Oracle database connection string for the remote database. This is a required option

 — 

isolation_level

The transaction isolation level to use at the Oracle database. The value can be serializable, read_committed, or read_only.

Note that the Oracle table can be queried more than once during a single PostgreSQL statement, for example, during a nested loop join. To make sure that no inconsistencies caused by concurrent transactions occur, the transaction isolation level must guarantee read stability. This is only guaranteed with the Oracle SERIALIZABLE or READ ONLY isolation levels.

The Oracle implementation of SERIALIZABLE can cause serialization errors (ORA-08177) in unexpected situations, like inserts into a table. Using READ COMMITTED transactions works around this problem, but there is a risk of inconsistencies. If you need to use this isolation level, ensure that query execution plans do not involve multiple executions of foreign scans

serializable

nchar

If on, Oracle uses a more expensive character conversion. This is required if Oracle tables have the NCHAR or NVARCHAR2 columns with characters that cannot be represented in the Oracle database character set. Setting nchar to on has a noticeable performance impact, and it causes ORA-01461 errors with UPDATE statements including strings over 2000 bytes (or 16383 bytes if Oracle has the setting: MAX_STRING_SIZE = EXTENDED). This issue is caused by Oracle limitations

off

set_timezone

If on, the Oracle session time zone is set to the current value of the ADP parameter timezone when the connection to Oracle is established. This is only useful if you plan to use Oracle columns of type TIMESTAMP WITH LOCAL TIME ZONE and want to convert them to timestamp without time zone in ADP.

If you change a time zone after the Oracle connection has been established, oracle_fdw will not change the Oracle session time zone. You can call the oracle_close_connections() function to open a new connection with the new time zone next time you access a foreign table.

If Oracle does not recognize the time zone, connections will fail with the error: ORA-01882: timezone region not found. In this case, use a different time zone or set the option to off and set the ORA_SDTZ environment variable to an appropriate value on the ADP server

off

Create user mapping

It is a good practice to use superuser privileges when absolutely necessary, so it is recommended to grant a standard user (pguser in the example below) permissions to use the foreign server:

CREATE USER pguser;

GRANT USAGE ON FOREIGN SERVER ora_server TO pguser;

Access to remote data requires authentication on the Oracle side. For example, you can create an Oracle user as follows:

CREATE USER <ora_user> IDENTIFIED BY <ora_password>;

GRANT CREATE SESSION TO <ora_user>;

where:

  • <ora_user> — user name;

  • <ora_password> — user password.

Connect to ADP as pguser and create a user mapping object to specify a user name and password that can be used for the Oracle authentication of a current ADP/PostgreSQL role. To do this, execute the CREATE USER MAPPING command:

CREATE USER MAPPING FOR pguser SERVER ora_server
    OPTIONS (user '<ora_user>', password '<ora_password>');

To use external authentication, pass an empty string as the user value (<ora_user>). In this case, the operating system user postgres should have access to the Oracle server.

Create a foreign table

For example, there is the following table on the Oracle server:

CREATE TABLE ORA_USER.ORA_BOOKS (
    id NUMBER PRIMARY KEY,
    author_id NUMBER,
    title VARCHAR2(255 char),
    genre VARCHAR2(50 char),
    price NUMBER);

Execute the CREATE FOREIGN TABLE command to define a foreign table. When you define the field types of the foreign table, consider the possibility of type conversion:

CREATE FOREIGN TABLE books_oracle (
    id integer OPTIONS (key 'true') NOT NULL,
    author_id integer,
    title VARCHAR(255) NOT NULL,
    genre VARCHAR(50),
    price NUMERIC(10, 2)
) SERVER ora_server OPTIONS (schema 'ORA_USER', table 'ORA_BOOKS');

where:

  • ORA_USER — schema name (usually the same as the Oracle username);

  • ORA_BOOKS — Oracle table name.

Oracle table and schema names are typically written in uppercase.

Now you can use the books_oracle table as a regular ADP/PostgreSQL table.

Foreign table options
Name Description Default value

table

Oracle table name. This name should be written as it appears in the Oracle system catalog, usually in uppercase letters only.

To define a foreign table based on an Oracle query, set this option to the query enclosed in parentheses, for example:

OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')')

Do not set the schema option in this case.

The INSERT, UPDATE, and DELETE commands work on foreign tables defined by simple queries, if you want to avoid that, use the foreign table option readonly

required

dblink

Oracle database link through which the table is accessed. This name should be written as it appears in the Oracle system catalog, usually in uppercase letters only

optional

schema

Table schema (or owner) used to access tables that do not belong to the connecting Oracle user. This name should be written as it appears in the Oracle system catalog, usually in uppercase letters only

optional

max_long

The maximal length of the LONG, LONG RAW, and XMLTYPE columns in the Oracle table. Possible values are integers between 1 and 1073741823 (the maximal size of a bytea in ADP/PostgreSQL). This amount of memory will be allocated at least twice, so large values will consume a lot of memory.

If max_long is less than the length of the longest value retrieved, you will receive the error message ORA-01406: fetched column value was truncated

32767

readonly

The INSERT, UPDATE, and DELETE commands are allowed on tables where this option is set to no / off / false. Possible values: yes/no, on/off, true/false

false

sample_percent

Defines the percentage of Oracle table blocks that will be randomly selected to calculate PostgreSQL table statistics. The value should be between 0.000001 and 100. The option only affects the ANALYZE process and can be useful for analyzing large tables in a reasonable amount of time

100

prefetch

Sets the number of rows that will be fetched with a single round-trip between ADP and Oracle during a foreign table scan. The value should be between 1 and 10240.

Higher values can speed up performance but will use more memory on the ADP server and can lead to out-of-memory errors.

Note that there is no prefetching if the Oracle table contains columns of the type MDSYS.SDO_GEOMETRY

50

lob_prefetch

Sets the number of bytes that are prefetched for the BLOB, CLOB, and BFILE types. Values of these types that exceed the specified size will require additional round trips between ADP and Oracle, so setting this value bigger than the size of your typical LOB will improve performance

1048576

When you create a foreign table, the Oracle table columns are mapped to the ADP/PostgreSQL table columns in the order they are specified in the FOREIGN TABLE command. The ADP/PostgreSQL table can include more or fewer columns than the Oracle table. If it has more columns, and these columns are used, you will receive a warning, and oracle_fdw will return NULL values for the missing columns.

oracle_fdw includes only those columns in the Oracle query that are required by the PostgreSQL query.

Foreign table columns can have the following optional settings:

  • key — if set to yes/on/true, the corresponding column on the foreign Oracle table is considered a primary key column. The default value is false.

  • strip_zeros — if set to yes/on/true, ASCII 0 characters will be removed from the string during transfer. Such characters are valid in Oracle but not in ADP/PostgreSQL. This option only makes sense for character, character varying and text columns. The default value is false.

If you want to execute the UPDATE or DELETE commands, ensure that the key option is set on all columns that belong to the table primary key.

Populate the foreign table with data. To successfully insert rows into an Oracle table, you may need to change the isolation_level foreign server’s option (see Modify foreign data):

ALTER SERVER ora_server OPTIONS (ADD isolation_level 'read_committed');

Add rows to the books_oracle foreign table:

 INSERT INTO books_oracle (id,author_id, title, genre, price) VALUES
    (1, 1, 'Mrs. Dalloway', 'novel', 360),
    (2, 1, 'To the Lighthouse', 'novel', 440),
    (3, 2, 'To Kill a Mockingbird', 'novel', 750),
    (4, 3, 'The Great Gatsby', 'novel', 900),
    (5, 4, 'The Lord of the Rings', 'fantasy', 1200);

Read rows from the table:

SELECT * FROM books_oracle WHERE genre = 'fantasy';
 id | author_id |         title         |  genre  |  price
----+-----------+-----------------------+---------+---------
  5 |         4 | The Lord of the Rings | fantasy | 1200.00

Data type conversion

It is necessary to define the ADP/PostgreSQL columns with data types that oracle_fdw can convert. The oracle_fdw extension automatically handles the following conversions:

Oracle type ADP/PostgreSQL type

CHAR

char, varchar, text

NCHAR

char, varchar, text

VARCHAR

char, varchar, text

VARCHAR2

char, varchar, text, json

NVARCHAR2

char, varchar, text

CLOB

char, varchar, text, json

NCLOB

char, varchar, text, json

LONG

char, varchar, text

RAW

uuid, bytea

BLOB

bytea

BFILE

bytea (read-only)

LONG RAW

bytea

NUMBER

numeric, float4, float8, char, varchar, text

NUMBER(n,m) with m <= 0

numeric, float4, float8, int2, int4, int8, boolean, char, varchar, text

FLOAT

numeric, float4, float8, char, varchar, text

BINARY_FLOAT

numeric, float4, float8, char, varchar, text

BINARY_DOUBLE

numeric, float4, float8, char, varchar, text

DATE

date, timestamp, timestamptz, char, varchar, text

TIMESTAMP

date, timestamp, timestamptz, char, varchar, text

TIMESTAMP WITH TIME ZONE

date, timestamp, timestamptz, char, varchar, text

TIMESTAMP WITH LOCAL TIME ZONE

date, timestamp, timestamptz, char, varchar, text

INTERVAL YEAR TO MONTH

interval, char, varchar, text

INTERVAL DAY TO SECOND

interval, char, varchar, text

XMLTYPE

xml, char, varchar, text

MDSYS.SDO_GEOMETRY

geometry

If an Oracle value exceeds the size of the ADP/PostgreSQL column, you will receive a runtime error.

If NUMBER is converted to a boolean, 0 is interpreted as false, any other values — as true.

You can insert or update XMLTYPE values only if they do not exceed the maximum length of the VARCHAR2 data type (4000 bytes or 32767 bytes, depending on the Oracle MAX_STRING_SIZE parameter).

If you want to convert TIMESTAMP WITH LOCAL TIME ZONE to timestamp, consider setting the set_timezone option of the foreign server.

The data type geometry is only available when PostGIS is installed.

The oracle_fdw extension only supports the following geometry types: POINT, LINE, POLYGON, MULTIPOINT, MULTILINE, and MULTIPOLYGON in two and three dimensions. Empty PostGIS geometries are not supported because they have no equivalent in Oracle Spatial.

WHERE and ORDER BY clauses

ADP/PostgreSQL uses all applicable parts of the WHERE clause as a filter for the scan. oracle_fdw constructs an Oracle query containing a WHERE clause corresponding to these filter criteria. Since the WHERE clause is applied on the Oracle side, it can greatly reduce the number of rows retrieved from Oracle. This feature is also known as push-down of WHERE clauses.

ORDER BY is also executed on the Oracle side whenever possible. Note that an ORDER BY condition that sorts by a character string is not pushed down, as it is impossible to guarantee that the sort order in ADP/PostgreSQL matches the sort order in Oracle.

To successfully push down ORDER BY clauses, use simple conditions on the foreign table. Choose PostgreSQL column data types that correspond to the Oracle types; otherwise the conditions cannot be converted.

The expressions now(), transaction_timestamp(), current_timestamp, current_date, and localtimestamp are translated correctly.

The output of the EXPLAIN command shows the Oracle query that is used, so you can see which conditions were translated to Oracle and how. For example:

EXPLAIN SELECT * FROM books_oracle WHERE genre = 'fantasy';
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------------
Foreign Scan on books_oracle  (cost=10000.00..20000.00 rows=1000 width=658)
 Oracle query: SELECT /*7c63955190fa1d22*/ r1."ID", r1."AUTHOR_ID", r1."TITLE", r1."GENRE", r1."PRICE"
        FROM "ORA_USER"."ORA_BOOKS" r1 WHERE (r1."GENRE" = 'fantasy')

Joins between foreign tables

oracle_fdw can push down JOINs to the Oracle server. So, JOIN between two foreign tables leads to a single Oracle query that performs JOIN on the Oracle side. Such a JOIN operation has the following limitations:

  • Both tables must be defined on the same foreign server.

  • The JOIN operation between three or more tables is not pushed down.

  • JOIN should be in a SELECT statement.

  • oracle_fdw must be able to push down all JOIN conditions and WHERE clauses.

  • CROSS JOIN without JOIN conditions is not pushed down.

  • If JOIN is pushed down, ORDER BY clauses are not pushed down.

It is recommended to use ANALYZE to collect statistics on both foreign tables to determine the optimal strategy.

Example

Create another table on the Oracle server:

CREATE TABLE ORA_USER.ORA_AUTHORS (
    id NUMBER PRIMARY KEY,
    author_name VARCHAR2(100 char)
);

INSERT INTO ORA_USER.ORA_AUTHORS (id, author_name) VALUES
    (1, 'Virginia Woolf'),
    (2, 'Harper Lee'),
    (3, 'F. Scott Fitzgerald'),
    (4, 'J.R.R. Tolkien');

Create the corresponding foreign table in ADP:

CREATE FOREIGN TABLE authors_oracle (
    id integer OPTIONS (key 'true') NOT NULL,
    name VARCHAR(100)
) SERVER ora_server OPTIONS (schema 'ORA_USER', table 'ORA_AUTHORS');

Run the ANALYZE command:

ANALYZE authors_oracle;
ANALYZE books_oracle;

Execute the JOIN query:

SELECT authors_oracle.name, books_oracle.title
    FROM books_oracle INNER JOIN authors_oracle ON authors_oracle.id = books_oracle.author_id
    ORDER BY name;
        name         |         title
---------------------+-----------------------
 F. Scott Fitzgerald | The Great Gatsby
 Harper Lee          | To Kill a Mockingbird
 J.R.R. Tolkien      | The Lord of the Rings
 Virginia Woolf      | Mrs. Dalloway
 Virginia Woolf      | To the Lighthouse

Display the query plan:

EXPLAIN SELECT authors_oracle.name, books_oracle.title
    FROM books_oracle INNER JOIN authors_oracle ON authors_oracle.id = books_oracle.author_id
    ORDER BY name;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Sort  (cost=10200.43..10200.48 rows=20 width=531)
   Sort Key: authors_oracle.name
   ->  Foreign Scan  (cost=10000.00..10200.00 rows=20 width=531)
         Oracle query: SELECT /*39731fa1cb7295c3*/ r2."AUTHOR_NAME", r1."TITLE" FROM ("ORA_USER"."ORA_BOOKS" r1
            INNER JOIN "ORA_USER"."ORA_AUTHORS" r2 ON (r1."AUTHOR_ID" = r2."ID"))

In the output, you can see that JOIN is performed on the Oracle side.

Modify foreign data

oracle_fdw supports the INSERT, UPDATE, and DELETE commands on foreign tables. These operations are allowed by default and can be disabled by setting the readonly table option.

If you omit a foreign table column during INSERT, that column is set to the value defined in the DEFAULT clause of the ADP/PostgreSQL foreign table (or NULL if there is no DEFAULT clause). DEFAULT clauses on the corresponding Oracle columns are not used. If the ADP/PostgreSQL foreign table does not include all columns of the Oracle table, the Oracle DEFAULT clauses will be used for the columns not included in the foreign table definition.

The RETURNING clause of the INSERT, UPDATE, and DELETE commands is supported except for columns of the LONG and LONG RAW Oracle data types.

Triggers on foreign tables are supported, but triggers defined with AFTER and FOR EACH ROW require that the foreign table has no columns of the LONG or LONG RAW Oracle data types, since these triggers use the RETURNING clause mentioned above.

Although modifying foreign data is supported, performance may be poor, especially when processing large numbers of rows. This is due to the specifics of oracle_fdw, which requires processing each row individually.

Transactions are forwarded to Oracle, so the BEGIN, COMMIT, ROLLBACK, and SAVEPOINT commands work as expected. Prepared statements involving Oracle are not supported. See Internals.

Since oracle_fdw uses serialized transactions by default, it is possible that data-modifying statements lead to a serialization failure: ORA-08177: can’t serialize access for this transaction. This can happen if concurrent transactions modify the same table, and is more likely in case of long-running transactions. Such errors can be identified by the SQLSTATE (40001) code. An application using oracle_fdw should retry transactions that fail with this error.

It is also possible to use a different transaction isolation level, see Create a foreign server.

Collect statistics on foreign tables

You can use the ANALYZE command to gather statistics on a foreign table. Without statistics, ADP cannot estimate the row count for queries on a foreign table, which can cause inappropriate execution plans to be chosen.

ADP does not automatically collect statistics for foreign tables using the autovacuum daemon as it does for regular tables, so you need to run ANALYZE on foreign tables after they are created and whenever a remote table changes significantly.

Note that analyzing an Oracle foreign table will result in a full sequential table scan. You can set the sample_percent table option to speed this up by using only randomly selected blocks of the Oracle table.

The ADP/PostgreSQL EXPLAIN command shows the query that is issued to Oracle. EXPLAIN VERBOSE displays the Oracle execution plan. Execute the EXPLAIN VERBOSE command with the JOIN query from the Joins between foreign tables section:

EXPLAIN VERBOSE SELECT authors_oracle.name, books_oracle.title
    FROM books_oracle INNER JOIN authors_oracle ON authors_oracle.id = books_oracle.author_id
    ORDER BY name;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=10200.43..10200.48 rows=20 width=531)
   Output: authors_oracle.name, books_oracle.title
   Sort Key: authors_oracle.name
   ->  Foreign Scan  (cost=10000.00..10200.00 rows=20 width=531)
         Output: authors_oracle.name, books_oracle.title
         Oracle query: SELECT /*4bab0a18b3951d4f*/ r2."AUTHOR_NAME", r1."GENRE"
            FROM ("ORA_USER"."ORA_BOOKS" r1 INNER JOIN "ORA_USER"."ORA_AUTHORS" r2 ON (r1."TITLE" = r2."ID"))
         Oracle plan: SELECT STATEMENT
         Oracle plan:   HASH JOIN   (condition "R2"."ID"=TO_NUMBER("R1"."TITLE"))
         Oracle plan:     NESTED LOOPS
         Oracle plan:       NESTED LOOPS
         Oracle plan:         STATISTICS COLLECTOR
         Oracle plan:           TABLE ACCESS FULL ORA_BOOKS
         Oracle plan:         INDEX UNIQUE SCAN SYS_C008644 (condition "R2"."ID"=TO_NUMBER("R1"."TITLE"))
         Oracle plan:       TABLE ACCESS BY INDEX ROWID ORA_AUTHORS
         Oracle plan:     TABLE ACCESS FULL ORA_AUTHORS
 Query Identifier: -5125250772757756403

Support for IMPORT FOREIGN SCHEMA

The IMPORT FOREIGN SCHEMA command supports bulk import of all table definitions from an Oracle schema. In addition to the IMPORT FOREIGN SCHEMA command documentation, consider the following:

  • IMPORT FOREIGN SCHEMA creates foreign tables for all objects found in the ALL_TAB_COLUMNS data dictionary view. That includes tables, views, and materialized views, but not synonyms.

  • The Oracle schema name should be written exactly as it is in Oracle, usually in uppercase. Since ADP/PostgreSQL translates names to lower case before processing, you should protect the schema name with double quotes (for example "SCHEMA1").

  • Table names in the LIMIT TO or EXCEPT clause should be written as they will appear in ADP/PostgreSQL after applying the case-change rules defined by the IMPORT FOREIGN SCHEMA options.

The following options can be specified in the OPTIONS field of the IMPORT FOREIGN SCHEMA command.

IMPORT FOREIGN SCHEMA options
Name Description

case

Controls case folding for table and column names during import. The possible values are:

  • keep — keep the names the same as in Oracle, usually in uppercase;

  • lower — convert all table and column names to lowercase;

  • smart — convert only those names that are written entirely in uppercase in Oracle (this is the default value).

collation

A collation used for case folding for the lower and smart values of the case option.

The default value is default, which corresponds to the default collation rule of the database. Only collations in the pg_catalog schema are supported. For a list of possible values, see the collname field in the pg_collation catalog

dblink

Oracle database link through which the schema is accessed. This name should be written as it appears in the Oracle system catalog, usually in uppercase letters only

readonly

Sets the readonly table option on all imported tables

skip_tables

Specifies whether to skip tables during the import. The default value is false

skip_views

Specifies whether to skip views during the import. The default value is false

skip_matviews

Specifies whether to skip materialized views during the import. The default value is false

max_long

Sets the max_long table option on all imported tables

sample_percent

Sets the sample_percent table option on all imported tables

prefetch

Sets the prefetch table option on all imported tables

lob_prefetch

Sets the lob_prefetch table option on all imported tables

nchar

Sets the nchar server option on all imported tables

set_timezone

Sets the set_timezone server option on all imported tables

The following code creates the schema_oracle schema and imports the ORA_USER schema from the Oracle server into it:

CREATE SCHEMA schema_oracle;

IMPORT FOREIGN SCHEMA "ORA_USER" from SERVER ora_server into schema_oracle;

Display the existing foreign tables to check the result:

SELECT foreign_table_schema AS schema_name,
       foreign_table_name AS table_name,
       foreign_server_name AS server_name
    FROM information_schema.foreign_tables
    WHERE foreign_table_schema = 'schema_oracle';
  schema_name  | table_name  | server_name
---------------+-------------+-------------
 schema_oracle | ora_authors | ora_server
 schema_oracle | ora_books   | ora_server

Functions created by the extension

oracle_fdw_handler and oracle_fdw_validator

The oracle_fdw_handler and oracle_fdw_validator functions are necessary to create a foreign data wrapper:

FUNCTION oracle_fdw_handler() RETURNS fdw_handler
FUNCTION oracle_fdw_validator(text[], oid) RETURNS void

You can find an example above.

oracle_close_connections

The function can be used to close all open Oracle connections in this session.

FUNCTION oracle_close_connections() RETURNS void

oracle_fdw caches Oracle connections because it is expensive to create an Oracle session for each query. All connections are closed when the ADP/PostgreSQL session ends.

The oracle_close_connections() function can be useful for long-running sessions that do not access foreign tables all the time, and you want to avoid blocking the resources required to keep an open connection to Oracle.

Example:

SELECT oracle_close_connections();

You cannot call this function inside a transaction that modifies Oracle data.

oracle_diag

This function is useful for diagnostic purposes only.

FUNCTION oracle_diag(<server_name> DEFAULT NULL) RETURNS text

where <server_name> is the name of a foreign server.

The function returns the versions of oracle_fdw, PostgreSQL server, and Oracle client. If called with no argument or NULL, it additionally returns the values of environment variables used for establishing Oracle connections:

SELECT oracle_diag(NULL);
                                        oracle_diag
--------------------------------------------------------------------------------------------
 oracle_fdw 2.8.0, PostgreSQL 16.13, Oracle client 23.26.2.0.0, ORACLE_HOME=/usr/lib/oracle

If called with the name of a foreign server, it also returns the Oracle server version:

SELECT oracle_diag('ora_server');
                                       oracle_diag
-----------------------------------------------------------------------------------------
 oracle_fdw 2.8.0, PostgreSQL 16.13, Oracle client 23.26.2.0.0, Oracle server 23.0.0.0.0

oracle_execute

The function allows you to execute SQL statements that do not return results (typically DDL statements) on the remote Oracle server.

FUNCTION oracle_execute(<server_name>, <query_text>) RETURNS void

where:

  • <server_name> — the name of a foreign server;

  • <query_text> — query text.

For example, you can add the in_stock column to the ORA_BOOKS table on the Oracle server:

SELECT oracle_execute('ora_server', 'ALTER TABLE ORA_USER.ORA_BOOKS ADD in_stock NUMBER');

Be careful when using this function, since it may impact the oracle_fdw transaction management. Executing DDL statements in Oracle is followed by an implicit COMMIT command. It is not recommended to use this function in transactions with multiple statements.

Found a mistake? Seleсt text and press Ctrl+Enter to report it