The RAISE_APPLICATION_ERROR stored procedure allows a developer to intentionally abort processing in an SPL program from which the procedure is called by causing an exception.
Exceptions are handled in the same way as described in Exception handling. In addition, the RAISE_APPLICATION_ERROR stored procedure provides a user-defined code and an error message for the program. This way, exceptions can be identified.
RAISE_APPLICATION_ERROR(error_number, message);
where:
- error_number is an integer value or an expression that is returned in a variable named SQLCODE when the stored procedure is executed. error_number must be a value between -20000 and -20999.
- message is a string literal or an expression that is returned in a variable named SQLERRM.
The RAISE_APPLICATION_ERROR stored procedure is used in the following example to display a different code and message based on the 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;
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