Use a DBLink to access data in a remote database

You can use a DBLink to access objects, such as tables, views, synonyms, and sequences, in a remote database.

Prerequisites

• You have created a DBLink. For more information about how to create a DBLink, see Create a DBLink.

• When you use a DBLink to access a remote Oracle database, if the local OceanBase database is of V4.2.1 or later, you must install and configure Oracle Call Interface (OCI) 12.2 on all OBServer nodes in the cluster. If the local OceanBase database was upgraded from an earlier version to V4.2.1 or later, you must upgrade the originally configured OCI 11.2 to OCI 12.2.

  For more information about how to install and configure OCI 12.2, see Install and configure OCI.

Access data of tables in a remote database

You can execute the SELECT... FROM table name@DBLink name statement to access data in a table in the remote database. Here is an example:

SELECT select_expr_list
      FROM table_name@dblink_name
      WHERE where_conditions;

select_expr_list:
    table_name.*
    | table_alias_name.*
    | expr [[AS] column_alias_name]

where

• select_expr_list specifies the expressions or column names to be queried. Separate the column names with commas (,). An asterisk (*) indicates all columns.

  • table_name.*: all columns in the specified table or view.

  • table_alias_name.: the alias of the table or view.

  • expr [[AS] column_alias_name]: the alias of the column or expression that you want to query. AS is optional.

• table_name@dblink_name specifies the tables to be queried in the remote database. Separate the tables with commas (,).

• where_conditions specifies the filter condition, which is optional. Only the data that meets the condition is returned.

For more information about the SQL statements for querying DBLinks, see SIMPLE SELECT.

Example: Use a DBLink named my_link to query data of the tbl1 table in the remote database:

obclient [SYS]> SELECT * FROM tbl1@my_link;

Access sequences in a remote database

Starting from OceanBase Database V4.2.1, you can access sequence values in a remote OceanBase database or Oracle database by using a DBLink.

Here is an example:

1. In the local database, create a table named tbl1 and a DBLink named my_link that connects to an Oracle tenant of a remote OceanBase database.

   • Create a DBLink named my_link

     obclient [SYS]> CREATE DATABASE LINK my_link CONNECT TO ob_user@oracle IDENTIFIED BY ****** HOST 'xx.xx.xx.xx:2881';
     

   • Create a table named tbl1

     obclient [SYS]> CREATE TABLE tbl1 (C1 int,C2 int);
     

2. Create a sequence named seq in the remote OceanBase database.

   obclient [SYS]> CREATE SEQUENCE seq START WITH 10000 INCREMENT BY 1 CACHE 50 NOCYCLE;
   

   Confirm that the next value of the sequence is 10000.

   obclient [SYS]> SELECT seq.nextval FROM DUAL;
   

   The result is as follows:

   +---------+
   | NEXTVAL |
   +---------+
   |   10000 |
   +---------+
   1 row in set
   

   For more information about operations on sequences, see Manage sequences.

3. In the local database, view the next and current values of the remote sequence by using the DBLink.

   • Query the next value

     obclient [SYS]> SELECT seq.nextval@my_link FROM DUAL;
     

     The query result is as follows:

     +---------+
     | NEXTVAL |
     +---------+
     |   10001 |
     +---------+
     1 row in set
     

   • Query the current value

     obclient [SYS]> SELECT seq.currval@my_link FROM DUAL;
     

     The query result is as follows:

     +---------+
     | CURRVAL |
     +---------+
     |   10001 |
     +---------+
     1 row in set
     

4. Insert the value of the remote sequence into the table in the local database by using the DBLink.

   obclient [SYS]> INSERT INTO tbl1(C1,C2) VALUES (1,seq.nextval@my_link);
   

   View the data in the table.

   obclient [SYS]> SELECT * FROM tbl1;
   

   The result is as follows:

   +------+-------+
   | C1   | C2    |
   +------+-------+
   |    1 | 10002 |
   +------+-------+
   1 row in set
   

