Was this helpful?
RAISE ERROR
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:
RAISE ERROR 123445 'Invalid value inserted';
When the statement is executed, QBF displays a pop-up window with the message:
'Invalid value inserted'
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:
RAISE ERROR 123445
'Error 123445: Invalid value inserted';
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.
Syntax
The Raise Error statement has the following format:
RAISE ERROR errornumber [errortext]
              [WITH DESTINATION = ([SESSION] [, ERROR_LOG] [, AUDIT_LOG])];
errornumber
Can be an integer constant, a local variable, or a parameter in the invoked database procedure. If it is a local variable, it must be either a non-nullable integer or smallint type.
errortext
Is an optional text string that describes the error associated with errornumber. It can be a string constant, a local string variable, or a parameter in the invoked database procedure. If errortext is not specified, interactive applications such as QBF display a default error message.
Limits: The maximum length is 1500 bytes.
Permissions
You must have CREATE_PROCEDURE privilege.
Related Statements
Execute Procedure
Inquire_sql
Message
Last modified date: 12/06/2024