Oracle PL/SQL – Exceptions (Error Handling)

Subject: Information Technology – Database Systems
Grade: 12th Grade – HTL Computer Science
Prerequisites: PL/SQL Introduction, Control Structures, Cursors
Author: HTL Pinkafeld – IF/IT

1. What are Exceptions?

1.1 Definition

An exception is a runtime error that interrupts normal program flow. PL/SQL provides a structured mechanism to catch such errors and react to them without crashing the program.

BEGIN
    -- Normal program flow
    ...
    -- Error occurs (e.g., ORA-01403)
    ↓
EXCEPTION
    WHEN NO_DATA_FOUND THEN   ← Specific handler
        ...
    WHEN OTHERS THEN          ← Global handler
        ...
END;

1.2 Without Exception Handling

BEGIN
    -- If no employee with empno=9999 → ORA-01403
    SELECT ename INTO v_name FROM emp WHERE empno = 9999;
    DBMS_OUTPUT.PUT_LINE(v_name);
END;
/
-- Result: ORA-01403: no data found → Program aborts

1.3 With Exception Handling

DECLARE
    v_name VARCHAR2(10);
BEGIN
    SELECT ename INTO v_name FROM emp WHERE empno = 9999;
    DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found – no error!');
END;
/
-- Program runs cleanly through

2. Predefined Oracle Exceptions

2.1 Commonly Used Exceptions

2.2 Examples

DECLARE
    v_result NUMBER;
BEGIN
    v_result := 100 / 0;
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Error: Division by zero!');
END;
/

3. The EXCEPTION Section

3.1 Structure

EXCEPTION
    WHEN exception_name1 THEN
        -- Handler for exception_name1
    WHEN exception_name2 OR exception_name3 THEN
        -- Handler for two exceptions at once
    WHEN OTHERS THEN
        -- Everything else

3.2 Multiple Handlers

DECLARE
    v_empno emp.empno%TYPE := 7839;
    v_row   emp%ROWTYPE;
BEGIN
    SELECT * INTO v_row FROM emp WHERE empno = v_empno;

    DBMS_OUTPUT.PUT_LINE('Name:   ' || v_row.ename);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_row.sal);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_empno || ' not found.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple matches – refine WHERE clause!');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unknown error: ' || SQLERRM);
END;
/

3.3 Exception Inside a Loop

After an exception, PL/SQL exits the current block. To continue loops, nest blocks:

BEGIN
    FOR r IN (SELECT empno FROM emp WHERE deptno = 20) LOOP
        BEGIN  -- Inner block catches the error
            IF r.empno = 7788 THEN
                RAISE ZERO_DIVIDE;  -- Simulated error
            END IF;
            DBMS_OUTPUT.PUT_LINE('Processed: ' || r.empno);
        EXCEPTION
            WHEN ZERO_DIVIDE THEN
                DBMS_OUTPUT.PUT_LINE('Error at ' || r.empno || ' – continuing...');
        END;
    END LOOP;
END;
/

4. OTHERS – Global Catch-All

4.1 WHEN OTHERS as Safety Net

DECLARE
    v_val NUMBER;
BEGIN
    v_val := TO_NUMBER('abc');  -- Invalid conversion
