dblink_ora provides a database link that is based on the Oracle Call Interface (OCI). In a PolarDB for PostgreSQL(Compatible with Oracle) database, you can use this link to execute SELECT, INSERT, UPDATE or DELETE statements on the data stored in an Oracle system.

If you want to connect to an Oracle database, you can download the OCI driver that is free of charge from the Oracle website. For more information, see OCI driver.

Connect to an Oracle database

If the Oracle Instant Client that you download does not include the libclntsh.so library, you must create a symbolic link named libclntsh.so that points to the downloaded version. Navigate to the Instant Client directory and run the following command:

ln -s libclntsh.so.version libclntsh.so

where, version specifies the version number of the libclntsh.so library. The following code block provides an example on the version.

ln -s libclntsh.so.12.1 libclntsh.so

Before you create a link to an Oracle server, you must specify the file path of the OCI driver in the PolarDB for PostgreSQL(Compatible with Oracle) database.

Set the LD_LIBRARY_PATH environment variable on Linux or PATH on Windows to the lib directory in which the Oracle client is installed.

You can set the value of the oracle_home configuration parameter in the postgresql.conf file only for Windows. The value specified by the oracle_home configuration parameter overwrites the PATH environment variable of Windows.

The LD_LIBRARY_PATH environment variable on Linux or the PATH environment variable or oracle_home configuration parameter on Windows must be correctly configured before you start the PolarDB for PostgreSQL(Compatible with Oracle) database.

When you use a Linux service script to start the PolarDB for PostgreSQL(Compatible with Oracle) database, make sure that the LD_LIBRARY_PATH parameter is configured in the service script. This way, the value of the LD_LIBRARY_PATH parameter can take effect when the script invokes the pg_ctl utility to start the PolarDB for PostgreSQL(Compatible with Oracle) database.

If you want to configure the oracle_home configuration parameter in the postgresql.conf file, add the following code block to the file. This operation is suitable only for Windows.

oracle_home = 'lib_directory '

Replace lib_directory with the name of the Windows directory that contains the oci.dll file.

After you configure the oracle_home configuration parameter, you must restart the server for the changes to take effect. You can restart the server from the Windows Services console.