To improve the performance of querying objects in the remote database, OceanBase Database may fail to obtain the latest CURRVAL value of a remote sequence in some scenarios. For example, if you execute the following statement, OceanBase Database sends the entire SELECT clause to the remote database for execution, and performs only data pull and insertion in the local database. After the statement is executed, you may not obtain the latest CURRVAL value by using the SELECT seq.currval#my_link FROM DUAL; statement.

INSERT INTO local_tbl1 SELECT remote_tbl1.NAME, seq.nextval@my_link FROM remote_tbl1@my_link;

Call a stored procedure in a remote database

Starting from OceanBase Database V4.2.1, you can call a stored procedure in a remote Oracle database by using a DBLink. At present, you cannot call stored procedures in a remote OceanBase database.

Before you call a stored procedure in a remote database by using a DBLink, note that:

• You can call only independent stored procedures and packages in a remote database by using a DBLink. User-defined functions (UDFs) and package functions cannot be remotely called.

• The following basic data types of inout parameters are supported in the call of an independent stored procedure:

  • String data types such as VARCHAR2, VARCHAR, and CHAR

  • Numeric data types such as NUMBER, INT, INTEGER, FLOAT, and DOUBLE

  • DATE

  • TIMESTAMP

• In the call of a package of stored procedures, inout parameters of the following composite data types are supported:

  • Record type

    The elements in a record must be of the following basic data types:

    • String data types such as VARCHAR2, VARCHAR, and CHAR

    • Numeric data types such as NUMBER, INT, INTEGER, FLOAT, and DOUBLE

    • DATE

    • TIMESTAMP

  However, if the remote Oracle database is of version 12c or earlier, the data types of parameters of a package must be any of the data types that appear in the routine parameters in the package. If the package type is defined only in the package but is not used in any routine parameters in the package, the package cannot be called.

  • Nested table and associative array

    The elements in a nested table or associative array must be of the record type or any of the following basic data types:

    • String data types such as VARCHAR2, VARCHAR, and CHAR

    • Numeric data types such as NUMBER, INT, INTEGER, FLOAT, and DOUBLE

    • DATE

    • TIMESTAMP

• You can use a DBLink to call the synonyms of a stored procedure or package in a remote Oracle database, and call the synonyms of remote objects created in the local database.

• You cannot use a DBLink to call remote packages of the constructor type.

Here are some examples: Assume that you have created, in the local OceanBase database, a DBLink named orcl_link to a remote Oracle database.

obclient [SYS]> CREATE DATABASE LINK orcl_dblink CONNECT TO orcl_user@oracle IDENTIFIED BY ****** OCI HOST 'xx.xx.xx.xx:1521/ORCL';

• Call a simple independent stored procedure

  1. Prepare the data environment.

     Create a table named customer_info to store customer information in the remote Oracle database.

     SQL> CREATE TABLE customer_info
            (id NUMBER,
            name VARCHAR2(20));
     

     Insert the following data into the table:

     SQL> INSERT INTO customer_info (id, name) VALUES (2, 'Jane Smith');
     

     SQL> INSERT INTO customer_info (id, name) VALUES (3, 'Mike Johnson');
     

     SQL> INSERT INTO customer_info (id, name) VALUES (4, 'Sarah Davis');
     

     Execute the SELECT * FROM customer_info; statement to query data in the table. The result is as follows:

             ID NAME
     ---------- --------------------
             2 Jane Smith
             3 Mike Johnson
             4 Sarah Davis
     

     Create a stored procedure named get_customer_id in the remote database to receive a customer_id parameter and return the ID of the customer.

     SQL> CREATE OR REPLACE PROCEDURE get_customer_id (
          customer_id IN NUMBER
      )
      IS
      BEGIN
          DBMS_OUTPUT.PUT_LINE('Customer ID is: ' || customer_id);
      END;
      /
     

  2. Call the stored procedure from the local database.

     obclient [SYS]> CALL get_customer_id@orcl_dblink(2);
     

However, if you call the stored procedure by using the following statement, which is not supported at present, an error is returned:

SELECT get_customer_id@orcl_dblink(2) FROM DUAL;

In addition, you can create synonyms of the get_customer_id stored procedure in the remote Oracle database by using the following methods:

