Valid in: DBProc

The RAISE ERROR statement notifies the DBMS Server and the application that a database procedure has encountered an error. The RAISE ERROR statement can only be issued inside a database procedure. This statement is particularly useful when using a rule and its associated database procedure to apply an integrity constraint.

When this statement is issued, the DBMS Server responds as if the database procedure has encountered an error. If the procedure was invoked by a rule, the DBMS Server rolls back any changes made to the database by the original user statement and any made by the database procedure and issues an error to the application. If the RAISE ERROR statement is issued in a procedure that is executed directly by the EXECUTE PROCEDURE statement, the error is processed in the same manner as are other errors in a database procedure. (For details, see Database Procedures.)

When executing a RAISE ERROR statement with associated errortext, both the errornumber and errortext are returned to the application. However, only the errortext is displayed. In embedded SQL and 4GL applications, this can be changed by using INQUIRE_SQL to retrieve the error number and text (DBMSERROR and ERRORTEXT). Additionally, in embedded SQL, use the WHENEVER statement with the SQLERROR condition to inhibit the automatic display of the errortext and provide an alternate error handling mechanism.

The errornumber is considered a local DBMS Server error and, by default, is returned to SQLCA variable SQLERRD(1) and to DBMSERROR, which is accessible using INQUIRE_SQL. The generic error number corresponding to a raise error is 41300. This number is returned, by default, to ERRORNO, which is accessible using INQUIRE_SQL, and to SQLCODE, another SQLCA variable. The number in SQLCODE is negative (-41300).

If you have specified that local errors are returned to ERRORNO and SQLCODE (by issuing the SQT_SQL(DBMSERROR) statement), the locations described above for the errornumber and its generic error number are reversed also. In such cases, it is not necessary to provide a negative number for the errornumber; the DBMS Server automatically negates the number when it places the number in SQLCODE. For a complete discussion of local and generic error numbers, see the chapter "Working with Transactions and Handling Errors.”

In interactive applications that rely on default error messages, such as QBF, the errornumber must be included as part of the errortext to display the number. For example, assume that you are working in QBF and a rule fires and, as a result, the following statement executes:

When the statement is executed, QBF displays a pop-up window with the message:

If it is important to display the error number also, it must be included as part of the errortext in addition to specifying it as the errornumber:

To direct the output of the RAISE ERROR statement to the error log, specify WITH DESTINATION=(ERROR_LOG). The error number and text are written to the errlog.log file with message identifier E_QE0300. To direct output to the session (the default behavior), specify WITH DESTINATION=(SESSION). To both log an error and return it to an application, specify WITH DESTINATION=(SESSION, ERROR_LOG).

To direct the output of the RAISE ERROR statement directly to the audit log, specify WITH DESTINATION=(AUDIT_LOG). Any such messages are regarded as security audit events. The description indicates the source of the event (for example: MESSAGE, RAISE ERROR). The message text and error number are available in the detail information for the event.

The Raise Error statement has the following format:

You must have CREATE_PROCEDURE privilege.