OceanBase Database allows you to remotely call a PL stored procedure. Currently, you can call stored procedures in standalone procedures or PL packages. You cannot remotely call functions in UDFs or PL packages.
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 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 specified sp_name object to be called is a remote stored procedure, and pakage_name. specifies the name of the PL package.
The following example remotely calls the standalone procedure proc1 and the proc2 procedure in the PL package pl_pkg1 by using the DBLink named dblink_test.
CALL proc1@dblink_test(1);
CALL pl_pkg1.proc2@dblink_test(1);
A remote call of a PL stored procedure supports output and input parameters of basic types, including string types such as VARCHAR2, VARCHAR, and CHAR, numeric types such as NUMBER, INT, INTEGER, FLOAT, and DOUBLE, and DATE and TIMESTAMP types.
The following example remotely calls a PL stored procedure with an input parameter of the VARCHAR2 type.
DELIMITER /
DECLARE
v1 VARCHAR2(10);
BEGIN
proc3@dblink(1, v1);
END;
/
You can also remotely call data types defined in a PL package in a PL block by using a DBLink. The syntax is as follows:
DECLARE
var_name package_name.type_name[@dblink_name]
BEGIN
...
END;
DECLARE defines a variable specified by var_name. The data type, specified by type_name, of the variable is defined in the package specified by package_name in the remote DBLink library specified by dblink_name.
The remote call of a PL stored procedure supports output and input parameters of complex data types defined in the package, including record types, nested tables, and associate arrays. The elements of a record type can only be the aforementioned basic data types. The elements of a nested table and an associate array can only be the aforementioned basic data types and the record type.
For Oracle Database of version 12c and earlier, data types defined in a package must be the types defined in FUNCTION or PROCEDURE parameters in the package. Remote calls are not supported if the data types are defined only in the package, but are not used in any FUNCTION or PROCEDURE parameters in the package. This limitation does not apply to Oracle Database of later versions.
The following example remotely defines a package named pkg1 and its data types, and remotely calls the data types defined in the package.
/* Remotely define a package named pkg1 */
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;
/
/* Remote calls in the following scenarios are not supported because ty6 does not appear in the parameter list of the internal procedure proc4 of pkg1: */
DECLARE
v1 pkg1.ty6@dblink_test;
BEGIN
NULL;
END;
OceanBase Database also supports synonyms when you remotely call PL stored procedures and packages, and allows you to locally create synonyms for remote PL objects.
/* Create a synonym for the remote PL stored procedure proc5. */
CREATE OR REPLACE SYNONYM syn_remote_p1 FOR proc5;
/* Locally create a synonym for the remote PL stored procedure proc6.
CREATE OR REPLACE SYNONYM syn_local_p1 FOR proc6@oci_link;
The following example calls a remote PL stored procedure.
CALL proc5@dblink_test(1);
CALL syn_remote_p1@dblink_test(1);
CALL syn_local_p1(1);