The Ultimate Guide to Exception Handling in PL/SQL: From Beginner to Expert

Exception handling is a critical part of PL/SQL programming that ensures smooth execution by handling errors gracefully. This comprehensive guide will cover everything from basic concepts to advanced techniques in exception handling, complete with practical examples, best practices, and in-depth explanations.

Table of Contents

1. Introduction to Exception Handling in PL/SQL
2. Basic Exception Handling Syntax in PL/SQL
3. Predefined Exceptions in PL/SQL
4. User-Defined Exceptions in PL/SQL
5. Handling Unnamed System Exceptions
6. The WHEN OTHERS Clause
7. RAISE_APPLICATION_ERROR for Custom Error Messages
8. Propagation and Nesting of Exceptions
9. Best Practices for Exception Handling in PL/SQL
10. Advanced Exception Handling Techniques
11. Exception Propagation in Subprograms and Packages
12. DML Error Logging with LOG ERRORS Clause
13. Exception Handling in Dynamic SQL
14. Custom Error Stacks and Chaining
15. Autonomous Transactions for Isolated Logging
16. Case Study: E-Commerce Order Processing
17. Exception Handling in Parallel Pipelines
18. Using SAVEPOINTS for Partial Rollbacks
19. Advanced Logging with DBMS_UTILITY
20. Testing Exception Handling Strategies
21. Exception Handling in Compound Triggers

1. Introduction to Exception Handling in PL/SQL

Exception handling allows a program to detect and respond to runtime errors in a controlled manner, preventing abrupt terminations. PL/SQL provides a structured approach to handle exceptions, ensuring data integrity and application reliability.

Why is Exception Handling Important?

• Prevents unexpected termination of PL/SQL blocks.
• Improves code reliability and maintainability.
• Helps log and debug errors efficiently.
• Ensures data integrity: Prevents partial updates by rolling back transactions or handling errors mid-operation.

Types of Exceptions:

PL/SQL exceptions are broadly categorized into three types:

• Predefined Exceptions – Built-in exceptions that cover common errors.
• User-Defined Exceptions – Custom exceptions declared by the programmer.
• Unnamed System Exceptions – System exceptions that do not have predefined names but can still be handled.

2. Basic Exception Handling Syntax in PL/SQL

A basic exception handling block consists of the following structure:

BEGIN
   -- Executable statements
EXCEPTION
   WHEN exception_name THEN
      -- Handling statements
END;

Key Notes:

• The EXCEPTION block is optional but recommended to handle errors gracefully.
• Without exception handling, errors propagate to the calling environment (e.g., SQL*Plus), causing abrupt termination.

Example Without Handling:

BEGIN
   DELETE FROM employees WHERE department_id = 999; -- No such department
END;
-- Error: "ORA-02292: integrity constraint violated - child record found"

Example With Handling:

BEGIN
   DELETE FROM employees WHERE department_id = 999;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Deletion failed. Check child records.');
END;

3. Predefined Exceptions in PL/SQL

PL/SQL provides several predefined exceptions for handling common runtime errors.

Example 1: Handling DUP_VAL_ON_INDEX

BEGIN
   INSERT INTO employees (employee_id, name) VALUES (101, 'John Doe');
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      DBMS_OUTPUT.PUT_LINE('Employee ID already exists!');
END;

Example 2: Handling VALUE_ERROR

DECLARE
   v_number NUMBER;
BEGIN
   v_number := 'ABC'; -- Invalid conversion
EXCEPTION
   WHEN VALUE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Invalid value assigned.');
END;

4. User-Defined Exceptions in PL/SQL

User-defined exceptions are ideal for enforcing business rules. They require explicit declaration and raising.

Steps to Declare and Handle:

1. Declare the exception in the DECLARE section.
2. Raise it using RAISE in the executable section.
3. Handle it in the EXCEPTION block.

Example: Raising in a Stored Procedure

CREATE OR REPLACE PROCEDURE check_inventory (p_product_id NUMBER, p_quantity NUMBER) IS
   ex_low_inventory EXCEPTION;
   v_current_stock NUMBER;
BEGIN
   SELECT stock INTO v_current_stock FROM products WHERE product_id = p_product_id;
   IF v_current_stock < p_quantity THEN
      RAISE ex_low_inventory;
   END IF;
EXCEPTION
   WHEN ex_low_inventory THEN
      DBMS_OUTPUT.PUT_LINE('Insufficient stock!');
END;
/

5. Handling Unnamed System Exceptions

Unnamed system exceptions (e.g., ORA-02292) can be handled using their error codes with PRAGMA EXCEPTION_INIT.

Example: Handling ORA-02292 (Child Record Found)

DECLARE
   ex_child_record EXCEPTION;
   PRAGMA EXCEPTION_INIT(ex_child_record, -2292);
BEGIN
   DELETE FROM departments WHERE department_id = 999;
EXCEPTION
   WHEN ex_child_record THEN
      DBMS_OUTPUT.PUT_LINE('Cannot delete: Child records exist.');
END;

6. The WHEN OTHERS Clause

WHEN OTHERS acts as a catch-all handler but should be used cautiously.

Best Practices:

• Always log the error details (e.g., SQLCODE, SQLERRM).
• Re-raise the exception if it cannot be resolved locally.

Example:

BEGIN
   DELETE FROM orders WHERE order_id = 1001;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error ' || SQLCODE || ': ' || SQLERRM);
      RAISE; -- Propagate the error
END;

7. RAISE_APPLICATION_ERROR for Custom Error Messages

Use RAISE_APPLICATION_ERROR to throw user-friendly errors with codes between -20000 and -20999.

Example with User-Defined Exception:

DECLARE
   ex_invalid_discount EXCEPTION;
BEGIN
   IF :NEW.discount > 50 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Discount cannot exceed 50%.');
   END IF;
END;

8. Propagation and Nesting of Exceptions

Exceptions propagate to outer blocks if unhandled. Nested blocks allow localized handling.

Real-World Example:

DECLARE
   ex_outer EXCEPTION;
BEGIN
   BEGIN -- Inner block
      DELETE FROM employees WHERE employee_id = 999;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('Employee not found.');
   END;
   
   BEGIN -- Another inner block
      RAISE ex_outer;
   EXCEPTION
      WHEN ex_outer THEN
         DBMS_OUTPUT.PUT_LINE('Inner block handled ex_outer.');
         RAISE; -- Propagate to outer block
   END;
EXCEPTION
   WHEN ex_outer THEN
      DBMS_OUTPUT.PUT_LINE('Outer block handled ex_outer.');
END;

9. Best Practices for Exception Handling in PL/SQL

• Log Exceptions: Use autonomous transactions to log errors without affecting the main transaction.
• Test Exception Paths: Validate handlers with unit tests.
• Avoid Empty Handlers: Never leave WHEN OTHERS without logging or re-raising.

Example: Logging with Autonomous Transaction

CREATE TABLE error_log (
   log_id      NUMBER GENERATED ALWAYS AS IDENTITY,
   error_code  NUMBER,
   error_msg   VARCHAR2(4000),
   log_time    TIMESTAMP
);

CREATE OR REPLACE PROCEDURE log_error IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO error_log (error_code, error_msg, log_time)
   VALUES (SQLCODE, SQLERRM, SYSTIMESTAMP);
   COMMIT;
END;
/

BEGIN
   DELETE FROM departments WHERE department_id = 999;
EXCEPTION
   WHEN OTHERS THEN
      log_error();
      RAISE;
END;

10. Advanced Exception Handling Techniques

Bulk Operations with SAVE EXCEPTIONS

Handle multiple DML errors in bulk operations using FORALL with SAVE EXCEPTIONS.

Example:

DECLARE
   TYPE t_emp_ids IS TABLE OF NUMBER;
   v_emp_ids t_emp_ids := t_emp_ids(101, 102, 999); -- Invalid ID 999
   ex_dml_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
   FORALL i IN 1..v_emp_ids.COUNT SAVE EXCEPTIONS
      DELETE FROM employees WHERE employee_id = v_emp_ids(i);
