SQL on Oracle

Overview

SQL on Oracle is a function of Extension Connector. This function enables the openGauss database instance to send SQL statements to an Oracle database (called the target database) for execution and obtain the execution result. The result can be used for join queries on local tables, or be recorded in local tables and file system.

Supported Oracle Versions

Oracle11.2.0.1 can be connected to Oracle12.2.0.1.

The Oracle ODBC driver version must be 12.2.0.1.0.

Supported Oracle Data Types

Table 1 shows the conversion relationship between Oracle data types and openGauss data types.

Table 1 Mapping between data types in Oracle and openGauss

NOTICE:

• To receive a type of data returned by Oracle, specify its corresponding data type in the AS clause. If the returned type of Oracle is not listed in the table or its corresponding data type is not specified, the type conversion may generate an incorrect result or fail. For example, the Oracle data type NUMBER cannot be converted.
• Data to be returned by Oracle cannot contain NaN or Inf.
• For a string of the CHAR(n) type in Oracle, if its length is less than n, it will be automatically padded with spaces. These spaces will be retained when the string is transferred to openGauss and converted to the TEXT type.
• If the encoding mode of openGauss is set to SQL_ASCII, the length() function returns the number of bytes of the string rather than the actual number of characters. For example, run the following statement to query for the length of data returned by exec_on_extension: select c2,length(c2) from exec_on_extension('oracle','select * from a;') as (c1 int, c2 text); The second column returned is the number of bytes of the string, rather than the number of characters.
• For the TIMESTAMP[(p)] WITH TIME ZONE data type, the time zone of the Oracle database must be the same as that of the local database. Otherwise, an error may occur.

Interconnection Configuration

SQL on Oracle uses the standard unixODBC-2.3.6 and Oracle ODBC-12.2 to connect to the Oracle database. Perform the following steps:

1. Log in as the OS user omm to the primary node of the database.

   Perform steps 2 to 7 on the node. Do not switch to other nodes.

2. (Optional) Configure the data source key files.

   The key files are used to encrypt the sensitive fields username and password in the data source. If the files are not set, the system uses the server.key.cipher and server.key.rand key files in the $GAUSSHOME/bin directory by default.

   1. Use gs_guc to generate key files.

      gs_guc encrypt –M source –K user key string –D  key file storage directory
      

      • The user key string must contain at least eight characters of three types.
      • Key files datasource.key.cipher and datasource.key.rand are generated. The file name must be fixed and cannot be changed.
      • The datasource.key.cipher and datasource.key.rand files must be placed to the $GAUSSHOME/bin directory on each node of the database instance.

   2. Save the key files to $GAUSSHOME/bin. Then, use the gs_om ec tool to send the key files to other nodes of the database instance.

      gs_om -t ec -m install --key-files --force
      

      For details, see “Server Tools > gs_om” in Tool Reference.

3. Prepare the package.zip package. Install and configure it.

   1. Download the following packages from the Oracle official website http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html and put the packages in the $GAUSSHOME/utilslib/fc_conf/$DSN path. If the path does not exist, create it first. $DSN indicates a folder naming after the DSN. The DSN name must consist of letters, digits, and underscores (_).

      • oracle-instantclient-basic-linux.x64-12.2.0.1.0.zip
      • oracle-instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
      • oracle-instantclient-odbc-linux.x64-12.2.0.1.0.zip

   2. Compress the three Oracle packages to package.zip.

      cd $GAUSSHOME/utilslib/fc_conf/$DSN
      zip -r package.zip oracle-instantclient-basic-linux.x64-12.2.0.1.0.zip oracle-instantclient-sqlplus-linux.x64-12.2.0.1.0.zip oracle-instantclient-odbc-linux.x64-12.2.0.1.0.zip
      

