CALL

Executes a stored procedure.

Syntax:

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

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

Parameters:

host_integer A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
result_hvar A host variable to receive the procedure result.
stored_procedure_name The name of the stored procedure.
parameter A literal, a DECLARE CURSOR statement1, or a host variable parameter of the form:
[keyword=]:param_hvar [IN | INPUT | 
INOUT | OUT | OUTPUT]

where:

keyword The formal parameter name for a keyword parameter. Keyword parameters can be useful as an aid to readability and where the server supports default parameter values and optional parameters.
param_hvar A host variable.
IN An input parameter.
INPUT An input parameter (default).
INOUT An input/output parameter.
OUT An output parameter.
OUTPUT An output parameter.

1 Specify DECLARE CURSOR only with Oracle 8 or later and for stored procedures that return a result set. The use of DECLARE CURSOR unbinds the corresponding parameter.

Comments:

Do not use the FOR clause if the CALL is part of a DECLARE CURSOR statement.

For maximum portability, observe the following as general rules:
  • Avoid literal parameters
  • Use host variable parameters
  • Avoid mixing positional parameters and keyword parameters
  • If your server supports a mixture of positional and keyword parameters, list keyword parameters after positional parameters

Examples:

Call a stored procedure using two positional host variables as input parameters:
   EXEC SQL
      CALL myProc(param1,param2)
   END-EXEC
Call a stored procedure using a keyword host variable as an input parameter:
   EXEC SQL 
      CALL myProc (namedParam=:paramValue) 
   END-EXEC
Call a stored procedure using a result host variable and a keyword host variable as an input parameter:
   EXEC SQL
      :myResult = CALL myFunction(namedParam=:paramValue)
   END-EXEC
Call a stored procedure using two positional host variables, one as an input parameter and one as an output parameter:
   EXEC SQL
      CALL getDept(:empName IN, :deptName OUT)
   END-EXEC
Call a stored procedure using a DECLARE CURSOR statement and a positional host variable as an input parameter (Oracle only):
   EXEC SQL
      DECLARE cities CURSOR FOR CALL locateStores(:userState)
   END-EXEC