RAISE_APPLICATION_ERROR v14

Suggest edits

The procedure RAISE_APPLICATION_ERROR allows you to abort processing in an SPL program by causing an exception. The exception is handled in the same manner as described in Exception handling. In addition, the RAISE_APPLICATION_ERROR procedure makes a user-defined code and error message available to the program, which you can then use to identify the exception.

The following is the syntax:

RAISE_APPLICATION_ERROR(<error_number>, <message>);

Where:

error_number is an integer value or expression returned in a variable named SQLCODE when the procedure is executed. The value is between ‑20000 and -20999.

message is a string literal or expression returned in a variable named SQLERRM.

For more information on the SQLCODE and SQLERRM variables, see Errors and messages.

This example uses the RAISE_APPLICATION_ERROR procedure to display a different code and message depending on the information missing from an employee:

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;

The following shows the output in a case where the manager number is missing from an employee record:

EXEC verify_emp(7839);

SQLCODE: -20030
SQLERRM: EDB-20030: No manager for 7839
← Prev

PRAGMA EXCEPTION_INIT

↑ Up

Control structures

Next →

Transaction control