4. Set the listening parameters of Oracle and start the listening process.

   Settings in the following steps are provided for reference.

   1. Set the listener.ora file to add configurations to the listening list:

      cd $ORACLE_HOME/network/admin
      vim listener.ora
      

      Add the content similar to the following to the file. Skip this step if the content already exists.

      #Change the directory specified by ORACLE_HOME as needed.
      #ORCL indicates the name of the Oracle instance to be connected. Change it as needed.
      SID_LIST_LISTENER =
        (SID_LIST =
          (SID_DESC =
            (SID_NAME = ORCL)
            (ORACLE_HOME =/opt/oracle/db/product/11.1.0/db)
            (PROGRAM = extproc)
          )
          (SID_DESC =
            (SID_NAME = ORCL)
            (ORACLE_HOME =/opt/oracle/db/product/11.1.0/db)
          )
        )
      
      #HOST and PORT indicate the IP address and port number of Oracle, respectively. Set them as needed.
      
      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX)(PORT = XXXX))
          )
        )
      
      #/opt/oracle/db indicates the installation directory of Oracle, not the value of ORACLE_HOME. Change it as needed.
      ADR_BASE_LISTENER = /opt/oracle/db
      

   2. Run the following command to set the tnsnames.ora file to add a database instance:

      cd $ORACLE_HOME/network/admin
      vim tnsnames.ora
      

      Add the following database instance to the file. Skip this step if the instance already exists:

      #ORCL indicates the name of the Oracle instance to be connected. Change it as needed.
      #HOST and PORT indicate the IP address and port number of Oracle, respectively. Set them as needed.
      
      ORCL =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX)(PORT = XXXX))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = ORCL)
          )
        )
      

   3. Check the listening status.

      Run the lsnrctl command. Use status to view the status, start to enable listening, and stop to stop listening. The following is an example of the listening status of the database instance ORCL:

      lsnrctl
      LSNRCTL> status
      ...
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux-ss3q)(PORT=1521)))
      Services Summary...
      Service "orcl" has 1 instance(s).
        Instance "orcl", status READY, has 1 handler(s) for this service...
      Service "orclXDB" has 1 instance(s).
        Instance "orcl", status READY, has 1 handler(s) for this service...
      The command completed successfully
      

      You can also enter the following information on the terminal:

      lsnrctl stop      #Stop listening
      lsnrctl start     #Start listening
      

   4. (Optional) Configure a secure connection.

      The following steps take encrypting network data as an example. For details about configurations and other encryption methods, see Oracle documents by visiting the following website:

      https://docs.oracle.com/cd/E11882_01/network.112/e40393/asoconfg.htm#ASOAG020

      Perform the following steps to configure AES256:

      a. Configure the server (Oracle database).

      Modify the **sqlnet.ora **configuration file.

      cd $ORACLE_HOME/network/admin
      vim sqlnet.ora
      

      If AES256 needs to be used, append the following content to the file. Skip this step if the content already exists.

      SQLNET.ENCRYPTION_SERVER=REQUIRED
      SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)
      

      b. Configure the client (local openGauss database instance).

      Modify the $GAUSSHOME/utilslib/instantclient_12_2/network/admin/sqlnet.ora file as follows (add the file first if it does not exist):

      #NAMES.DIRECTORY_PATH specifies the parsing method.
      #ADR_BASE is the local home directory of Oracle, that is, the directory where ODBC is located.
      #The encryption method must be the same as that on the server. Here it is AES256.
      NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
      SQLNET.ENCRYPTION_CLIENT=REQUIRED
      SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)
      ADR_BASE = $GAUSSHOME/utilslib/instantclient_12_2  #Environment variables need to specify absolute paths.
      

      Modify the $GAUSSHOME/utilslib/instantclient_12_2/network/admin/tnsnames.ora file as follows (add the file first if it does not exist):

      #HOST and PORT indicate the IP address and port number of Oracle, respectively.
      #SERVICE_NAME=ORCL indicates the Oracle instance name.
      #Remote_ORCL indicates the instance name set by the user. Specify this name when encrypting the connection.
      Remote_ORCL =
        (DESCRIPTION =
          (ADDRESS_LIST=
              (ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX)(PORT = XXXX))
          )
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = ORCL)
          )
        )
      

      If the two files are used for the first time, ensure that users in the database instance have the read and write permissions on them.

      cd $GAUSSHOME/utilslib/instantclient_12_2/
      chmod -R 700 network
      

      NOTE: For transmission security purposes, you are advised to configure this secure connection.

