A DBMS vendor-specific stored procedure can:
- Accept input parameters
- Return output parameters
- Accept and return input/output parameters
- Use positional or keyword parameters
- Return one or more forward only read only result sets
- Be called with parameter arrays
Note: The features provided by different database vendors vary considerably, and any given vendor might offer only a subset of the features listed above. For this reason, stored procedure calls are much less portable between data sources than other OpenESQL statements.
Calling a Stored Procedure that Returns No Result Sets
To call a stored procedure that returns no result sets, use the CALL embedded SQL statement. For complete details on using this statement, see the
CALL topic.
Calling a Stored Procedure that Returns One or More Result Sets
If a stored procedure returns one or more result sets, you must first declare it using the DECLARE CURSOR statement. For example:
EXEC SQL
DECLARE cursorName CURSOR FOR storedProcedureCallStatement
END-EXEC
You then call the stored procedure just as you would any other type of cursor using the OPEN statement to open the cursor, followed by the FETCH statement to fetch result set rows.
If your stored procedure is coded to return more than one result set, and your FETCH statement returns an SQLCODE of 100 (signifying the end of the current result set), you can issue the GET NEXT RESULT SET statement to fetch the next result set as follows:
EXEC SQL
GET NEXT RESULT SET FOR cursorName
END-EXEC
If your GET NEXT RESULT SET statement returns an SQLCODE of 0, another result set is available, and you can issue another GET NEXT RESULT SET to retrieve it. However, if GET NEXT RESULT SET returns an SQLCODE of 100, no additional result sets are available.
Using Array Parameters
ODBC parameters differ from Oracle array parameters. The effect of using a parameter array is the same as repeating the statement for each element of the array. On a stored procedure call, if one parameter is passed as an array, then all parameters must be arrays with the same number of elements. The stored procedure "sees" one call for each "row" of parameters. The number of rows passed can be limited to less than the full array size by preceding the call with the following phrase:
FOR :hvar
Where
:hvar is an integer host variable containing a count of the number of rows to be passed.
OpenESQL for JDBC
When working with OpenESQL for JDBC, the following applies:
Restriction: The following scenario can cause the COBOL compiler to fail to update output host variables when the cursor for a stored procedure call is closed. This can happen when stored procedures meet all three of the following criteria:
- Are called using the OPEN statement
- Return a return value, output parameters, or both
- Return a result set
The failure occurs because the COBOL compiler, when possible, compiles 01-level fields and their subordinate 01-level FILLER fields as JVM types.
To ensure successful compilation, use traditional COBOL data types, such as PIC X(n), COMP-3, etc., for host variables rather than object types, such as string, binary-long, BigDecimal, etc., and do one of the following:
- Compile using the NOILNATIVE COBOL compiler directive
- Declare host variables that receive return values or output parameters at level 02 or higher, with a record name other than FILLER
This scenario does not apply to stored procedures called using the CALL statement.
