Progress
DataServer
for ORACLE Guide


Returning Errors

There are two types of errors that stored procedures can encounter. The first type of error can be trapped by the stored procedure. The stored procedure can call raise_application_exception with a value ranging from -20000 to -20999 and continue processing. The DataServer checks whether an error is the result of raise_application_error. If this is the case, the DataServer stores the value, which you can retrieve with the PROC-STATUS function when you close the stored procedure. The Progress application continues to execute. The same behavior occurs when you send an SQL statement that causes a trigger to fire that results in a raise_application_error.

The second type of error is caught by the RUN STORED-PROC statement itself and the procedure stops. This error might be due to a PL/SQL compilation error or another error that is not handled.

The RUN STORED-PROC statement supports the NO-ERROR option. The following example shows how to trap errors within a procedure:

DEFINE VAR h1 AS INTEGER.
DEFINE VAR j AS INTEGER.
RUN STORED-PROC send-sql-statement h1 = PROC-HANDLE NO-ERROR
  ("select count (*) from xxx.customer where name between ’A’ and ’Z’ ").

IF ERROR STATUS:ERROR THEN DO:
  DO j = 1 TO ERROR-STATUS:NUM-MESSAGES:
    MESSAGE "error" ERROR-STATUS:GET-NUMBER(j)
        ERROR-STATUS:GET-MESSAGE(j).
  END.
END.

CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = h1. 

The PROC-STATUS clause of the CLOSE STORED-PROCEDURE statement allows the DataServer to retrieve the text of an error message that was passed to raise_application_error. Use the ERROR-STATUS:GET-MESSAGE handle to retrieve the message as in the following example:

DEFINE VARIABLE st AS INTEGER INITIAL 0.
DEFINE VARIABLE h  AS INTEGER. 
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.  

In this example, the PROC-STATUS clause is necessary to enable the ERROR-STATUS:GET-MESSAGE handle to retrieve the text of the error message.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095