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}
- [[RD-node-name.]authorization-identifier.] routine-identifier
- 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.
- argument ::= {{IN|OUT|INOUT} :embedded-variable[:indicator-variable]
|[{{IN|OUT|INOUT}} SQL-variable|SQL-parameter
|{{IN|OUT|INOUT}] ?-parameter
|[IN] value-expression}
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 specification | Type of CALL statement |
---|
Dynamic execution | UAP embedding | In routine definition |
---|
:embedded-variable[:indicator-variable] | N | Y2 | N |
SQL-variable, SQL-parameter | N | N | Y1 |
Component specification based on SQL-variable, SQL-parameter | N | N | Y1 |
?-parameter | Y1 | N | N |
Value expression other than above | Y3 | Y3 | Y3 |
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.
- Arguments are associated with parameters by the order in which they are specified.
- The data type of an argument must be compatible with the data type of the parameter with which the argument is associated.
- 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.
- ? 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.
- The BOOLEAN data type cannot be specified for an input or output parameter.
- An embedded variable (indicator variable) and the ? parameter must be a simple structure.
- 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
- 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.
- 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.
- A subquery cannot be specified in a value expression specified as an argument.
- 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 type | Returned result sets | Order in which result sets are returned |
---|
Java procedure | The 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 procedure | Of 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 |
- 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.