PL/SQL | Order of Exception Handling

In PL/SQL, the order of exception handling clauses is crucial because the PL/SQL engine processes them sequentially. When you write the WHEN OTHERS clause before any user-defined exceptions, it captures all unhandled exceptions, including those that could have been caught by specific handlers. This means that if WHEN OTHERS is placed first, your user-defined exceptions will never be reached, effectively changing the flow of exception handling.

Example Scenario

Here’s a demonstration to illustrate what happens when WHEN OTHERS is placed before a user-defined exception handler:

Example Code

DECLARE
-- Declare a user-defined exception
insufficient_funds EXCEPTION;

BEGIN
-- Some logic that could raise an exception
DECLARE
v_salary NUMBER := 500; -- Example salary
BEGIN
IF v_salary < 1000 THEN
RAISE insufficient_funds; -- Raising user-defined exception
END IF;
END;

EXCEPTION
-- Handling predefined and general exceptions first
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Caught in WHEN OTHERS: ' || SQLERRM);

-- Handling user-defined exception after WHEN OTHERS
WHEN insufficient_funds THEN
    DBMS_OUTPUT.PUT_LINE('Caught insufficient_funds exception.');

END;

Execution Flow

1. Execution of the BEGIN Block:

The PL/SQL block starts executing the code in the BEGIN section.

When the condition IF v_salary < 1000 is met, the user-defined exception insufficient_funds is raised.

1. Exception Handling:

When the exception is raised, control passes to the EXCEPTION section.

The first exception handler encountered is WHEN OTHERS. Since it captures all exceptions, it will execute this block regardless of whether the exception is user-defined or predefined.

The output will be:

Caught in WHEN OTHERS: ORA-20001: user-defined error

1. User-Defined Exception Not Caught:

The specific handler for insufficient_funds will never be executed because the flow of control has already entered the WHEN OTHERS block, which captures everything.

Key Takeaways

Order Matters: In PL/SQL, exception handlers are processed in the order they are defined. Once an exception is caught by a handler, subsequent handlers are not evaluated.

Placement of WHEN OTHERS:

If placed first: It will catch all exceptions, including user-defined ones, preventing their specific handling.

If placed last: It acts as a catch-all for any unhandled exceptions, allowing specific exceptions to be processed first.

Correct Way to Structure Exception Handling

To ensure that your user-defined exceptions are handled correctly, you should structure your exception handlers like this:

DECLARE
insufficient_funds EXCEPTION;

BEGIN
-- Logic that may raise an exception
DECLARE
v_salary NUMBER := 500;
BEGIN
IF v_salary < 1000 THEN
RAISE insufficient_funds;
END IF;
END;

EXCEPTION
-- Handle user-defined exception first
WHEN insufficient_funds THEN
DBMS_OUTPUT.PUT_LINE('Caught insufficient_funds exception.');

-- Then handle any other exceptions
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Caught in WHEN OTHERS: ' || SQLERRM);

END;

Conclusion

In summary, always place WHEN OTHERS at the end of your exception handling block. This way, you can ensure that specific user-defined exceptions are caught and handled properly, maintaining a logical and predictable flow in your PL/SQL code.