Exception handling in PLSQL with EXCEPTION keyword

In PL/SQL, which is Oracle's procedural extension to SQL, exception handling is an essential feature that allows you to manage errors and exceptions that occur during the execution of a program. The EXCEPTION keyword is used in PL/SQL to define the exception handling section of a block of code. Below is a detailed explanation of how to implement exception handling using the EXCEPTION keyword in PL/SQL, along with an example.

A PL/SQL block typically consists of three sections:

1. Declaration Section: Here, you define variables, cursors, and exceptions.

2. Execution Section: This is where you place the executable statements.

3. Exception Section: This is where you handle exceptions using the EXCEPTION keyword.

Syntax

DECLARE
    -- Declaration section
    v_variable datatype;  -- Variable declarations
    v_exception EXCEPTION; -- User-defined exception
BEGIN
    -- Execution section
    -- Your executable statements go here
EXCEPTION
    WHEN predefined_exception THEN
        -- Actions to take when a predefined exception occurs
    WHEN v_exception THEN
        -- Actions to take when a user-defined exception occurs
    WHEN OTHERS THEN
        -- Actions to take for any other exceptions
END;

Key Points

Predefined Exceptions: PL/SQL provides several predefined exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, etc. You can handle these exceptions directly in the exception section.

User-defined Exceptions: You can also define your own exceptions using the EXCEPTION keyword, as shown above.

OTHERS: The WHEN OTHERS clause is a catch-all for any exceptions not explicitly handled.

Here’s a simple example demonstrating exception handling in PL/SQL:

DECLARE
    v_salary NUMBER := 5000;
    v_bonus NUMBER;
    v_total_salary NUMBER;

    -- User-defined exception
    v_salary_exception EXCEPTION;

BEGIN
    -- Calculation that might raise an exception
    IF v_salary < 0 THEN
        RAISE v_salary_exception; -- Raise user-defined exception
    END IF;

    -- Some calculations
    v_bonus := v_salary * 0.1;  -- 10% bonus
    v_total_salary := v_salary + v_bonus;

    DBMS_OUTPUT.PUT_LINE('Total Salary: ' || v_total_salary);

EXCEPTION
    WHEN v_salary_exception THEN
        DBMS_OUTPUT.PUT_LINE('Error: Salary cannot be negative.');
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Error: Division by zero encountered.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;

Explanation

1. Declaration Section:

• Declares variables for salary, bonus, and total salary.
• Defines a user-defined exception v_salary_exception.

2. Execution Section:

• Checks if v_salary is negative; if so, it raises the v_salary_exception.
• Calculates the bonus and total salary if no exception occurs.

3. Exception Section:

• Handles the v_salary_exception by printing a custom error message.
• Catches the ZERO_DIVIDE predefined exception.
• Catches any other unexpected exceptions with WHEN OTHERS and uses SQLERRM to retrieve the error message.

Conclusion

Using the EXCEPTION keyword in PL/SQL allows for robust error handling, making your code more resilient to runtime errors. You can manage both predefined and user-defined exceptions effectively, ensuring that your applications handle errors gracefully.