RAISE_ERROR

The RAISE_ERROR function causes the statement that invokes the function to return an error with the specified SQLSTATE (along with SQLCODE -438) and diagnostic string.

sqlstate

An expression that returns a value of a built-in CHAR or VARCHAR data type with exactly 5 characters. The sqlstate value must follow the rules for application-defined SQLSTATEs:

Each character must be from the set of digits ('0' through '9') or nonaccented uppercase letters ('A' through 'Z').
The SQLSTATE class (first two characters) cannot be '00', '01', or '02' because these are not error classes.

If the SQLSTATE does not conform to these rules, an error is returned.

diagnostic-string

An expression that returns a value of a built-in CHAR or VARCHAR data type and a length up to 1000 bytes that describes the error condition. If the string is longer than 1000 bytes, it is truncated.

If an SQLCA is used, the following actions occur:

The string is returned in the SQLERRMC field of the SQLCA.
If the actual length of the string is longer than 70 bytes, it is truncated without a warning.

Since the data type of the result of RAISE_ERROR is undefined, it may only be used where parameter markers are allowed. To use this function in a context where parameter markers are not allowed (such as alone in a select list), you must use a cast specification to give a data type to the null value that is returned.

The RAISE_ERROR function always returns NULL with an undefined data type.

Example

List employee numbers and education levels as Post Graduate, Graduate and Diploma. If an education level is greater than 20, raise an error.

   SELECT EMPNO,
      CASE WHEN EDLEVEL < 16 THEN 'Diploma'
           WHEN EDLEVEL < 18 THEN 'Graduate'
           WHEN EDLEVEL < 21 THEN 'Post Graduate'
           ELSE RAISE_ERROR( '07001',
                    'EDLEVEL has a value greater than 20' )
      END
    FROM EMPLOYEE