PL Database Link

OceanBase Database allows you to remotely execute PL (procedural language) stored procedures and functions in the database. OceanBase Database supports the following types of DBLinks for remote calls:

• DBLink from an Oracle tenant of OceanBase Database to an Oracle database.
• DBLink from an Oracle tenant of OceanBase Database to another Oracle tenant of OceanBase Database.

The syntax for remotely calling a PL stored procedure is as follows:

CALL [pakage_name.]sp_name@dblink_name([parameter[,...]])

In the preceding syntax, sp_name specifies the name of the stored procedure to be called. @dblink_name specifies the name of the DBLink, indicating that the sp_name to be called is a remote stored procedure. pakage_name. specifies the name of the PL package.

The following example shows how to remotely call the proc1 (standalone procedure) of the dblink_test DBLink and the proc2 procedure of the pl_pkg1 PL package.

CALL proc1@dblink_test(1);
CALL pl_pkg1.proc2@dblink_test(1);

Remote calls to PL stored procedures support basic data types for input and output parameters. Supported basic data types include string types (VARCHAR2, VARCHAR, CHAR, etc.), numeric types (NUMBER, INT, INTEGER, FLOAT, DOUBLE, etc.), DATE, and TIMESTAMP.

The following example shows how to remotely call a PL stored procedure with an input parameter of the VARCHAR2 type.

DELIMITER /

DECLARE
    v1 VARCHAR2(10);
BEGIN
    proc3@dblink(1, v1);
END;
/

In a PL block, you can also call data types defined in a PL package of a remote DBLink. The syntax is as follows:

DECLARE
    var_name package_name.type_name[@dblink_name]
BEGIN
    ...
END;

In the preceding syntax, DECLARE defines a variable var_name whose data type is the data type defined in the package_name package of the remote DBLink specified by dblink_name. The name of the data type is type_name.

Remote calls to PL stored procedures support complex data types for input and output parameters. Complex data types include Record Type, Nested Table, and Associative Array. The element types of a Record Type can only be basic data types. The element types of a Nested Table and an Associative Array can only be basic data types or Record Types.

DBLinks in Oracle mode of OceanBase Database support the transmission of complex data types. This type usually includes not only basic types (such as integers and strings) but also nested complex structures. Supported nested types are as follows:

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

If the remote Oracle database is of version 12c or earlier, the data types defined in the package must be used as parameters of the FUNCTION or PROCEDURE in the package. If a data type is defined in the package but not used as a parameter of any FUNCTION or PROCEDURE, remote calls are not supported. For remote Oracle databases of higher versions, this restriction does not apply.

The following example shows how to define a package pkg1 and its data types in a remote database and then remotely call the data types defined in the package.

/* Define the package pkg1 in the remote database. */

DELIMITER /

CREATE OR REPLACE PACKAGE pkg1 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 proc4(param1 ty1, param2 ty2, param3 ty3 , param4 ty4, param5 ty5);
END;
/

/* Remotely call the data types defined in the package pkg1. */
DECLARE
    v1 pkg1.ty1@dblink_test;
    v2 pkg1.ty2@dblink_test;
    v3 pkg1.ty3@dblink_test;
    v4 pkg1.ty4@dblink_test;
    v4 pkg1.ty5@dblink_test;
BEGIN
    pkg1.proc4@dblink_test(v1, v2, v3, v4, v5);
END;
/

/* The following remote call is not supported because ty6 is not used as a parameter of the proc4 procedure in the package pkg1. */

DECLARE
    v1 pkg1.ty6@dblink_test;
BEGIN
    NULL;
END;

OceanBase Database also allows you to create synonyms for remote PL stored procedures and packages and create synonyms for remote PL objects in the local database.

/* Create a synonym for the remote PL stored procedure proc5. */
CREATE OR REPLACE SYNONYM syn_remote_p1 FOR proc5;

/* Create a synonym for the remote PL stored procedure proc6 in the local database.
CREATE OR REPLACE SYNONYM syn_local_p1 FOR proc6@oci_link;

You can call a remote PL stored procedure by using the following command.

CALL proc5@dblink_test(1);
CALL syn_remote_p1@dblink_test(1);
CALL syn_local_p1(1);

References

• CREATE DATABASE LINK syntax

• DROP DATABASE LINK syntax

• Access data in a remote database by using a DBLink