DB2 10.5 for Linux, UNIX, and Windows

Raise application error (PL/SQL)

The RAISE_APPLICATION_ERROR procedure raises an exception based on a user-provided error code and message. This procedure is only supported in PL/SQL contexts.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-RAISE_APPLICATION_ERROR--(--error-number--,--message--,--+---------------------------+--)--;-><
                                                            |                 .-false-. |         
                                                            '-keeperrorstack--+-------+-'         

Description

error-number
A vendor-specific number that is mapped to an error code before it is stored in a variable named SQLCODE. The RAISE_APPLICATION_ERROR procedure accepts user-defined error-number values from -20000 to -20999. The SQLCODE that is returned in the error message is SQL0438N. The SQLSTATE contains class 'UD' plus three characters that correspond to the last three digits of the error-number value.
message
A user-defined message with a maximum length of 70 bytes.
keeperrorstack
An optional boolean value indicating whether the error stack should be preserved. Currently, only the default value of false is supported.

Example

The following example uses the RAISE_APPLICATION_ERROR procedure to display error codes and messages that are specific to missing employee information:
CREATE OR REPLACE PROCEDURE verify_emp (
    p_empno         NUMBER
)
IS
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE;
    v_mgr           emp.mgr%TYPE;
    v_hiredate      emp.hiredate%TYPE;
BEGIN
    SELECT ename, job, mgr, hiredate
        INTO v_ename, v_job, v_mgr, v_hiredate FROM emp
        WHERE empno = p_empno;
    IF v_ename IS NULL THEN
        RAISE_APPLICATION_ERROR(-20010, 'No name for ' || p_empno);
    END IF;
    IF v_job IS NULL THEN
        RAISE_APPLICATION_ERROR(-20020, 'No job for' || p_empno);
    END IF;
    IF v_mgr IS NULL THEN
        RAISE_APPLICATION_ERROR(-20030, 'No manager for ' || p_empno);
    END IF;
    IF v_hiredate IS NULL THEN
        RAISE_APPLICATION_ERROR(-20040, 'No hire date for ' || p_empno);
    END IF;
    DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno ||
        ' validated without errors');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;

CALL verify_emp(7839);

SQLCODE: -438
SQLERRM: SQL0438N  Application raised error or warning with
 diagnostic text: "No manager for 7839".  SQLSTATE=UD030