PL Database Link

OceanBase Database supports remote execution of PL (procedural language) stored procedures and functions, including standalone stored procedures and those within packages, as well as functions within packages. The supported DBLink types for remote calls in OceanBase Database are as follows:

• DBLink from an Oracle-compatible mode of OceanBase Database to an Oracle database.
• DBLink from an Oracle-compatible mode of OceanBase Database to another Oracle-compatible mode of OceanBase Database.

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

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

In the syntax above, 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 is a remote stored procedure; and pakage_name. specifies the name of the PL package.

The following example demonstrates how to call the remote proc1 (a standalone procedure) and the proc2 procedure within the pl_pkg1 package from the remote dblink_test DBLink.

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

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

The following example demonstrates a remote call to 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 within a remote DBLink. The syntax is as follows:

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

In the syntax above, 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 defined in packages for both input and output parameters. Complex data types include Record Type, Nested Table, and Associative Array. The elements of a Record Type can only be the basic types mentioned earlier. The elements of a Nested Table and an Associative Array can only be the basic types mentioned earlier and Record Types.

In Oracle-compatible mode of OceanBase Database, DBLinks support the transmission of complex data types. These data types typically include not only basic types (such as integers and strings) but also nested complex structures. The supported nested structures are as follows:

• record(basic): A record type containing fields of basic types.
• array(basic): An array type where the elements are basic types.
• array(record(basic)): An array type where the elements are record types, and the fields of these record types are basic types.

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

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

/* Define the package pkg1 in a 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;
/

/* 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;
/

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

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

OceanBase Database also supports synonyms for remote PL stored procedures and packages, as well as the creation of 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 use the following command to call a remote PL stored procedure.

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