The RAISE_APPLICATION_ERROR procedure allows a developer to intentionally abort processing within an SPL program from which the procedure is called by causing an exception. The exception is handled in the same manner as described in the topic of Exception handling. In addition, the RAISE_APPLICATION_ERROR procedure makes a user-defined code and error message available to the program which can then be used to identify the exception.
- error_number is an integer value or expression that is returned in a variable named SQLCODE when the procedure is executed. error_number must be a value between -20000 and -20999.
- message is a string literal or expression that is returned in a variable named SQLERRM.
The following example uses the RAISE_APPLICATION_ERROR procedure to display a different code and message depending upon the information missing from an employee record:
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 output is generated in a case where the manager number is missing from an employee record:
EXEC verify_emp(7839); SQLCODE: -20030 SQLERRM: polar-20030: No manager for 7839