See information about the latest product version
Invoking stored procedures
To invoke a procedure that is stored in a database, use the ESQL CALL statement. The stored procedure must be defined by a CREATE PROCEDURE statement that has a Language clause of DATABASE and an EXTERNAL NAME clause that identifies the name of the procedure in the database and, optionally, the database schema to which it belongs.
When you invoke a stored procedure with the CALL statement, the broker ensures that the ESQL definition and the database definition match:
- The external name of the procedure must match a procedure in the database.
- The number of parameters must be the same.
- The type of each parameter must be the same.
- The direction of each parameter (IN, OUT, INOUT) must be the same.
The following restrictions apply to the use of stored procedures:
- Overloaded procedures are not supported. (An overloaded procedure is one that has the same name as another procedure in the same database schema with a different number of parameters, or parameters with different types.) If the broker detects that a procedure is overloaded, it raises an exception.
- In an Oracle stored procedure declaration, you are not permitted to constrain CHAR and VARCHAR2 parameters with a length, and NUMBER parameters with a precision or scale, or both. Use %TYPE when you declare CHAR, VARCHAR, and NUMBER parameters to provide constraints on a formal parameter.
- Avoid changing the type or number of parameters of a procedure without also changing the name. If changes of this nature cannot be avoided, any execution groups with procedures with changed parameters called by the ESQL must be restarted.
Creating a stored procedure in ESQL
When you define an ESQL procedure that corresponds to a database stored procedure, you can specify either a qualified name (where the qualifier is a database schema) or an unqualified name.
To create a stored procedure: