This post is co-authored by Paula Berenguel, Principal Program Manager and reviewed by Akash Rao, Senior Program Manager, Guy Bowerman, Principal Group Program Manager, Jared Meade, Senior Product Manager, Varun Dhawan, Principal Product Manager. 

We are pleased to announce that Azure Database for PostgreSQL now supports Oracle foreign data wrapper, unlocking the ability for users to access and interact with Oracle data sources from within their PostgreSQL databases. The extension is available worldwide for PostgreSQL server versions 12 to 17.

What is Foreign Data Wrapper for Oracle? 

Simply put, the oracle_fdw extension acts as a bridge between Azure Database for PostgreSQL- Flexible Server and an Oracle database. It simplifies your workflow by enabling you to not only query but also update, insert, and delete Oracle data directly from your PostgreSQL environment. It includes support for pushdown of WHERE conditions and required columns, as well as comprehensive EXPLAIN support. 

Key Features of oracle_fdw 

The oracle_fdw extension offers a streamlined and efficient data access experience. It enhances your queries by smartly pushing down conditions and fetching only the columns you need, coupled with providing in-depth execution plans through its EXPLAIN support. This extension is transformative for database administrators, developers, and data analysts who need to work with Oracle data within PostgreSQL. 

Benefits of Using oracle_fdw in Azure Database for PostgreSQL 

Data Integration: Connect to Oracle databases without the hassle of setting up complex data synchronization or migration processes.  

Enhanced Productivity: Spend less time on data migration and more time gaining actionable insights, developing applications, and optimizing your data workflows. 

Cost-Effective: Reduce the need for additional data integration tools and simplify your tech stack, potentially lowering your overall system costs. 

Using Oracle Foreign Data Wrapper in Azure Database for PostgreSQL 

To start using the Oracle Foreign Data Wrapper in Azure Database for PostgreSQL, follow these steps: 

1. Create an Azure Database for PostgreSQL Instance 

Begin by setting up a new instance of Azure Database for PostgreSQL.  Quickstart: Create with Azure portal - Azure Database for PostgreSQL - Flexible Server | Microsoft Learn 

2. Enable & Install oracle_fdw Extension 

Once your PostgreSQL instance is up and running, you can install oracle_fdw extension by enabling the extension in the Server Parameters section of the Azure Database for PostgreSQL blade in Azure Portal, and then executing the following SQL command:  

CREATE EXTENSION oracle_fdw;

3. Create Objects in PostgreSQL and Query Remote Data from Oracle 

 Now, let us assume that we have an Oracle database called testpdb hosted by an instance running in host foo.fdwacme.com and listening on port 1521. With those details, we would construct a connection string that we can use to create a foreign server using the following SQL command. In the PostgreSQL side, we will refer to that server by a friendly name which, in this case, we've chosen oraserver.  

CREATE SERVER oraserver FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//foo.fdwacme.com:1521/testpdb');

 Then we can use the following command to create a user mapping to map the postgres_user in PostgreSQL to the oracle_user in Oracle database:  

CREATE USER MAPPING FOR postgres_user SERVER oraserver OPTIONS (user 'oracle_user', password 'oracle_password');

Note that the Oracle database user to which the PostgreSQL user is mapped, needs to have the CREATE SESSION privilege and the rights to execute the action on the SQL object that will be accessed as foreign table or view. Additionally, the firewall rules on the Oracle database should be set up to allow the PostgreSQL server machine to make a connection to the Oracle database. If the PostgreSQL user for which the mapping was created (postgres_user in the example above) is not a member of the azure_pg_admin role in the instance of PostgreSQL, you should also grant USAGE permission on the foreign server to that user. To do so, you can run the following command:

GRANT USAGE ON FOREIGN SERVER oraserver TO postgres_user;

 The above steps complete the setup for using oracle_fdw extension. Now, we can either create a foreign table for one of the tables in Oracle database, or we can import a whole foreign schema into PostgreSQL. The following example can be used to create a foreign table for an EMPLOYEE table in Oracle database: 

CREATE FOREIGN TABLE foreign_employee ( id integer OPTIONS (key 'true') NOT NULL, name character varying(30) ) SERVER oraserver OPTIONS (schema EMPLOYEE_SCHEMA, table 'EMPLOYEE');

 Now, you can use the table foreign_employee like a regular table in PostgreSQL. You can read data using SELECT statements, or you can update its contents using INSERT/UPDATE/DELETE statements.  

Alternatively, we can import a whole Oracle schema into PostgreSQL using the following SQL command:  

IMPORT FOREIGN SCHEMA "EMPLOYEE_SCHEMA" FROM SERVER oraserver INTO public;

 This will create foreign tables/views on PostgreSQL for each table/view in the EMPLOYEE_SCHEMA in the Oracle database server. After importing the foreign schema, you can use the psql meta-command \dE to show imported tables. The IMPORT FOREIGN SCHEMA command provides a list of options. For example, you can specify skipping tables or views, or you can set imported tables/views as readonly.   

