1 – Compound Statement
Invokes an external or stored procedure from within a compound
statement. That is, invocation must occur with a BEGIN . . . END
block.
The OUT and INOUT arguments cannot be general value expressions.
They must be variables or parameters. The IN argument can be a
general value expression.
When you register a procedure definition with the CREATE
PROCEDURE statement, you store information in the database
about an external procedure written in a 3GL language. External
procedures reside outside the database. The CREATE PROCEDURE
statement is documented under the CREATE Routine. See the CREATE
Routine for more information on creating external procedures.
For optional information on invoking stored procedures, see the
CALL Simple_Statement.
1.1 – Environment
You can use the compound statement CALL:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
1.2 – Format
CALL --> <procedure-name> --> ( -+-------------------+-> ) -->
+-+-> value-expr -+-+
+-> DEFAULT ----+
+------ , <-----+
1.3 – Arguments
1.3.1 – DEFAULT
Requests that Oracle Rdb use the DEFAULT expression defined for
the parameter. The DEFAULT can be defined for an IN parameter
during the CREATE MODULE . . . PROCEDURE or ALTER MODULE . . . ADD
PROCEDURE statements. If no DEFAULT clause exists then the NULL
expression is assumed.
1.3.2 – procedure-name
The name of the external or stored procedure being invoked.
1.3.3 – value-expr
Any value expression except DBKEY or aggregate functions.
See the Value_Expressions HELP topic for details on value
expressions.
1.4 – Examples
Example 1: Calling an external routine within a compound
statement
BEGIN
DECLARE :param1 INTEGER;
CALL extern_routine (:param1, 3);
END;
Example 2: Calling a stored procedure from a stored function
SQL> CREATE MODULE utility_functions
cont> LANGUAGE SQL
cont> --
cont> PROCEDURE trace_date (:dt DATE);
cont> BEGIN
cont> TRACE :dt;
cont> END;
cont> --
cont> FUNCTION mdy (IN :dt DATE) RETURNS CHAR(10)
cont> COMMENT 'Returns the date in month/day/year format';
cont> BEGIN
cont> IF :dt IS NULL THEN
cont> RETURN '**/**/****';
cont> ELSE
cont> CALL trace_date (:dt);
cont> RETURN CAST(EXTRACT(MONTH FROM :dt) AS VARCHAR(2)) || '/' ||
cont> CAST(EXTRACT(DAY FROM :dt) AS VARCHAR(2)) || '/' ||
cont> CAST(EXTRACT(YEAR FROM :dt) AS VARCHAR(4));
cont> END IF;
cont> END;
cont> END MODULE;
2 – Simple Statement
Invokes a stored procedure.
When you define a module with the CREATE MODULE statement, SQL
stores the module as an object in an Oracle Rdb database. It also
stores each of the module's procedures and functions. The module
procedures that reside in an Oracle Rdb database are called
stored procedures. In contrast, nonstored procedures refer to
module procedures that reside outside the database in SQL module
files. See the CREATE MODULE for more information on creating
stored procedures.
For optional information on invoking stored procedures, see the
CALL Compound_Statement.
2.1 – Environment
You can use the simple statement CALL:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement that are dynamically executed
2.2 – Format
CALL --> <stored-procedure-name> --> call-argument-list -->
call-argument-list =
---> ( --+-+------------------------+-+-> ) -->
| +--> <literal> ----------+ |
| +--> <parameter> --------+ |
| +--> <variable> ---------+ |
+----------- , <-------------+
2.3 – Arguments
2.3.1 – call-argument-list
Passes a list of literal, parameter values (parameter markers for
dynamic execution), or variables to the called stored procedure.
You can pass a literal only to an IN parameter of a stored
procedure. You cannot pass a literal to an OUT or INOUT
parameter.
In SQL statements to be dynamically executed, you refer to both
the main and indicator parameters with a single parameter marker
(?). See the Parameters HELP topic for details about how to
use parameters in programs for static as well as dynamic SQL
statement execution.
2.3.2 – procedure-name
The name of a stored procedure.
2.4 – Examples
Example 1: Calling a stored procedure
The following examples show the definition of a stored procedure,
NEW_SALARY_PROC, and the nonstored procedure, CALL_NEW_SALARY,
that invokes it with the simple statement CALL.
SQL> ! The following shows the definition of the stored procedure:
SQL> !
SQL> CREATE MODULE NEW_SALARY_PROC
cont> LANGUAGE SQL
cont> PROCEDURE NEW_SALARY_PROC
cont> (:ID CHAR (5),
cont> :NEW_SALARY INTEGER (2));
cont> BEGIN
cont> UPDATE SALARY_HISTORY
cont> SET SALARY_END = CURRENT_TIMESTAMP
cont> WHERE EMPLOYEE_ID = :ID;
cont> INSERT INTO SALARY_HISTORY
cont> (EMPLOYEE_ID, SALARY_AMOUNT,
cont> SALARY_START, SALARY_END)
cont> VALUES (:ID, :NEW_SALARY,
cont> CURRENT_TIMESTAMP, NULL);
cont> END;
cont> END MODULE;
SQL>
The following example shows an excerpt of an SQL module that
contains the nonstored procedure that calls the stored procedure.
.
.
.
PROCEDURE CALL_NEW_SALARY
:ID CHAR(5),
:ID_IND SMALLINT,
:NEW_SALARY INTEGER (2),
:NEW_SALARY_IND SMALLINT,
SQLCODE;
CALL NEW_SALARY_PROC (:ID, :NEW_SALARY );
.
.
.
Example 2: Calling a procedure in interactive SQL
The following example shows that you use interactive SQL to
invoke a stored procedure with the simple statement CALL:
SQL> DECLARE :X INTEGER;
SQL> BEGIN
cont> SET :X = 0;
cont> END;
SQL> CALL P2 (10, :X);