5. Set the DSN configuration file.

   Go to the $GAUSSHOME/utilslib/fc_conf/$DSN directory and create the DSN.ini file. Ensure that the file content is correct. An example is as follows. Replace $GAUSSHOME and $DSN with the actual paths.

   • To configure a secure connection, add the following content (Omit the number sign [#] and the content following it.):

     [oracle]                               # DSN
     Driver=Oracle ODBC driver              #Oracle ODBC name
     Servername=Remote_ORCL                 #User-defined instance name
     Database=orcl                          #Name of the Oracle instance to be connected
     Port=XXXX                              #Oracle port number
     

   • If a secure connection is not required, add the following content (Omit the number sign [#] and the content following it.):

     [oracle]                                # DSN
     Driver=Oracle ODBC driver               #Oracle ODBC name
     Servername=//XX.XX.XX.XX:XXXX/orcl      #// Oracle Host IP address: port number/instance name
     Database=orcl                          #Name of the Oracle instance to be connected
     Port=XXXX                               #Oracle port number
     

     When you create a data source, the value of dsn is oracle defined in the DNS.ini file.

6. Run the following command to perform automatic deployment.

   Before installation, ensure that users in the database instance have read and write permissions on the files to be distributed. Then run the following command:

   gs_om -t ec -m add -N DSN -U username --type=oracle
   gs_om -t ec -m add -N DSN -U username --type=oracle -L  #-L indicates the local mode.
   

   NOTE: If the local mode is used, perform steps 1 to 5 on each node.

7. Install unixODBC (this step is required when Extension Connector is used and Chinese characters are used in the Oracle database).

   You are advised to install unixODBC using the source code.

   The source code is available at http://www.unixodbc.org/ and https://sourceforge.net/projects/unixodbc/files/unixODBC/.

   The 2.3.6 version is recommended. You are advised to install and deploy unixODBC as follows:

   a. Download and decompress the package.

   tar -xzvf unixODBC-2.3.6.tar.gz
   cd unixODBC-2.3.6
   

   b. Run configure using the GNU Compiler Collection (GCC).

   Run the following command to configure unixODBC. prefix indicates the installation directory specified by the user. The following installation directory is temporary. You are advised to specify an empty directory.

   ./configure --enable-gui=no --prefix=/tmp/unixODBC-2.3.6 --enable-iconv=yes --with-iconv-char-enc=enc
   

   NOTE:

   • Replace enc in the compilation parameter --with-iconv-char-enc=enc with the character encoding format of the Oracle database.
   • The common Chinese character encoding formats of the Oracle database are AL32UTF8 and ZHS16GBK.
   • If the Oracle encoding format is AL32UTF8, you are advised to set --with-iconv-char-enc to UTF8.
   • If the Oracle encoding format is ZHS16GBK, you are advised to set --with-iconv-char-enc to GB18030.

   c. Perform compilation.

   make
   make install
   

   d. Copy unixODBC to the specified installation directory.

   cd /tmp/unixODBC-2.3.6
   rm -rf $GPHOME/unixodbc/*
   cp -r /tmp/unixODBC-2.3.6/* $GPHOME/unixodbc/
   

   e. Change the permission and owner of the files in $GPHOME/unixodbc/.

   chmod -R 700 $GPHOME/unixodbc/*
   chown -R user:group $GPHOME/unixodbc/*
   

   NOTE: Replace user and group in the command with the database instance installation user and owner, respectively.

   f. Set the unixODBC configuration file. For details, see 4. Replace /usr/local/etc/ with $GPHOME/unixodbc/etc/.

   g. Set environment variables. For details, see 7.

   Modify the environmental variable NLS_LANG in the $GAUSSHOME/utilslib/env_ec file. If the variable does not exist in the file, add it.

   NOTE: You can log in to the Oracle database and run the following statement to query for the value of NLS_LANG:

   SELECT userenv('language') FROM sys_dummy;
   

   h. Copy the unixODBC file in step d to the $GPHOME/unixodbc/ directory of other data nodes in the database instance. Then perform steps e and f.

   NOTE: For a heterogeneous-OS database instance, group nodes by OS and compile unixODBC for each group separately (perform steps b to g).

8. Run the following command to restart the database instance and stop the om_monitor process so that the openGauss process can detect the environment variable change:

   gs_om -t ec -m restart -U username
   gs_om -t ec -m restart -U username -L   #-L indicates the local mode.
   gs_om -t stop && gs_om -t start         #Run this command only in local mode.
   

   NOTE:

   • If the local mode is used, perform steps 1 to 6 on each node.
   • In local mode, the database instance does not automatically start and stop. Therefore, you need to manually start and stop the database instance.

9. Create a data source.

   Perform the following steps:

   1. Perform the steps in Connecting to a Database.

   2. Create a data source.

      postgres=# CREATE DATA SOURCE ds_oracle TYPE 'ORACLE' OPTIONS (DSN 'oracle', USERNAME 'oracle_user', PASSWORD 'oracle_pwd', ENCODING 'UTF8');
      

      The DSN field for OPTIONS indicates the DSN of the Oracle database (the Oracle database connected in the previous step) in odbc.ini. The USERNAME and PASSWORD fields indicate the username and password of the ORCL instance (the database specified in odbc.ini) in the Oracle database, respectively. The ENCODING field indicates the encoding mode of Oracle character sets.

      To set PASSWORD in ds_oracle to new_pwd, run the following command:

      postgres=# ALTER DATA SOURCE ds_oracle OPTIONS (SET PASSWORD 'new_pwd');
      

      NOTE:

      • No matter whether you have performed operations in (Optional) Configure the data source key files., the Oracle username and password provided when you create and modify the data source will be encrypted and then stored in the system catalog pg_extension_data_source in the openGauss database instance.
      • If the character set of the Oracle database is Chinese, ensure that the values of ENCODING in the data source, NLS_LANG in $GAUSSHOME/utilslib/env_ec, and encoding format specified by the unixODBC compilation parameter --with-iconv-char-enc are consistent.
      • If the character set of the Oracle database is Chinese, it is recommended that the character encoding format of the local database be consistent with that of the remote Oracle database to prevent job execution failures caused by incompatible character sets.
      • After a data source is created, the key files cannot be modified. Otherwise, the data source cannot be used.
      • Ensure that the content in package.zip is complete and that the content in the DSN.ini file is correct. Do not modify them after the installation and deployment are complete. Otherwise, the environment probably cannot be used.
      • The created data source must be the same as that configured in the odbc.ini file. Otherwise, the data source cannot be found.

10. Connect to the Oracle database.

    After the preceding configuration is complete, if the database instance is normal, you can connect to the openGauss database and perform SQL operations on the Oracle database. For example, to query a table a(c1 number (9,0)) in Oracle, run the following command:

    postgres=# SELECT * FROM exec_on_extension('ds_oracle', 'select * from a;') AS (c1 int);
    

    NOTE:

    • Before using exec_on_extension, you need to create a data source.
    • If the sent SQL statement fails to be executed in Oracle, the connection function exec_on_extension will also fail to be executed.

Precautions

1. For details about the usage restrictions on the $GAUSSHOME/utilslib directory and the env_ec file, see “Server Tools > gs_om” in Tool Reference.

2. Users in the database must have the read and write permissions for the $GAUSSHOME/utilslib directory, its content, and the $GAUSSHOME/bin/datasource.key.* file. You are advised to check user permissions every time before you use gs_om ec, preventing upgrade and scale-out failures.

3. The relative position where gs_om ec deploys Extension Connector (excluding data source key files) is $GAUSSHOME/utilslib/fc_conf/$DSN, which is irrelevant to the current location. No matter in which path you run the command, the tool will be installed in the $DSN directory in $GAUSSHOME/utilslib/fc_conf.

   gs_om -t ec -m add -N DSN -U username --type=oracle
   

4. After a database administrator creates a data source (corresponding to a DSN) and grants its use permissions to a user, the user can use exec_on_extension to access the remote database corresponding to the DSN defined in the odbc.ini file.

Examples

-- Create a data source. username and password indicate the username and password used for connecting to the Oracle database.
postgres=# CREATE DATA SOURCE oracle VERSION '11g' OPTIONS (dsn 'oracle', username 'user_ora', password 'pwd_ora', encoding 'utf8');


-- Create a remote table, insert data, and query data.
postgres=# SELECT * FROM exec_on_extension('oracle', 'create table a (c1 int);') AS (c1 text);
 c1 
----
(0 rows)
postgres=# SELECT * FROM exec_on_extension('oracle', 'insert into a values (119);') AS (c1 text);
 c1 
----
(0 rows)
postgres=# SELECT * FROM exec_on_extension('oracle', 'insert into a select * from a;') AS (c1 text);
 c1 
----
(0 rows)
postgres=# SELECT * FROM exec_on_extension('oracle', 'select * from a;') AS (c1 int);
 c1  
-----
 119
 119
(2 rows)
postgres=# SELECT * FROM exec_on_extension('oracle', 'select * from a a1 inner join a a2 on a1.c1=a2.c1;') AS (c1 int, c2 int);
 c1  | c2  
-----+-----
 119 | 119
 119 | 119
 119 | 119
 119 | 119
(4 rows)

-- Save the query result to the local table.
postgres=# CREATE TABLE b AS SELECT * FROM exec_on_extension('oracle', 'select * from a group by c1;') AS (c1 int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
INSERT 0 1
postgres=# INSERT INTO b SELECT * FROM exec_on_extension('oracle', 'select * from a group by c1;') AS (c1 int);
INSERT 0 1

-- Associate the query result with the local table:
postgres=# SELECT * FROM b INNER JOIN (SELECT * FROM exec_on_extension('oracle', 'select * from a;') AS (c1 int)) a ON a.c1=b.c1;
 c1  | c2  
-----+-----
 119 | 119
 119 | 119
 119 | 119
 119 | 119
(4 rows)

-- Let other users use the data source.
postgres=# CREATE USER tmp_usr IDENTIFIED BY 'Gs@123456';

postgres=# GRANT USAGE ON DATA SOURCE oracle TO tmp_usr;

postgres=# \c - tmp_usr
postgres=#  SELECT * FROM exec_on_extension('oracle', 'select * from a group by c1;') AS (c1 int);
 c1  
-----
 119
(1 row)

-- Delete the data source, table, and user.
postgres=# \c - omm
postgres=# SELECT * FROM exec_on_extension('oracle', 'drop table a;') AS (c1 text);
 c1 
----
(0 rows)
postgres=# DROP DATA SOURCE oracle;

postgres=# DROP TABLE b;

postgres=# DROP USER tmp_usr;

Troubleshooting

For details about common issues that occur while Extension Connector interconnects with openGauss, see Table 2.

Table 2 Common troubleshooting for interconnection between Extension Connector and openGauss

Related Links

CREATE DATA SOURCE, SQL on Spark, SQL on openGauss, and “Server Tools > gs_om” in the Tool Reference.