Exception Handling

PL/SQL's exception-handling model lets you catch runtime errors gracefully, log them, recover where possible, and propagate them to callers when recovery is not possible. An unhandled exception rolls back the current statement (not the whole transaction) and propagates up the call stack.

Predefined Exceptions

Oracle defines named exceptions for the most common errors. You can catch them by name in the EXCEPTION block:

DECLARE
    v_salary  employees.salary%TYPE;
    v_result  NUMBER;
BEGIN
    -- NO_DATA_FOUND
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 9999;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found — defaulting to 0');
        v_salary := 0;
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected: multiple rows returned');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
        RAISE;  -- re-raise to propagate to caller
END;
/

WHEN OTHERS

WHEN OTHERS catches any exception not matched by a preceding WHEN. It should always RAISE or RAISE_APPLICATION_ERROR after logging — silently swallowing exceptions hides bugs:

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Handle specifically
        RETURN 0;
    WHEN OTHERS THEN
        -- Log the error
        DBMS_OUTPUT.PUT_LINE(
            'Error ' || SQLCODE || ': ' || SQLERRM
        );
        -- Then propagate
        RAISE;
END;

User-Defined Exceptions

Declare and raise your own exceptions for business rule violations:

DECLARE
    e_salary_too_high  EXCEPTION;
    e_invalid_dept     EXCEPTION;
    v_salary           NUMBER := 300000;
    v_dept_id          NUMBER := 999;
BEGIN
    IF v_salary > 250000 THEN
        RAISE e_salary_too_high;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM departments WHERE department_id = v_dept_id) THEN
        RAISE e_invalid_dept;
    END IF;

EXCEPTION
    WHEN e_salary_too_high THEN
        DBMS_OUTPUT.PUT_LINE('Salary $' || v_salary || ' exceeds company cap.');
    WHEN e_invalid_dept THEN
        DBMS_OUTPUT.PUT_LINE('Department ' || v_dept_id || ' does not exist.');
END;
/

PRAGMA EXCEPTION_INIT

Associates a user-defined exception name with an Oracle error code, giving a named handle to system errors not covered by predefined exceptions:

DECLARE
    e_deadlock       EXCEPTION;
    e_null_not_allowed EXCEPTION;

    PRAGMA EXCEPTION_INIT(e_deadlock,         -60);    -- ORA-00060
    PRAGMA EXCEPTION_INIT(e_null_not_allowed, -1400);  -- ORA-01400

BEGIN
    INSERT INTO employees (employee_id) VALUES (NULL);  -- violates NOT NULL

EXCEPTION
    WHEN e_null_not_allowed THEN
        DBMS_OUTPUT.PUT_LINE('Cannot insert NULL for employee_id.');
    WHEN e_deadlock THEN
        DBMS_OUTPUT.PUT_LINE('Deadlock detected — retrying...');
END;
/

RAISE_APPLICATION_ERROR

Raises a user-defined error with a custom ORA error number (-20000 to -20999) and message. The error appears exactly like an Oracle system error to callers:

CREATE OR REPLACE PROCEDURE update_salary(
    p_emp_id  IN employees.employee_id%TYPE,
    p_new_sal IN employees.salary%TYPE
) IS
    v_max_salary CONSTANT NUMBER := 250000;
    v_min_salary CONSTANT NUMBER := 2000;
BEGIN
    IF p_new_sal > v_max_salary THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Salary $' || p_new_sal || ' exceeds maximum of $' || v_max_salary
        );
    END IF;

    IF p_new_sal < v_min_salary THEN
        RAISE_APPLICATION_ERROR(
            -20002,
            'Salary $' || p_new_sal || ' is below minimum of $' || v_min_salary
        );
    END IF;

    UPDATE employees SET salary = p_new_sal WHERE employee_id = p_emp_id;

    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20003, 'Employee ' || p_emp_id || ' not found');
    END IF;

    COMMIT;
END update_salary;
/

The second parameter to RAISE_APPLICATION_ERROR can be TRUE to add the error to the existing error stack, or FALSE (default) to replace the stack.

SQLCODE and SQLERRM

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Code: '    || SQLCODE);  -- e.g., -1403
        DBMS_OUTPUT.PUT_LINE('Message: ' || SQLERRM);  -- e.g., ORA-01403: no data found
        DBMS_OUTPUT.PUT_LINE('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        RAISE;
END;

Exception Propagation

CREATE OR REPLACE PROCEDURE inner_proc IS
BEGIN
    -- This raises NO_DATA_FOUND
    DECLARE v_x NUMBER;
    BEGIN
        SELECT salary INTO v_x FROM employees WHERE employee_id = 9999;
    END;
    -- Not caught here — propagates to inner_proc's EXCEPTION block
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Handle it here OR re-raise with RAISE
        RAISE_APPLICATION_ERROR(-20099, 'Employee lookup failed in inner_proc');
END inner_proc;
/

CREATE OR REPLACE PROCEDURE outer_proc IS
BEGIN
    inner_proc;  -- if inner_proc raises, exception propagates here
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(
            'Caught in outer_proc: ' || SQLERRM || CHR(10) ||
            DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
        );
END outer_proc;
/

Re-raising Exceptions

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Log, then re-raise the original exception unchanged
        log_error('get_employee', SQLCODE, SQLERRM);
        RAISE;  -- re-raise preserves original error code and message

    WHEN OTHERS THEN
        log_error('get_employee', SQLCODE, SQLERRM);
        RAISE_APPLICATION_ERROR(
            -20100,
            'Unexpected error in get_employee: ' || SQLERRM,
            TRUE   -- TRUE = keep original error stack
        );
END;

Summary

• The EXCEPTION block at the end of a BEGIN ... END catches runtime errors.
• Predefined named exceptions (NO_DATA_FOUND, TOO_MANY_ROWS, etc.) match common Oracle errors.
• WHEN OTHERS catches everything else — always log and RAISE or RAISE_APPLICATION_ERROR.
• Declare custom exceptions and RAISE them for business rule violations.
• PRAGMA EXCEPTION_INIT assigns a name to any Oracle error code.
• RAISE_APPLICATION_ERROR(-20000..-20999, msg) creates structured user-defined errors visible to callers.
• SQLCODE, SQLERRM, and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE give full error context.
• RAISE re-raises the current exception unchanged; RAISE_APPLICATION_ERROR replaces it.