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.
>>-RAISE_APPLICATION_ERROR--(--error-number--,--message--,--+---------------------------+--)--;->< | .-false-. | '-keeperrorstack--+-------+-'
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