PL/SQL - Raise Application Error Example

An example is given here to raise application error (raise_application_error) in PL/SQL stored procedure if the certain condition does not match. The program will raise an exception if the commission (comm) is null in EMP table for given employee number (empno).

Raise_Application_Error in PL/SQL Example

CREATE OR REPLACE PROCEDURE compute_sal (i_EMPNO IN emp.empno%TYPE)
IS
CURSOR c_emp (p_empno emp.empno%TYPE)
IS
SELECT sal, comm
FROM emp
WHERE empno = p_empno;

v_sal NUMBER;
v_comm NUMBER;
BEGIN
OPEN c_emp (i_empno);

FETCH c_emp
INTO v_sal, v_comm;

CLOSE c_emp;

IF v_comm IS NULL
THEN
raise_application_error (-20001, 'Commission is null.');
END IF;

DBMS_OUTPUT.put_line ('Total salary is: ' || (v_sal + v_comm));
END compute_sal;
/

Execute

SET SERVEROUTPUT ON;
BEGIN
COMPUTE_SAL(7369);
END;
/

Output

SQL> SET SERVEROUTPUT ON;
SQL> BEGIN
2 COMPUTE_SAL(7369);
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-20001: Commission is null.
ORA-06512: at "SCOTT.COMPUTE_SAL", line 16
ORA-06512: at line 2

See also:

1. Find error line number in PL/SQL
2. An example of On-Error trigger in Oracle Forms
3. Error Oracle 12c database installation guide