PL Database Link

OceanBase Database supports remote execution of PL (procedural language) stored procedures and functions in standalone procedures and packages, as well as functions in packages. OceanBase Database supports the following types of DBLinks for remote calls:

• A DBLink from an Oracle mode of OceanBase Database to an Oracle database.
• A DBLink from an Oracle mode of OceanBase Database to another Oracle mode 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 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 package_name. specifies the name of the PL package.

The following example shows how to call the proc1 (standalone procedure) and proc2 (function in the pl_pkg1 package) of 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 as both input and output parameters. Supported basic 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 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 data type is named type_name.

Remote calls to PL stored procedures support complex data types as 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 basic types. The elements of a Nested Table or Associative Array can only be basic types or Record Types.

DBLinks in Oracle mode of OceanBase Database support 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 following nested types are supported:

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

Note that for a remote Oracle database 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 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 scenario is not supported because ty6 is not used as a parameter in the proc4 procedure of the package pkg1. */

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

OceanBase Database also allows you to call synonyms of remote PL stored procedures and packages, and create synonyms of 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 remotely call a 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 a remote database by using a database link