Purpose
This statement is used to invoke subprograms (procedures or functions) within a stored procedure or package. The CALL statement serves as the entry point for invoking PL subprograms from the SQL layer in OceanBase Database's Oracle-compatible mode.
Privilege requirements
You must have the EXECUTE privilege on a stored procedure to call it. If the procedure belongs to another user, you must also have cross-schema access privileges. For more information about OceanBase Database privileges, see Privilege types in Oracle-compatible mode.
Syntax
CALL routine_access_name ( [parameter_list] )
routine_access_name:
[schema_name.]package_name.routine_name
| [schema_name.]routine_name
Parameters
Parameter |
Description |
|---|---|
| schema_name | Optional. Specifies the name of the schema (user) to which the stored procedure or package belongs. If omitted, the current schema is used. |
| package_name | Optional. Specifies the package name when calling subprograms. |
| routine_name | Required. The name of the stored procedure or subprogram in the package to be called. |
| parameter_list | The parameter list passed during the function call. Parameter passing supports positional notation (passing parameters in order) and named notation (param_name => value). If the subprogram has no parameters, you must still include empty parentheses (). |
Note
The CALL statement must include parentheses (), even if the called subprogram has no parameters. Omitting the parentheses results in a syntax error.
Examples
Call an independent stored procedure
Create a stored procedure named insert_order to insert a record into the order_table:
obclient> CREATE TABLE order_table (
order_id INT PRIMARY KEY,
cust_name VARCHAR2(50),
amount NUMBER(10, 2)
);
obclient> CREATE OR REPLACE PROCEDURE insert_order(
p_id IN INT,
p_name IN VARCHAR2,
p_amt IN NUMBER
) AS
BEGIN
INSERT INTO order_table VALUES (p_id, p_name, p_amt);
END;
/
Call this stored procedure using the CALL statement (positional parameter passing):
obclient> CALL insert_order(1001, 'Alice', 250.00);
The query result is as follows:
obclient> SELECT * FROM order_table;
+----------+-----------+--------+
| ORDER_ID | CUST_NAME | AMOUNT |
+----------+-----------+--------+
| 1001 | Alice | 250.00 |
+----------+-----------+--------+
Call a package subprogram
Create a package named pkg_order that contains a procedure for managing orders:
obclient> CREATE OR REPLACE PACKAGE pkg_order AS
PROCEDURE add_order(p_id IN INT, p_name IN VARCHAR2, p_amt IN NUMBER);
END pkg_order;
/
obclient> CREATE OR REPLACE PACKAGE BODY pkg_order AS
PROCEDURE add_order(p_id IN INT, p_name IN VARCHAR2, p_amt IN NUMBER) AS
BEGIN
INSERT INTO order_table VALUES (p_id, p_name, p_amt);
END add_order;
END pkg_order;
/
You can call a package subprogram in the package_name.routine_name format. For cross-schema calls, you can use the three-level name schema_name.package_name.routine_name:
obclient> CALL pkg_order.add_order(1002, 'Bob', 180.50);
The query result is as follows:
obclient> SELECT * FROM order_table ORDER BY order_id;
+----------+-----------+--------+
| ORDER_ID | CUST_NAME | AMOUNT |
+----------+-----------+--------+
| 1001 | Alice | 250.00 |
| 1002 | Bob | 180.50 |
+----------+-----------+--------+
Pass parameters by using named notation
You can pass parameters by name in the param_name => value format. The order of parameters is not limited:
obclient> CALL insert_order(p_amt => 99.90, p_name => 'Charlie', p_id => 1003);
The query result is as follows:
obclient> SELECT * FROM order_table ORDER BY order_id;
+----------+-----------+--------+
| ORDER_ID | CUST_NAME | AMOUNT |
+----------+-----------+--------+
| 1001 | Alice | 250.00 |
| 1002 | Bob | 180.50 |
| 1003 | Charlie | 99.90 |
+----------+-----------+--------+