For a non-ABL stored procedure, indicates that the procedure has completed execution and retrieves any return status. For a send-sql-statement stored procedure, closes the SQL cursor used by the procedure.
Syntax
CLOSE STORED-PROCEDURE procedure [integer-field = PROC-STATUS ] [ WHERE PROC-HANDLE = integer-field]
procedure
The name of the stored procedure that you want to close or the built-in procedure name, send-sql-statement.
integer-field = PROC-STATUS
Assigns the return value from a stored procedure to the specified integer field or variable (integer-field).
WHERE PROC-HANDLE = integer-field
An integer field or variable whose value uniquely identifies the stored procedure that produces the results returned from the data source or the SQL cursor of a send-sql-statement stored procedure.
Example
The PROC-STATUS clause of the CLOSE STORED-PROCEDURE statement allows the DataServer for ORACLE to retrieve the text of an ORACLE error message that was passed to raise_application_error. Use the ERROR-STATUS:GET-MESSAGE handle to retrieve the message, as shown in the following example:
DEFINE VARIABLE st AS INTEGER NO-UNDO.
DEFINE VARIABLE h AS INTEGER NO-UNDO.
RUN STORED-PROC p1 h = PROC-HANDLE NO-ERROR.
CLOSE STORED-PROC p1 st = PROC-STATUS WHERE PROC-HANDLE = h.
DISPLAY st.
IF ERROR-STATUS:ERROR THEN
MESSAGE ERROR-STATUS:GET-MESSAGE(1) ERROR-STATUS:GET-NUMBER(1)
VIEW-AS ALERT-BOX.
Notes
If you specified a PROC-HANDLE when you ran a stored procedure, you must specify the PROC-HANDLE when you close the stored procedure.
If you do not specify a PROC-HANDLE, the CLOSE STORED-PROCEDURE statement will close the procedure if there is only one stored procedure running. If there is more than one stored procedure running, an error will be returned.
You cannot close a send-sql-statement procedure until you have retrieved all row results.
You can close all stored procedures at once with the following statement:
RUN STORED-PROC closeallprocs.
For more information on using this statement, see OpenEdge Data Management: DataServer for Oracle.