External Data Wrapper for Oracle

oracle_fdw (foreign data wrapper for oracle) is an external data wrapper for Oracle and is an open source plug-in. openGauss is developed and adapted based on the open-source oracle_fdw Release 2.2.0.

To compile and use oracle_fdw, the Oracle development packages must be included in the environment. Therefore, openGauss does not compile oracle_fdw by default. The following describes how to compile and use oracle_fdw.

Compiling oracle_fdw

To compile oracle_fdw, you need to install the Oracle development library and header files.

Select a proper running environment and version, download Basic Package and SDK Package, and install them. In addition, SQLPlus Package is a client tool of the Oracle server. You can install it as required to connect to the Oracle server for testing.

After installing the development packages, start oracle_fdw compilation. Add the --enable-oracle-fdw option when running the configure command. Then perform compilation using the common openGauss compilation method. (See Software Compilation and Installation for details about openGauss compilation.)

After the compilation is complete, the oracle_fdw.so file is generated in lib/postgresql/ in the installation directory. SQL files and control files related to oracle_fdw are stored in share/postgresql/extension/ in the installation directory.

If the --enable-oracle-fdw option is not added during compilation and installation, compile oracle_fdw again after openGauss is installed, and then manually place the oracle_fdw.so file to lib/postgresql/ in the installation directory, and place oracle_fdw--1.0--1.1.sql, oracle_fdw--1.1.sql, and oracle_fdw.control to share/postgresql/extension/ in the installation directory.

Using oracle_fdw

To use oracle_fdw, install and connect to the Oracle server.
Load the oracle_fdw extension using CREATE EXTENSION oracle_fdw;.
Create a server object using CREATE SERVER.
Create a user mapping using CREATE USER MAPPING.
Create a foreign table using CREATE FOREIGN TABLE. The structure of the foreign table must be the same as that of the Oracle table. The first column in the table on the Oracle server must be unique, for example, PRIMARY KEY and UNIQUE.
Perform normal operations on the foreign table, such as INSERT, UPDATE, DELETE, SELECT, EXPLAIN, ANALYZE and COPY.
Drop a foreign table using DROP FOREIGN TABLE.
Drop a user mapping using DROP USER MAPPING.
Drop a server object using DROP SERVER.
Drop an extension using DROP EXTENSION oracle_fdw;.

Common Issues

When a foreign table is created on openGauss, the table is not automatically created in the Oracle database. You need to manually perform this operation.
The Oracle user used for executing CREATE USER MAPPING must have the permission to remotely connect to the Oracle database and perform operations on tables. Before using a foreign table, you can use the Oracle client on the machine where the openGauss server is located and use the corresponding user name and password to check whether the Oracle server can be successfully connected and operations can be performed.
When CREATE EXTENSION oracle_fdw; is executed, the message libclntsh.so: cannot open shared object file: No such file or directory is displayed. The reason is that the Oracle development library libclntsh.so is not in the related path of the system. You can find the specific path of libclntsh.so, and then add the folder where the libclntsh.so file is located to /etc/ld.so.conf. For example, if the path of libclntsh.so is /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1, add /usr/lib/oracle/11.2/client64/lib/ to the end of /etc/ld.so.conf. Run the ldconfig command for the modification to take effect. Note that this operation requires the root permission.

Precautions

SELECT JOIN between two Oracle foreign tables cannot be pushed down to the Oracle server for execution. Instead, SELECT JOIN is divided into two SQL statements and transferred to the Oracle server for execution. Then the processing result is summarized in openGauss.
The IMPORT FOREIGN SCHEMA syntax is not supported.
CREATE TRIGGER cannot be executed for foreign tables.