The RAISE_ERROR Function

The RAISE_ERROR Function

The RAISE_ERROR function is a different type of function than we have discussed so far. It is not a column function because it does not take a group of rows and return a single value. Nor is RAISE_ERROR truly a scalar function because it does not transform column data from one state to another.

Instead, the RAISE_ERROR function is used to raise an error condition in the SQLCA. The user supplies the SQLSTATE and error description for the error to be raised. The error will be raised with the specified SQLSTATE and a SQLCODE of “438 .

The RAISE_ERROR function can be used to signal application program and data problems. One situation where RAISE_ERROR may prove useful is in a CASE statement such as

 SELECT EMPNO,   CASE WHEN SEX = 'M' THEN 'MALE  '        WHEN SEX = 'F' THEN 'FEMALE'  ELSE  RAISE_ERROR('70SX1', 'INVALID DATA, SEX IS NEITHER F NOR M.')    END    FROM DSN8810.EMP; 

The value specified for SQLSTATE must conform to the following rules:

• The value must be a character string of exactly five characters in length.

• Only the characters '0' through '9' and uppercase 'A' through 'Z' may be used.

• The first two characters cannot be '00' , '01' , or '02' .

• If the first character is '0' through '6' or 'A' through 'H' , the last three characters must start with a letter from 'I' through 'Z' .

• If the first character is '7' , '8' , '9' , or 'I' though 'Z' , the last three characters can be any valid character.

NOTE

Technically, the RAISE_ERROR function does return a value. It always returns NULL with an undefined data type. You must use the CAST function to cast it to a defined data type to return the value to a program.