Foreign Data Wrapper Between PostgreSQL and Oracle

In the article “Postgresql Foreign Data Wrapper“, we created Foreign Data Wrapper to read data between two PostgreSQLs.

And we have created Foreing Data Wrapper Between PostgreSQL and MSSQL in the article named “PostgreSQL Foreign Data Wrapper Between PostgreSQL and MSSQL“.

In this article, we will create Foreign Data Wrapper between PostgreSQL and Oracle.

PREPARATION:

When installing Oracle Foreign Data Wrapper, we need to install oracle instant client.

In the instant client, we need to install the following rmps;

instantclient-basic_linux,
instantclient_sdk_linux,
instantclient_sqlplus

For installation, please refer to the article titled “How To Install Oracle Client For PostgreSQL On Centos“.

If you have a standard installation on Postgresql, you should download and install postgresql-devel and postresql-lib rpm packages according to your version.

After downloading and installing the required oracle_client version, you can install oracle_fdw by following the steps below.

First we download the Oracle_fdw extension, you can use the link below to download.

https://github.com/laurenz/oracle_fdw

After downloading Oracle_fdw extension files and transferring them to any folder on our machine, we go the folder and execute the following command.

After running this command, the result should return as follows.

You may receive errors as follows when you run this command. You can find solutions to these errors in the following articles.

“make: gcc : Command not found make:*** [oracle_fdw.o] Error 127″,

“Oracle_fdw oracle_utils.c:22:17: fatal error: oci.h: No such file or directory”

After the above operation is completed, you can continue by running the next command.

Finally, we execute the following command and our operations at the operating level are completed.

The purpose of this process is to compile the PostgreSQL code and the oracle_fdw code together.

CREATE EXTENSION:

After connecting to postgres with psql, we run the following command to create the extension.

CREATE SERVER:

After the extension is installed, we create the server as in other extension installations.

USER MAPPING:

We create user mapping.

CREATE FOREIGN TABLE:

And finally, we create a foreign table.