EXCEPTION
   WHEN ex_dml_errors THEN
      DBMS_OUTPUT.PUT_LINE(SQL%BULK_EXCEPTIONS.COUNT || ' errors occurred:');
      FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
         DBMS_OUTPUT.PUT_LINE('Error ' || j || ': ' ||
            SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
      END LOOP;
END;

Exception Handling in Triggers

Use RAISE_APPLICATION_ERROR in triggers to enforce business rules.

Example:

CREATE OR REPLACE TRIGGER check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
   IF :NEW.salary < 3000 THEN
      RAISE_APPLICATION_ERROR(-20002, 'Salary below minimum threshold.');
   END IF;
END;

Case Study: E-Commerce Order Processing

Scenario: Handle inventory checks, payment validation, and order logging in a transaction.

DECLARE
   ex_payment_failed EXCEPTION;
   ex_inventory_shortage EXCEPTION;
BEGIN
   -- Step 1: Check inventory
   IF inventory_check() = FALSE THEN
      RAISE ex_inventory_shortage;
   END IF;

   -- Step 2: Process payment
   IF process_payment() = FALSE THEN
      RAISE ex_payment_failed;
   END IF;

   -- Step 3: Log order
   log_order();

   COMMIT;
EXCEPTION
   WHEN ex_inventory_shortage THEN
      DBMS_OUTPUT.PUT_LINE('Order failed: Insufficient inventory.');
      ROLLBACK;
   WHEN ex_payment_failed THEN
      DBMS_OUTPUT.PUT_LINE('Order failed: Payment declined.');
      ROLLBACK;
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
      ROLLBACK;
END;

11. Exception Propagation in Subprograms and Packages

Exceptions can propagate across procedures, functions, and packages. Unhandled exceptions in nested subprograms bubble up to the caller.

Example: Propagation Across Procedures

CREATE OR REPLACE PROCEDURE inner_proc IS
BEGIN
   RAISE NO_DATA_FOUND; -- Explicitly raise an exception
END;
/

CREATE OR REPLACE PROCEDURE outer_proc IS
BEGIN
   inner_proc;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Outer procedure handled the error.');
END;
/

BEGIN
   outer_proc;
END;

12. DML Error Logging with LOG ERRORS Clause

Oracle’s LOG ERRORS clause allows bulk DML operations to continue despite errors, logging issues to a specified table.

Step-by-Step Implementation:

1. Create an Error Log Table:

CREATE TABLE error_log (
   log_id      NUMBER GENERATED ALWAYS AS IDENTITY,
   error_code  NUMBER,
   error_msg   VARCHAR2(4000),
   log_time    TIMESTAMP
);

1. Use LOG ERRORS in DML:

INSERT ALL
   INTO employees (employee_id, name) VALUES (101, 'John Doe')
   INTO employees (employee_id, name) VALUES (102, 'Jane Smith')
   INTO employees (employee_id, name) VALUES (999, 'Invalid Employee') -- Invalid
LOG ERRORS INTO error_log ('INSERT INTO employees') REJECT LIMIT UNLIMITED;

13. Exception Handling in Dynamic SQL

Handle exceptions in dynamically constructed SQL statements using EXECUTE IMMEDIATE.

Example: Safely Executing Dynamic SQL

DECLARE
   v_sql VARCHAR2(200) := 'DELETE FROM non_existent_table';
BEGIN
   EXECUTE IMMEDIATE v_sql;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Dynamic SQL Error: ' || SQLERRM);
END;

14. Custom Error Stacks and Chaining

Chain multiple exceptions to create detailed error stacks using RAISE_APPLICATION_ERROR.

Example: Chaining Errors

DECLARE
   ex_custom_error EXCEPTION;
   PRAGMA EXCEPTION_INIT(ex_custom_error, -20001);
BEGIN
   BEGIN
      RAISE ex_custom_error;
   EXCEPTION
      WHEN ex_custom_error THEN
         RAISE_APPLICATION_ERROR(-20002, 'Secondary error caused by -20001');
   END;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error Stack: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
END;

15. Autonomous Transactions for Isolated Logging

Use autonomous transactions to log errors without affecting the main transaction’s rollback/commit.

Example:

CREATE OR REPLACE PROCEDURE log_error IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO error_log (error_code, error_msg)
   VALUES (SQLCODE, SQLERRM);
   COMMIT;
END;
/

BEGIN
   DELETE FROM departments WHERE department_id = 999;
   log_error(); -- Logs error even if main transaction rolls back
   ROLLBACK;
EXCEPTION
   WHEN OTHERS THEN
      log_error();
      RAISE;
END;

16. Case Study: Banking Transaction System

Handle overdrafts, concurrency, and invalid accounts in a transaction.

DECLARE
   ex_overdraft        EXCEPTION;
   ex_invalid_account  EXCEPTION;
   PRAGMA EXCEPTION_INIT(ex_invalid_account, -20003);
BEGIN
   -- Check account validity
   IF NOT account_exists(:acct_id) THEN
      RAISE_APPLICATION_ERROR(-20003, 'Invalid account.');
   END IF;

   -- Check balance
   IF get_balance(:acct_id) - :amount < 0 THEN
      RAISE ex_overdraft;
   END IF;

   -- Process transaction
   UPDATE accounts SET balance = balance - :amount WHERE id = :acct_id;
   COMMIT;

EXCEPTION
   WHEN ex_overdraft THEN
      DBMS_OUTPUT.PUT_LINE('Transaction failed: Insufficient funds.');
      ROLLBACK;
   WHEN ex_invalid_account THEN
      DBMS_OUTPUT.PUT_LINE('Transaction failed: Invalid account.');
      ROLLBACK;
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
      ROLLBACK;
END;

17. Exception Handling in Parallel Pipelines

Handle exceptions in parallel-enabled pipelined functions.

Example:

CREATE OR REPLACE FUNCTION process_data RETURN data_t PIPELINED PARALLEL_ENABLE IS
   ex_parallel_error EXCEPTION;
BEGIN
   FOR rec IN (SELECT * FROM large_table) LOOP
      BEGIN
         -- Complex transformation
         PIPE ROW(rec);
      EXCEPTION
         WHEN OTHERS THEN
            RAISE ex_parallel_error;
      END;
   END LOOP;
EXCEPTION
   WHEN ex_parallel_error THEN
      DBMS_OUTPUT.PUT_LINE('Parallel processing error.');
END;

18. Using SAVEPOINTS for Partial Rollbacks

Combine SAVEPOINT with exceptions to roll back specific operations.

Example:

BEGIN
   SAVEPOINT before_insert;
   INSERT INTO orders (order_id) VALUES (1001);

   SAVEPOINT before_update;
   UPDATE inventory SET stock = stock - 10 WHERE product_id = 500;

EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK TO before_update; -- Undo only the update
      DBMS_OUTPUT.PUT_LINE('Inventory update rolled back.');
      RAISE;
END;

19. Advanced Logging with DBMS_UTILITY

Leverage DBMS_UTILITY to capture detailed error context.

Example:

DECLARE
   v_error_stack VARCHAR2(4000);
BEGIN
   RAISE PROGRAM_ERROR;
EXCEPTION
   WHEN OTHERS THEN
      v_error_stack := DBMS_UTILITY.FORMAT_ERROR_STACK || CHR(10) || 
                       DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      DBMS_OUTPUT.PUT_LINE('Full Error Trace: ' || v_error_stack);
END;

20. Testing Exception Handling Strategies

Use Oracle’s UTL_TEST or third-party tools to simulate exceptions.

Example: Unit Test for NO_DATA_FOUND

CREATE OR REPLACE PROCEDURE test_no_data_found IS
   v_result VARCHAR2(100);
BEGIN
   BEGIN
      SELECT name INTO v_result FROM employees WHERE 1 = 0;
      UTL_TEST.EXCEPTION_FAIL('Expected NO_DATA_FOUND');
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         UTL_TEST.EXCEPTION_PASS('NO_DATA_FOUND handled correctly');
   END;
END;

21. Exception Handling in Compound Triggers

Handle errors in compound triggers with separate exception sections for each timing point.

Example:

CREATE OR REPLACE TRIGGER compound_trigger
FOR INSERT ON employees
COMPOUND TRIGGER

   ex_salary_error EXCEPTION;

BEFORE EACH ROW IS
BEGIN
   IF :NEW.salary < 0 THEN
      RAISE ex_salary_error;
   END IF;
EXCEPTION
   WHEN ex_salary_error THEN
      RAISE_APPLICATION_ERROR(-20004, 'Salary cannot be negative.');
END BEFORE EACH ROW;
END;
/

Mastering exception handling in PL/SQL is essential for writing robust, error-free applications. By leveraging predefined exceptions, user-defined errors, and advanced techniques like bulk error handling and autonomous logging, developers can build resilient systems that maintain data integrity and user trust. Practice these strategies with real-world scenarios to solidify your expertise. ```