Exception Handling in PL/SQL using RAISE_APPLICATION_ERROR

In PL/SQL, exception handling allows you to handle runtime errors, ensuring your program doesn't fail abruptly. One way to handle user-defined errors is through the RAISE_APPLICATION_ERROR - procedure. This procedure allows you to raise a custom error with a specified error number and message.

Syntax

The syntax for using the RAISE_APPLICATION_ERROR procedure in PL/SQL is as follows:

RAISE_APPLICATION_ERROR(error_number, message [, {TRUE | FALSE}]);

Parameters:

error_number: A negative integer between -20000 and -20999 that you assign to identify the specific error.

message: A string (up to 2048 bytes) that describes the error message you want to display.

Example:

RAISE_APPLICATION_ERROR(-20001, 'Invalid input value', FALSE);

This will raise the error with error number -20001 and display the message 'Invalid input value'.

RAISE_APPLICATION_ERROR: Used to generate user-defined exceptions.
Error Number: A negative integer between -20000 and -20999.
Error Message: Custom message describing the error.

Detailed Example

Let's create a PL/SQL block that checks for an invalid salary. If the salary is negative, we will raise a custom error using RAISE_APPLICATION_ERROR.

DECLARE
    v_employee_id NUMBER := 101;
    v_salary NUMBER := -4500;  -- Invalid salary
BEGIN
    -- Check if salary is negative
    IF v_salary < 0 THEN
        -- Raise a custom application error
        RAISE_APPLICATION_ERROR(-20001, 'Error: Invalid salary (' || v_salary || ') 
for Employee ID: ' || v_employee_id);
    END IF;

    -- Other logic can go here
    DBMS_OUTPUT.PUT_LINE('Salary is valid: ' || v_salary);

EXCEPTION
    -- Handle any exceptions that occur
    WHEN OTHERS THEN
        -- Print the custom error message using SQLERRM
        DBMS_OUTPUT.PUT_LINE('Exception Occurred: ' || SQLERRM);
END;
/

Explanation

1. v_employee_id: A variable representing the employee's ID.

2. v_salary: A variable representing the salary, set to -4500, which is invalid for this case.

3. IF condition: Checks if the salary is negative. If it is, the RAISE_APPLICATION_ERROR procedure is called to raise a custom exception.

4. RAISE_APPLICATION_ERROR: Raises an error with:

Error number -20001 (user-defined).
Error message 'Error: Invalid salary (-4500) for Employee ID: 101'.

5. EXCEPTION block: Catches the raised exception. The WHEN OTHERS clause captures any exception, and the DBMS_OUTPUT.PUT_LINE procedure prints the error message using the SQLERRM function, which returns the error message of the raised exception.

When you run the above PL/SQL block, the following output will be printed:

Exception Occurred: ORA-20001: 
Error: Invalid salary (-4500) for Employee ID: 101
ORA-06512: at line 6

Explanation of the Output

ORA-20001: This is the custom error number that was raised using RAISE_APPLICATION_ERROR.

Error Message: 'Error: Invalid salary (-4500) for Employee ID: 101' is the custom error message specified in the RAISE_APPLICATION_ERROR procedure.

ORA-06512: This is a standard Oracle error that indicates the location (line number) where the error occurred.

Breakdown of the Process

1. The salary is checked using an IF condition.

2. If the salary is invalid (negative in this case), RAISE_APPLICATION_ERROR raises a custom error with a specific error number and message.

3. The EXCEPTION block catches this raised error.

4. The DBMS_OUTPUT.PUT_LINE in the EXCEPTION block prints the error message generated by the SQLERRM function, which retrieves the error message associated with the raised exception.

Conclusion

This example demonstrates how to use RAISE_APPLICATION_ERROR to handle custom exceptions in PL/SQL. It allows you to raise specific errors with meaningful messages, ensuring better control over how exceptions are managed in your program.