EXCEPTION
    WHEN INVALID_NUMBER THEN
        DBMS_OUTPUT.PUT_LINE('Invalid number entered.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error code:    ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('Error message: ' || SQLERRM);
END;
/

4.2 Best Practice: Never Silence OTHERS

-- BAD: Errors are swallowed
EXCEPTION
    WHEN OTHERS THEN NULL;

-- GOOD: Log the error, then re-raise
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        RAISE;  -- Re-raise to calling block

5. SQLCODE and SQLERRM

5.1 SQLCODE

SQLCODE returns the numeric error code:

• 0 → no error
• negative number → Oracle error (e.g., -1403 for NO_DATA_FOUND)

5.2 SQLERRM

SQLERRM returns the associated error message. Can also be called with an error code:

DECLARE
    v_code    NUMBER;
    v_message VARCHAR2(500);
BEGIN
    SELECT sal / 0 INTO v_code FROM dual;
EXCEPTION
    WHEN OTHERS THEN
        v_code    := SQLCODE;
        v_message := SQLERRM;
        DBMS_OUTPUT.PUT_LINE('Code:    ' || v_code);
        DBMS_OUTPUT.PUT_LINE('Message: ' || v_message);
        DBMS_OUTPUT.PUT_LINE(SQLERRM(-1403));  -- → ORA-01403: no data found
END;
/

6. User-Defined Exceptions

6.1 Declaration and Raising

Custom exceptions are declared in the DECLARE section and raised with RAISE:

DECLARE
    e_invalid_salary  EXCEPTION;
    e_no_manager      EXCEPTION;

    v_sal  emp.sal%TYPE := -500;
BEGIN
    IF v_sal < 0 THEN
        RAISE e_invalid_salary;
    END IF;

    DBMS_OUTPUT.PUT_LINE('Salary OK: ' || v_sal);

EXCEPTION
    WHEN e_invalid_salary THEN
        DBMS_OUTPUT.PUT_LINE('Error: Salary must not be negative!');
    WHEN e_no_manager THEN
        DBMS_OUTPUT.PUT_LINE('Error: No manager for this department.');
END;
/

6.2 Practical Example: Business Rule Validation

DECLARE
    e_min_salary   EXCEPTION;
    e_max_hours    EXCEPTION;

    v_salary    NUMBER := 1200;
    v_hours     NUMBER := 55;
    c_min_sal   CONSTANT NUMBER := 1700;
    c_max_h     CONSTANT NUMBER := 50;
BEGIN
    IF v_salary < c_min_sal THEN RAISE e_min_salary; END IF;
    IF v_hours  > c_max_h   THEN RAISE e_max_hours;  END IF;

    DBMS_OUTPUT.PUT_LINE('Data valid.');

EXCEPTION
    WHEN e_min_salary THEN
        DBMS_OUTPUT.PUT_LINE('Salary below minimum wage (' || c_min_sal || ' USD)!');
    WHEN e_max_hours THEN
        DBMS_OUTPUT.PUT_LINE('Exceeding maximum hours (' || c_max_h || 'h)!');
END;
/

7. RAISE_APPLICATION_ERROR

7.1 Custom Error Numbers

RAISE_APPLICATION_ERROR creates custom ORA error messages with user-defined numbers. The error code must be in the range -20000 to -20999:

PROCEDURE validate_salary (p_sal NUMBER) IS
BEGIN
    IF p_sal < 0 THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Salary must not be negative. Input: ' || p_sal
        );
    ELSIF p_sal > 50000 THEN
        RAISE_APPLICATION_ERROR(
            -20002,
            'Salary exceeds maximum of 50,000. Input: ' || p_sal
        );
    END IF;
END;
/

7.2 Calling and Handling

BEGIN
    validate_salary(-100);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error ' || SQLCODE || ': ' || SQLERRM);
        -- Output: Error -20001: ORA-20001: Salary must not be negative. Input: -100
END;
/

7.3 RAISE vs. RAISE_APPLICATION_ERROR

8. PRAGMA EXCEPTION_INIT

8.1 Map ORA Error to an Exception

PRAGMA EXCEPTION_INIT associates an Oracle error code with a named exception:

DECLARE
    e_fk_violated EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_fk_violated, -2292);  -- ORA-02292: integrity constraint violated

    e_deadlock    EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_deadlock, -60);       -- ORA-00060: deadlock detected

BEGIN
    DELETE FROM dept WHERE deptno = 10;  -- Has employees!

EXCEPTION
    WHEN e_fk_violated THEN
        DBMS_OUTPUT.PUT_LINE('Department cannot be deleted – has employees!');
    WHEN e_deadlock THEN
        DBMS_OUTPUT.PUT_LINE('Deadlock detected – transaction rolled back.');
END;
/

9. Exception Propagation and Scope

9.1 Exceptions in Nested Blocks

BEGIN
    BEGIN          -- Inner block
        RAISE NO_DATA_FOUND;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Inner block: caught');
            RAISE;  -- Re-raise to outer block
    END;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Outer block: also caught');
END;
/

9.2 Transaction Safety in Exception Handlers

DECLARE
    v_empno NUMBER := 9001;
BEGIN
    INSERT INTO emp (empno, ename, deptno, sal, hiredate, job)
    VALUES (v_empno, 'TESTUSER', 10, 2500, SYSDATE, 'CLERK');

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Transaction successful.');

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- Roll back all DML in this transaction
        DBMS_OUTPUT.PUT_LINE('Error – rollback performed: ' || SQLERRM);
END;
/

10. Summary and Outlook

10.1 Exception Types Overview

10.2 Good Exception Handling Checklist

• ✅ Handle specific exceptions before WHEN OTHERS
• ✅ Avoid WHEN OTHERS THEN NULL – always log
• ✅ ROLLBACK in error handlers with DML
• ✅ Use RAISE to propagate errors
• ✅ Use RAISE_APPLICATION_ERROR for meaningful API errors
• ✅ In loops: use inner blocks so the loop continues

10.3 Outlook

The next chapter covers stored procedures and functions – reusable PL/SQL programs in the database:

• Difference between procedure and function
• Parameter modes: IN, OUT, IN OUT
• Local vs. stored subprograms
• Calling from SQL and PL/SQL