Configuration Options for Oracle Foreign Data Wrapper 

The Oracle foreign data wrapper provides various options for the foreign data wrapper, foreign server, user mappings, foreign tables and columns. These options can be set while creating the objects using the above commands.  

Since it is expensive to create an Oracle session for each individual query, the connections created to the Oracle database are cached by oracle_fdw. All connections are closed automatically when the PostgreSQL session ends. However, you can also use the function oracle_close_connections() to close all cached Oracle connections while inside a PostgreSQL session.  

For UPDATE and DELETE queries on foreign tables to work, you need to make sure that when you define the foreign table in PostgreSQL by using the CREATE FOREIGN TABLE command, the columns corresponding to the primary key columns of the Oracle table must be designated with the “key” column option. These columns are used to uniquely identify a row in a foreign table, so make sure that the option is set on all columns that belong to the primary key. 

The EXPLAIN command on PostgreSQL will add a property identified as "Oracle query" which will show the text of query that is issued to Oracle database. For Oracle servers running version 10.1 or higher, EXPLAIN VERBOSE will also show Oracle’s own execution plan. Underneath the "Oracle query" property of the EXPLAIN output, there will be added as many additional rows identified as "Oracle plan" showing the plan produced by Oracle to execute the remote query.

You can also use the ANALYZE command to gather statistics on a foreign table. Since PostgreSQL will not automatically gather statistics for foreign tables during autovacuum, it is important to run ANALYZE on foreign tables after creation, and whenever the data stored in the remote table has changed significantly.

For a list of all available options and usage details, you can see the documentation for oracle_fdw extension. 

About Version of the Extension

If you have run CREATE EXTENSION already and you execute the following query:

SELECT * from pg_available_extensions WHERE name = 'oracle_fdw';

You would get an output like the following:

The values of those four columns are obtained from the control file of the extension https://github.com/laurenz/oracle_fdw/blob/master/oracle_fdw.control. From these locations specifically:

• name: from the name of the control file of the extension. It's oracle_fdw in the case of this extension.
• default_version: from the default_version setting declared in the control file. At the time of writing this article, it's version 1.2.
• installed_version: from column extversion in the pg_extension catalog, for the row where pg_extension.extname is equal to the value of name in this view. If the extension is not installed, because the CREATE EXTENSION command has not been executed yet, then this is filled with null.
• comment: from the comment setting declared in the control file.

Typically, extensions that include SQL artifacts such as functions, data types, operators, or index support methods, to name a few, bump up the value assigned to the default_version setting in their control file, whenever any of the SQL artifacts have changed (ie a new data type has been added, an existing table has been dropped, the signature of a user-defined function has changed its signature, etc. In the case of the oracle_fdw extension, that has only been the case in three occasions throughout its lifetime:

1. When the Initial revision was published. default_version was set to 1.0 at that point (Aug 25, 2011).
2. When it was added the function oracle_version(name) for diagnostic purposes, which was later renamed from oracle_version(name) to oracle_diag(name) before it was included in a release. default_version was set to 1.1 at that point (Feb 4, 2014).
3. When it was added a function "oracle_execute" to execute arbitrary SQL statements. default_version was set to 1.2 at that point (Jan 9, 2020).

Since then, the SQL artifacts packaged and distributed with the extension haven't changed.

However, the code that implements the functionality of all user-defined functions packaged in this extension is compiled and linked in a binary library, which is the result of compiling a C language file: https://github.com/laurenz/oracle_fdw/blob/master/oracle_fdw.c .

The reason why this extension implemented the oracle_diag(name) function in the first place, was so that each time the extension releases a new version that includes changes (bugfixes or enhancements) that don't change any of the packaged SQL objects, users of the extension still can find out what version of the binary they are using.

That version of the binary is defined as a macro that changes on every new release. Here you can find its value for the 2.7.0 release: https://github.com/laurenz/oracle_fdw/blob/ORACLE_FDW_2_7_0/oracle_fdw.h#L19

And when the oracle_diag() function is called it returns, among other things, that version number.

So, for example, if after having successfully executed CREATE EXTENSION oracle_fdw, you execute the following statement:

select oracle_diag();

You would get something like this:

More generically, the text would consist of these parts:

oracle_fdw %1, PostgreSQL %2, Oracle client %3.%4.%5.%6.%7, ORACLE_HOME=/opt/oracle/oracle_instantclient

Where:

• %1 corresponds to the release version of oracle_fdw.
• %2 corresponds to the minor version of PostgreSQL inside which your oracle_fdw extension is running.
• %3 .. %7 correspond to major, minor, update, patch, and port patch version numbers of the Oracle Client being used