Use a DBLink to access data in a remote database|V4.3.5| docs|Distributed Database

Use a DBLink to access data in a remote database

Last Updated：2025-01-26 09:36:34 Updated

You can use a DBLink to access objects, such as tables, views, synonyms, sequences, stored procedures, and UDFs 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:

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);
```

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.

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

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

OceanBase Database allows you to call a stored procedure in a remote Oracle database or OceanBase's Oracle tenant by using a DBLink. In the current version, DBLink in Oracle mode of OceanBase Database supports the transmission of complex data types. These data types are not only basic types (such as integers and strings), but also nested complex structures. The supported nested data types are as follows:

record(basic): a record type that contains fields of basic types.
array(basic): an array type whose elements are of basic types.
array(record(basic)): an array type whose elements are of record types, and the fields of the record types are of basic types.

When you call a stored procedure in a remote OceanBase's Oracle tenant that contains parameters of complex data types by using a DBLink, note the following:

Only record(basic), array(basic), and array(record(basic)) complex data types are supported, and they must be defined in the package that directly calls the stored procedure.
The BINARY_DOUBLE data type is not supported for array(basic) or array(record(basic)) calls.
The float, timestamp, timestamp with time zone, and timestamp with local time zone data types are not supported for array parameters.
The char data type is supported for calling a native OceanBase stored procedure.

Before you call a stored procedure in a remote Oracle database by using a DBLink, note the following:

The char data type is not supported for calling a native Oracle stored procedure.
The following basic data types are supported for input and output parameters:
- 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 call synonyms for stored procedures or packages in the remote Oracle database, or synonyms for stored procedures or packages 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 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 OceanBase 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 for the stored procedure get_customer_id in the remote Oracle database.
```
obclient> 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``
```
obclient [SYS]> 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
```
obclient [SYS]> CALL syn_remote_customer_id@orcl_dblink(2);
```
- Call the synonym created in the local OceanBase database
```
obclient [SYS]> 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 packages, see Create a package.

Call a UDF in a remote database

Starting from OceanBase Database V4.2.2, you can call user-defined functions (UDFs) and package functions in a remote Oracle database through a DBLink. However, you cannot call UDFs and package functions in a remote OceanBase database.

Before you call a UDF or package function in a remote Oracle database through a DBLink, note the following considerations:

When you call a UDF, the following data types are supported for the input and output parameters:
- VARCHAR2, VARCHAR, and CHAR string data types
- NUMBER, INT, INTEGER, FLOAT, and DOUBLE numeric data types
- DATE data type
- TIMESTAMP data type
When you call a package function, the following data types are supported for the input and output parameters of the package type:
- Record type
  
  Note that the element type of the record type must be one of the following data types:
  - VARCHAR2, VARCHAR, and CHAR string data types
  - NUMBER, INT, INTEGER, FLOAT, and DOUBLE numeric data types
  - DATE data type
  - TIMESTAMP data type
However, note that in a remote Oracle database of an Oracle version earlier than Oracle 12c, the package type must be a data type that appears in the parameter position of a routine in the package. If the data type is defined only in the package but is not used in any routine parameter of the package, it cannot be called.
- Nested table or associative array
  
  Note that the element type of the nested table or associative array must be a record type or one of the following data types:
  - VARCHAR2, VARCHAR, and CHAR string data types
  - NUMBER, INT, INTEGER, FLOAT, and DOUBLE numeric data types
  - DATE data type
  - TIMESTAMP data type
You can call synonyms for functions or packages in a remote Oracle database or synonyms for remote functions or packages created in the local database.

The following examples illustrate how to call a UDF or package function through a DBLink. Assume that a DBLink named orcl_link has been created from a local OceanBase database 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 UDF.
1. Create a UDF named AddNumbers in the remote Oracle database.
```
SQL> CREATE OR REPLACE FUNCTION AddNumbers (num1 IN NUMBER, num2 IN NUMBER)
 RETURN NUMBER
 AS
 BEGIN
   RETURN num1 + num2;
 END;
 /
```
  The function accepts two numbers as input parameters and returns the sum of the two numbers.
2. Call the function in the local OceanBase database.
```
obclient [SYS]> SELECT AddNumbers@orcl_dblink(10, 20) AS Sum_Result FROM DUAL;
```
  The query result is as follows:
```
+------------+
| SUM_RESULT |
+------------+
|         30 |
+------------+
1 row in set
```
As with stored procedures, if you create synonyms for UDFs or packages in a remote Oracle database, you can call the synonyms in the local OceanBase database. For example, assume that you have created the following synonyms for the AddNumbers function:
- Create a synonym named syn_remote_AddNumbers for the AddNumbers function in the remote Oracle database.
```
SQL> CREATE OR REPLACE SYNONYM syn_remote_AddNumbers FOR AddNumbers;
```
- Create a synonym named syn_local_AddNumbers for the AddNumbers function through the DBLink in the local OceanBase database.
```
obclient [SYS]> CREATE OR REPLACE SYNONYM syn_local_AddNumbers FOR AddNumbers@orcl_dblink;
```
You can call these synonyms in the local OceanBase database. The calling statements are as follows:
- Call the synonym in the remote Oracle database.
```
obclient [SYS]> SELECT syn_remote_AddNumbers@orcl_dblink(10, 20) AS Sum_Result FROM DUAL;
```
- Call the synonym in the local OceanBase database.
```
obclient [SYS]> SELECT syn_local_AddNumbers(10, 20) AS Sum_Result FROM DUAL;
```
For more information about UDFs, see User-defined functions.

Call a package function.

Create a package named pac_employee_data in the remote Oracle database. The package contains a function named calculate_annual_bonus.

SQL> CREATE OR REPLACE PACKAGE pac_employee_data AS
   -- Define the function prototype.
   FUNCTION calculate_annual_bonus(emp_id IN NUMBER, monthly_salary IN NUMBER) RETURN NUMBER;
 END pac_employee_data;
 /

The package body is as follows.

SQL> CREATE OR REPLACE PACKAGE BODY pac_employee_data AS
   -- Implement the function.
   FUNCTION calculate_annual_bonus(emp_id IN NUMBER, monthly_salary IN NUMBER) RETURN NUMBER IS
     annual_bonus NUMBER;
   BEGIN
     -- Assume that the annual bonus is 10% of the annual salary.
     annual_bonus := monthly_salary * 12 * 0.1;
     RETURN annual_bonus;
   END calculate_annual_bonus;
 END pac_employee_data;
 /

Call the package function in the local OceanBase database.

You can call a function in a package through a PL block, as shown in the following example.

obclient [SYS]> SET SERVEROUTPUT ON;

obclient [SYS]> DECLARE
   emp_id CONSTANT NUMBER := 100;
   monthly_salary CONSTANT NUMBER := 5000;
   bonus_amount NUMBER;
 BEGIN
   -- Call the package function and obtain the result.
   bonus_amount := pac_employee_data.calculate_annual_bonus@orcl_dblink(emp_id, monthly_salary);

   DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id || ', Annual Bonus: $' || TO_CHAR(bonus_amount, '999,999.99'));
 END;
 /

The return result is as follows:

Employee ID: 100, Annual Bonus: $   6,000.00

Alternatively, you can call the package function through an SQL query, as shown in the following example.

obclient [SYS]> SELECT pac_employee_data.calculate_annual_bonus@orcl_dblink(100, 5000) AS "Annual Bonus" FROM DUAL;

The query result is as follows:

+--------------+
| Annual Bonus |
+--------------+
|         6000 |
+--------------+
1 row in set

For more information about packages, see Create a package.

References

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