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:
- A DBLink from the Oracle mode of OceanBase Database to an Oracle database.
- A DBLink from the Oracle mode of OceanBase Database to the 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 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 is a remote stored procedure. package_name. specifies the name of the PL package.
The following example shows how to call a standalone procedure proc1 and a procedure proc2 in a PL package pl_pkg1 through the DBLink dblink_test.
CALL proc1@dblink_test(1);
CALL pl_pkg1.proc2@dblink_test(1);
Remote calls of PL stored procedures support basic types as input and output parameters. Basic types include string types (VARCHAR2, VARCHAR, CHAR, etc.), numeric types (NUMBER, INT, INTEGER, FLOAT, DOUBLE, etc.), DATE types, and TIMESTAMP types.
The following example shows how to remotely call a PL stored procedure with a VARCHAR2 parameter.
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 through 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 of PL stored procedures support complex data types as input and output parameters. Complex data types include record types, nested tables, and associative arrays. The elements of a record type can only be basic types. The elements of a nested table or an associative array can only be basic types or record types.
The DBLink in the Oracle mode of OceanBase Database supports the transmission of complex data types. This type usually includes 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 a package must be used as parameters in the FUNCTION or PROCEDURE of the package. If a data type is defined in a package but not used as a parameter in any FUNCTION or PROCEDURE of the package, remote calls are not supported. For a remote Oracle database of a later version, 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 the data type ty6 is not used as a parameter in the procedure proc4 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.
Note
OceanBase Database allows you to access tables or views in a DBLink through a local synonym.
/* 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 run the following commands to remotely call a PL stored procedure.
CALL proc5@dblink_test(1);
CALL syn_remote_p1@dblink_test(1);
CALL syn_local_p1(1);