PL/SQL - Raise Application Error Example

In PL/SQL, you can use the raise_application_error procedure to throw custom error messages when a specific condition is not met in your code. This is especially helpful when you want to enforce business rules or validation checks within your stored procedures. Below is an example demonstrating how to use raise_application_error when a required value is missing from the database.

Example: Raising an Application Error if a Condition is Not Met

Suppose you want to ensure that every employee in the EMP table has a non-null commission (comm). If the commission is NULL for a given employee number, you want the procedure to raise an error and stop execution.

Definition of the Procedure

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;
/

• The procedure compute_sal takes an employee number as input.
• It uses a cursor to fetch the salary (sal) and commission (comm) for the specified employee.
• If the commission is NULL, the procedure raises an application error with a custom message.
• If the commission is not null, it prints the total salary (salary plus commission).

How to Execute the Procedure

You can call the procedure using an anonymous PL/SQL block as shown below:

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

Sample Output When the Commission is Null

If the employee with number 7369 has a null commission, the output will be:

BEGIN
*
ERROR at line 1:
ORA-20001: Commission is null.
ORA-06512: at "SCOTT.COMPUTE_SAL", line 16
ORA-06512: at line 2

Additional Notes

• The raise_application_error procedure lets you specify a custom error number (between -20000 and -20999) and an error message.
• This feature is useful for enforcing data integrity and providing meaningful feedback to users or calling applications.
• You can also use this technique in more complex validation and business logic scenarios.

By using raise_application_error in your PL/SQL procedures, you can ensure that your application handles unexpected situations gracefully and provides clear, actionable error messages.