• Create a synonym named syn_remote_customer_id in the remote Oracle database

  CREATE OR REPLACE SYNONYM syn_remote_customer_id FOR get_customer_id;
  

• Create a synonym named syn_local_customer_id in the local OceanBase database by using the DBLink``

  CREATE OR REPLACE SYNONYM syn_local_customer_id FOR get_customer_id@orcl_dblink;
  

You can use the DBLink to call these synonyms by executing the following statements:

• Call the synonym created in the remote Oracle database

  CALL syn_remote_customer_id@orcl_dblink(2);
  

• Call the synonym created in the local OceanBase database

  CALL syn_local_customer_id(2);
  

• Call a stored procedure that contains inout parameters

  Assume that you have created a table named employees to store employee information in the remote Oracle database by using the following statement:

  SQL> CREATE TABLE employees
        (name VARCHAR2(20),
        id NUMBER);
  

  You have created a stored procedure named get_employee_info to receive an input parameter p_employee_id and returns the name and ID of an employee. If the employee is not found, the stored procedure returns Employee not found. If other exceptions occur, the stored procedure returns Error occurred.

  SQL> CREATE OR REPLACE PROCEDURE get_employee_info (
      p_employee_id IN NUMBER,
      p_employee_name OUT VARCHAR2,
      p_employee_id_out OUT NUMBER
  )
  IS
  BEGIN
      SELECT name, id INTO p_employee_name, p_employee_id_out
      FROM employees
      WHERE id = p_employee_id;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          p_employee_name := 'Employee not found';
          p_employee_id_out := NULL;
      WHEN OTHERS THEN
          p_employee_name := 'Error occurred';
          p_employee_id_out := NULL;
  END;
  /
  

  The data types of the input parameter employee_id and output parameter employee_name meet the requirements. You can execute the following statements in the local database to call the stored procedure:

  obclient [SYS]> SET SERVEROUTPUT ON;
  

  obclient [SYS]> DECLARE
      v_employee_name VARCHAR2(20);
      v_employee_id NUMBER;
  BEGIN
      get_employee_info@orcl_dblink(1, v_employee_name, v_employee_id);
      DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
      DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
  END;
  /
  

  The preceding statement declares the v_employee_name and v_employee_id variables to receive the output of the stored procedure. The output is as follows:

  Employee Name: Employee not found
  Employee ID:
  

