PL/SQL Exception Handling Anti-Patterns and How to Fix Them

Exception Handling Gone Wrong

PL/SQL’s exception handling mechanism is straightforward, but several common anti-patterns lead to bugs that are difficult to diagnose, data corruption, and applications that silently swallow errors.

Anti-Pattern 1: The Silent Swallower

-- BAD: Exception is caught and completely ignored
BEGIN
    DELETE FROM orders WHERE order_id = p_order_id;
EXCEPTION
    WHEN OTHERS THEN
        NULL;  -- Silently swallows ALL errors
END;

-- FIXED: Log and re-raise, or handle specific exceptions
BEGIN
    DELETE FROM orders WHERE order_id = p_order_id;
EXCEPTION
    WHEN OTHERS THEN
        log_error(SQLCODE, SQLERRM, 'delete_order: ' || p_order_id);
        RAISE;
END;

Anti-Pattern 2: WHEN OTHERS Without RAISE

-- BAD: Catches everything but does not re-raise
EXCEPTION
    WHEN OTHERS THEN
        :P10_ERROR_MSG := 'An error occurred.';

-- FIXED: Re-raise after handling
EXCEPTION
    WHEN OTHERS THEN
        :P10_ERROR_MSG := SQLERRM;
        RAISE;

Without RAISE, the calling code thinks the operation succeeded. This leads to data inconsistencies where the caller proceeds to the next step based on an operation that actually failed.

Anti-Pattern 3: Too-Broad Exception Handlers

-- BAD: One handler hides the real error
BEGIN
    SELECT salary INTO l_salary FROM employees WHERE employee_id = p_id;
    l_bonus := calculate_bonus(l_salary);
    UPDATE employees SET bonus = l_bonus WHERE employee_id = p_id;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Something went wrong');

-- FIXED: Handle specific exceptions at the right level
BEGIN
    BEGIN
        SELECT salary INTO l_salary FROM employees WHERE employee_id = p_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20001, 'Employee ' || p_id || ' not found');
    END;

    l_bonus := calculate_bonus(l_salary);

    UPDATE employees SET bonus = l_bonus WHERE employee_id = p_id;
END;

Anti-Pattern 4: Using Exceptions for Flow Control

-- BAD: Using NO_DATA_FOUND as an IF statement
BEGIN
    SELECT 1 INTO l_dummy FROM employees WHERE email = p_email;
    -- Email exists, raise error
    RAISE_APPLICATION_ERROR(-20001, 'Email already in use');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        NULL;  -- Email is available, continue
END;

-- BETTER: Use a count or EXISTS check
SELECT COUNT(*) INTO l_count FROM employees WHERE email = p_email;
IF l_count > 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Email already in use');
END IF;

Best Practice Summary

Catch only the exceptions you expect and know how to handle. Always re-raise unexpected exceptions after logging. Use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in WHEN OTHERS handlers to capture the original error location. Never use WHEN OTHERS THEN NULL. Keep exception handlers close to the code that might raise the exception, not at the outermost block level where context is lost.