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 Use the Oracle Data Server.

See also

PROC-HANDLE function, PROC-STATUS function, RUN STORED-PROCEDURE statement