CALL

Syntax

>>--EXEC SQL--.-----------------.--.----------------.->
              +-FOR :row_count--+  +- :result_hvar -+

 >---CALL stored_procedure_name-.------------.-END-EXEC-><
                                | +-- , --+  |
                                | V       |  |
                                +(parameter)-+

Parameters

:row_count
An integer host variable that specifies the number of rows to be used if the result and parameter host variables are all arrays of the same size, and not all elements should be used. You cannot use the FOR clause if the CALL is part of a DECLARE CURSOR statement.
:result_hvar
A host variable to receive the procedure result.
stored_procedure_name
The name of the stored procedure.
parameter
A literal, the keyword CURSOR or a host variable parameter of the form:
[keyword=]:param_hvar [IN | INPUT | INOUT | OUT | OUTPUT]
where:
keyword
is the formal parameter name for a keyword parameter.
:param_hvar
is a host variable.
IN
specifies an input parameter.
INPUT
specifies an input parameter.
INOUT
specifies an input/output parameter.
OUT
specifies an output parameter.
OUTPUT
specifies an output parameter.

CURSOR is used only for stored procedures with Oracle 8 or later which return a result set. Its use causes the corresponding parameter to be unbound

Purpose

The CALL statement is used to execute a stored procedure.

Example:

   EXEC SQL
      CALL myProc(param1,param2)
   END-EXEC

   EXEC SQL
      :myResult = CALL myFunction(namedParam=:paramValue)
   END-EXEC

   EXEC SQL
      CALL getDept(:empName IN, :deptName OUT)
   END-EXEC

   EXEC SQL
      DECLARE cities CURSOR FOR CALL locateStores(:userState)
   END-EXEC