CALL statement (Call procedure)

Function

The CALL statement calls a procedure.

Privileges

Users with the DBA or CONNECT privilege

These users can call procedures. If SQL statements are to be executed in a procedure, the user needs to have the privilege to execute all the SQL.

Format

CALL [[RD-node-name.]authorization-identifier.]routine-identifier ([argument
         [, argument]...])
argument ::= {{IN|OUT|INOUT} :embedded-variable[:indicator-variable]
             |[{IN|OUT|INOUT}] {SQL-variable|SQL-parameter
                                   |?-parameter}
             |[IN] value-expression}

Operands

RD-node-name
Specifies the RD node name of the RD node to which remote database access is to be made. An RD node name cannot be specified in a routine definition.
authorization-identifier
Specifies the authorization identifier of the owner of the procedure being called.
routine-identifier
Specifies the routine name of the procedure being called.

Specifies arguments for a parameter of the procedure to be called. IN, OUT, or INOUT specifies the I/O mode (parameter mode) for the parameter for a procedure specified in a CREATE PROCEDURE statement.

Table 4-3 shows specification rules for IN, OUT, and INOUT.

Table 4-3 Specification rules for IN, OUT, and INOUT

Argument specificationType of CALL statement
Dynamic executionUAP embeddingIn routine definition
:embedded-variable[:indicator-variable]NY2N
SQL-variable, SQL-parameterNNY1
Component specification based on SQL-variable, SQL-parameterNNY1
?-parameterY1NN
Value expression other than aboveY3Y3Y3

Y: Can be specified.

N: Cannot be specified.

1 IN, OUT, or INOUT may be specified; or the specification may be omitted.

2 IN, OUT, or INOUT must be specified.

3 IN can be specified or may be omitted.

Common rules

  1. Arguments are associated with parameters by the order in which they are specified.
  2. The data type of an argument must be compatible with the data type of the parameter with which the argument is associated.
  3. If the parameter mode of the associated parameter is OUT or INOUT and the NULL value is output as a parameter value, the receiving argument must have an indicator variable.
  4. ? parameters that are specified as arguments for parameters with an IN, OUT, or INOUT parameter mode become, respectively, the input ? parameter, the output ? parameter, or the input ? parameter and output ? parameter.
    The ? parameter in the value expression will be the input ? parameter.
  5. The BOOLEAN data type cannot be specified for an input or output parameter.
  6. An embedded variable (indicator variable) and the ? parameter must be a simple structure.
  7. The following cannot be specified as an argument when the parameter mode is IN:
    • A value expression containing a set function
    • A value expression containing the window function
    • A value expression containing a column specification
    • A value expression containing a component specification that exhibits the attribute of an abstract data type column
  8. A component specification that exhibits the attribute of an abstract data type column cannot be specified as an argument when the parameter mode is OUT or INOUT.
  9. In argument, a SUBSTR scalar function with a result data type of BLOB or BINARY with a maximum length of 32,001 bytes or greater cannot be specified as a single value expression.
  10. A subquery cannot be specified in a value expression specified as an argument.
  11. If a procedure in which the number 1 or greater is specified in the DYNAMIC RESULT SETS clause of a procedure definition, the procedure returns one of the groups of result sets shown in the following table. If, however, the number of result sets shown in the following table is greater than the number specified in the DYNAMIC RESULT SETS clause, only result sets up to the number specified in the DYNAMIC RESULT SETS clause are returned.

    Table 4-4 Result sets returned by a procedure and their order

    Procedure typeReturned result setsOrder in which result sets are returned
    Java procedureThe result set specified in a java.sql.ResultSet[] type parameter for the Java method specified in a foreign routine specification in the procedure definition.Order of parameter specifications
    SQL procedureOf the result set cursors declared in the procedure, the result set of cursors that are open when the procedure terminates.Order in which cursors are opened
  12. If the called procedure returns a result set, one of the following return codes is assigned:
    • A return code 120 to the SQLCODE area of the SQL communications area
    • A return code 120 to the SQLCODE variable
    • A return code 0100C to the SQLSTATE variable

However, if the number of result set cursors that are open at the time of procedure termination is greater than the number specified in the DYNAMIC RESULT SETS clause, a code 0100E is assigned to the SQLSTATE variable.

Notes

  1. If index information in an SQL object in the procedure is invalidated by addition or deletion of an index, the procedure cannot be executed. In such a case, the SQL object in the procedure must be recreated.
  2. Procedures using PURGE TABLE, COMMIT, and ROLLBACK statements cannot be used in the following environment:
    • Calling a procedure from a UAP running under OLTP
    • Calling a procedure defined on a distributed RD node in remote database access using the distributed database facility