oracle_fdw

oracle_fdw is an open-source plug-in. MogDB is developed and adapted based on the open-source oracle_fdw Release 2.2.0. oracle_fdw is provided as an extension.

 

Install Oracle Client

1. The oracle_fdw relies on the Oracle client so file, the client package needs to be downloaded using the official address and then unzipped.

   x86: https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

   ARM: https://www.oracle.com/database/technologies/instant-client/linux-arm-aarch64-downloads.html

   For example:

   [root@mogdb-kernel-002 ~]# cd /opt
   [root@mogdb-kernel-002 opt]# unzip instantclient-basic-linux.x64-21.11.0.0.0dbru.zip

2. Use the database installation user omm to modify the configuration file to add the path to ORACLE_HOME and add $ORACLE_HOME to the PATH and LD_LIBRARY_PATH.

   For example:

   [root@mogdb-kernel-002 opt]# su - omm
   [omm@mogdb-kernel-002 ~]$ vim /home/omm/.ptk_mogdb_env

   export ORACLE_HOME=/opt/instantclient_21_11
   export PATH=$GPHOME/ptk_tool/bin:$GAUSSHOME/bin:$GPHOME/script:$ORACLE_HOME:$PATH
   export LD_LIBRARY_PATH=$ORACLE_HOME:$GAUSSHOME/lib:$GPHOME/lib:$GPHOME/script/gspylib/clib:$LD_LIBRARY_PATH

   [omm@mogdb-kernel-002 ~]$ source ~/.ptk_mogdb_env

3. Restart the database

   [omm@mogdb-kernel-002 ~]# ptk cluster -n <cluster_name> restart

 

Install oracle_fdw

1. Access Download page of the MogDB official website, download the oracle_fdw extension for the version you need.

2. Unpack the package, for example:

   tar -xzvf oracle_fdw-1.0-5.0.0-01-Kylin-x86_64.tar.gz

3. Go to the directory where the extension is located and execute the make install command.

   cd oracle_fdw/
   make install

4. Connect to the database and execute the create extension oracle_fdw; to use it.

   MogDB=# CREATE EXTENSION oracle_fdw;
   CREATE EXTENSION

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

• Directly use the @ symbol plus dblink_name for DML operations on external tables, i.e. <oracle_table_name>@<dblink_name>. See Oracle DBLink Syntax Compatibility for a detailed example.

  Note: You can also create a foreign table using CREATE FOREIGN TABLE and perform normal operations on the foreign table, such as INSERT, UPDATE, DELETE, SELECT, EXPLAIN, ANALYZE and COPY. 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. Configure the external table's schema and table for Oralce both require uppercase, perform UPDATE or DELETE must set options(key 'true'), that is, set the primary key of the external table.

• Drop a foreign table usingDROP FOREIGN TABLE

• Drop a user mapping usingDROP 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 the MogDB, the table is not created on the Oracle server. You need to use the Oracle client to connect to the Oracle server to create a table.
• The Oracle user used for executing CREATE USER MAPPING must have the permission to remotely connect to the Oracle server and perform operations on tables. Before using a foreign table, you can use the Oracle client on the machine where the MogDB 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 the MogDB.
• The IMPORT FOREIGN SCHEMA syntax is not supported.
• CREATE TRIGGER cannot be executed for foreign tables.