dblink_ora v11

dblink_ora enables you to issue arbitrary queries to a remote Oracle server. It provides an OCI-based database link that allows you to SELECT, INSERT, UPDATE or DELETE data stored on an Oracle system from within Advanced Server.

Connecting to an Oracle Database

To enable Oracle connectivity, download Oracle's freely available OCI drivers from their website, presently at: http://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html

For Linux, if the Oracle instant client that you've downloaded 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 execute the following command:

ln -s libclntsh.so.<version> libclntsh.so

Where version is the version number of the libclntsh.so library. For example:

ln -s libclntsh.so.12.1 libclntsh.so

Before creating a link to an Oracle server, you must direct Advanced Server to the correct Oracle home directory. Set the LD_LIBRARY_PATH environment variable on Linux (or PATH on Windows) to the lib directory of the Oracle client installation directory.

Alternatively, you can also set the value of the oracle_home configuration parameter in the postgresql.conf file. The value specified in the oracle_home configuration parameter will override the LD_LIBRARY_PATH environment variable in Linux and PATH environment variable in Windows.

To set the oracle_home configuration parameter in the postgresql.conf file, edit the file, adding the following line:

oracle_home = 'lib_directory'

Substitute the name of the oracle_home path to the Oracle client installation directory that contains libclntsh.so in Linux and oci.dll in Windows for lib_directory.

After setting the oracle_home configuration parameter, you must restart the server for the changes to take effect. Restart the server,

• on Linux, using the systemctl command or pg_ctl services.

• on Windows, from the Windows Services console.