• Call a package of stored procedures with inout parameters of composite data types.

  The remote Oracle database contains a table named tbl1_v and a package named pkg1. The package type is nested table.

  SQL> CREATE TABLE tbl1_v(v varchar2(1000));
  

  SQL> CREATE OR REPLACE PACKAGE pkg1 AS
      TYPE ty1 IS TABLE OF varchar2(32767);
      TYPE ty2 IS TABLE OF NUMBER;
      v varchar2(1000);
  
      PROCEDURE pro1(param1 ty1);
      PROCEDURE pro1(param1 ty2);
      PROCEDURE pro1_out(param1 out ty1);
      PROCEDURE pro1_out(param1 out ty2);
      PROCEDURE pro1_inout(param1 in out ty1);
      PROCEDURE pro1_inout(param1 in out ty2);
  END;
  /
  

  The corresponding package body is as follows:

  SQL> CREATE OR REPLACE PACKAGE BODY pkg1 IS
      PROCEDURE pro1(param1 ty1) IS
      v varchar2(1024);
      BEGIN
        DELETE FROM tbl1_v;
        FOR idx IN param1.first .. param1.last LOOP
          v := 'param1(' || idx || ')=' || param1(idx) || ';';
          INSERT INTO tbl1_v values(v);
        END LOOP;
      END;
      PROCEDURE pro1(param1 ty2) IS
      BEGIN
        DELETE FROM tbl1_v;
        FOR idx in param1.first .. param1.last LOOP
          v := 'param1(' || idx || ')=' || param1(idx) || ';';
          INSERT INTO tbl1_v values(v);
        END LOOP;
      END;
     PROCEDURE pro1_out(param1 out ty1) IS
     BEGIN
        param1 := ty1();
        param1.extend(10);
        FOR idx in 1..10 LOOP
         param1(idx) := idx;
        END LOOP;
      END;
     PROCEDURE pro1_out(param1 out ty2) IS
     BEGIN
        param1 := ty2();
        param1.extend(10);
        FOR idx in 1..10 LOOP
         param1(idx) := idx;
        END LOOP;
      END;
   PROCEDURE pro1_inout(param1 IN OUT ty1) IS BEGIN pro1(param1); pro1_out(param1); END;
   PROCEDURE pro1_inout(param1 IN OUT ty2) IS BEGIN pro1(param1); pro1_out(param1); END;
   END;
  /
  

  You can call the package from the local OceanBase database by executing the following statements:

  obclient [SYS]> CREATE TABLE tbl_local (c1 int);
  

  obclient [SYS]> INSERT INTO tbl_local VALUES(1);
  

  obclient [SYS]> DECLARE
      param pkg1.ty2@orcl_dblink;
    BEGIN
      SELECT C1 bulk collect INTO param FROM tbl_local;
      pkg1.pro1@orcl_dblink(param);
    END;
    /
  

  However, for a package named pkg2 that is created by using the following statement:

  SQL> CREATE OR REPLACE PACKAGE pkg2 AS
      TYPE ty1 IS RECORD(c1 varchar2(10), c2 int);
      TYPE ty2 IS TABLE OF varchar2(10);
      TYPE ty3 IS TABLE OF ty1;
      TYPE ty4 IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER;
      TYPE ty5 IS TABLE OF ty1 INDEX BY PLS_INTEGER;
      TYPE ty6 IS TABLE OF varchar2(10);
      PROCEDURE pro2(param1 ty1, param2 ty2, param3 ty3 , param4 ty4, param5 ty5);
  END;
  /
  

  Its body is as follows:

  SQL> CREATE OR REPLACE PACKAGE BODY pkg2 AS
      PROCEDURE pro2 (param1 ty1, param2 ty2, param3 ty3 , param4 ty4, param5 ty5) IS
      BEGIN
          DBMS_OUTPUT.PUT_LINE('Parameter 1: ' || param1.c1 || ', ' || param1.c2);
          FOR i IN param2.FIRST .. param2.LAST LOOP
              DBMS_OUTPUT.PUT_LINE('Parameter 2(' || i || '): ' || param2(i));
          END LOOP;
          FOR i IN param3.FIRST .. param3.LAST LOOP
              DBMS_OUTPUT.PUT_LINE('Parameter 3(' || i || '): ' || param3(i).c1 || ', ' || param3(i).c2);
          END LOOP;
      END pro2;
  END pkg2;
  /
  

  If you call the pkg2 package by using the following method, an error is returned:

  obclient [SYS]> DECLARE
      param1 pkg2.ty1@orcl_dblink;
      param2 pkg2.ty2@orcl_dblink;
      param3 pkg2.ty3@orcl_dblink;
      param4 pkg2.ty4@orcl_dblink;
      param5 pkg2.ty5@orcl_dblink;
    BEGIN
      param1.c1 := 'a';
      param1.c2 := 123;
      param2 := pkg2.ty2@orcl_dblink('b', 'c');
      param3 := pkg2.ty3@orcl_dblink();
      param3.EXTEND(2);
      param3(1).c1 := 'd';
      param3(1).c2 := 456;
      param3(2).c1 := 'e';
      param3(2).c2 := 789;
  
      pkg2.pro2@orcl_dblink(param1, param2, param3, param4, param5);
    END;
    /
  

  In addition, if the remote Oracle database is of version 12c or earlier, an error is returned when the pkg2 package is called by using the following statement. This is because ty6 does not appear in the routine parameters of the package.

  obclient [SYS]> DECLARE
    v1 pkg2.ty6@orcl_dblink;
  BEGIN
    null;
  END;
  /
  

  For more information about how to use a package, see Create a program package.

References

For more information about operations on DBLinks, see the following topics:

• Create a DBLink

• Query existing DBLinks

• Use a DBLink to modify data in a remote database

• Drop a DBLink