PL/SQL Exception Handling Cheat Sheet - Error Handling Best Practices

PL/SQL Exception Handling Cheat Sheet

Quick reference for Oracle PL/SQL exception handling with practical examples.

Basic Exception Block

DECLARE
  v_result NUMBER;
BEGIN
  v_result := 10 / 0;  -- This will raise an exception
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Cannot divide by zero');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

---

Predefined Exceptions

Common Named Exceptions

Exception	ORA Error	Description
NO_DATA_FOUND	ORA-01403	SELECT INTO returned no rows
TOO_MANY_ROWS	ORA-01422	SELECT INTO returned multiple rows
ZERO_DIVIDE	ORA-01476	Division by zero
DUP_VAL_ON_INDEX	ORA-00001	Unique constraint violation
VALUE_ERROR	ORA-06502	Numeric or value error
INVALID_CURSOR	ORA-01001	Invalid cursor operation
CURSOR_ALREADY_OPEN	ORA-06511	Cursor already open
INVALID_NUMBER	ORA-01722	Invalid number conversion
LOGIN_DENIED	ORA-01017	Invalid username/password
NOT_LOGGED_ON	ORA-01012	Not connected to database
PROGRAM_ERROR	ORA-06501	Internal PL/SQL error
TIMEOUT_ON_RESOURCE	ORA-00051	Timeout waiting for resource
ACCESS_INTO_NULL	ORA-06530	Access null object
COLLECTION_IS_NULL	ORA-06531	Collection not initialized
SUBSCRIPT_BEYOND_COUNT	ORA-06533	Index exceeds collection count
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	Index outside valid range

Using Named Exceptions

DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT employee_name INTO v_name
  FROM employees
  WHERE employee_id = 99999;

  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Multiple employees found');
END;
/

---

SQLCODE and SQLERRM

DECLARE
  v_code NUMBER;
  v_msg  VARCHAR2(200);
BEGIN
  -- Some operation that might fail
  NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_code := SQLCODE;
    v_msg  := SQLERRM;

    DBMS_OUTPUT.PUT_LINE('Error Code: ' || v_code);
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || v_msg);

    -- Log to error table
    INSERT INTO error_log (error_code, error_message, error_date)
    VALUES (v_code, v_msg, SYSDATE);
    COMMIT;
END;
/

SQLERRM with Error Code

-- Get message for specific error
SELECT SQLERRM(-1403) FROM DUAL;  -- ORA-01403: no data found
SELECT SQLERRM(-1) FROM DUAL;     -- ORA-00001: unique constraint violated

---

User-Defined Exceptions

Declare and Raise

DECLARE
  e_invalid_salary EXCEPTION;
  v_salary NUMBER := -1000;
BEGIN
  IF v_salary < 0 THEN
    RAISE e_invalid_salary;
  END IF;

  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
  WHEN e_invalid_salary THEN
    DBMS_OUTPUT.PUT_LINE('Error: Salary cannot be negative');
END;
/

Associate Exception with Error Code

DECLARE
  e_no_employee EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_no_employee, -20001);
BEGIN
  -- Code that might raise -20001
  RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
EXCEPTION
  WHEN e_no_employee THEN
    DBMS_OUTPUT.PUT_LINE('Custom handling for employee not found');
END;
/

---

RAISE_APPLICATION_ERROR

Raise custom application errors (-20000 to -20999)

DECLARE
  v_balance NUMBER := 100;
  v_withdrawal NUMBER := 500;
BEGIN
  IF v_withdrawal > v_balance THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Insufficient funds. Balance: ' || v_balance ||
      ', Requested: ' || v_withdrawal);
  END IF;

  v_balance := v_balance - v_withdrawal;
END;
/

With Error Stack Preservation

BEGIN
  -- Some operation
  NULL;
EXCEPTION
  WHEN OTHERS THEN
    -- TRUE preserves the error stack
    RAISE_APPLICATION_ERROR(-20001, 'Operation failed: ' || SQLERRM, TRUE);
END;
/

---

Exception Propagation

CREATE OR REPLACE PROCEDURE inner_proc AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/

CREATE OR REPLACE PROCEDURE outer_proc AS
BEGIN
  inner_proc();
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Exception caught in outer_proc');
    -- Re-raise to caller
    RAISE;
END;
/

---

DBMS_UTILITY Error Functions

Format Error Stack

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error Stack:');
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;

Format Error Backtrace

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error Backtrace:');
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;

Format Call Stack

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Call Stack:');
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
END;

Complete Error Logging

EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO error_log (
      error_date,
      error_code,
      error_message,
      error_stack,
      error_backtrace
    ) VALUES (
      SYSDATE,
      SQLCODE,
      SQLERRM,
      DBMS_UTILITY.FORMAT_ERROR_STACK,
      DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
    );
    COMMIT;
    RAISE;
END;

---

Exception Handling Patterns

Selective Re-raise

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Handle silently
    NULL;
  WHEN DUP_VAL_ON_INDEX THEN
    -- Log and continue
    log_error(SQLCODE, SQLERRM);
  WHEN OTHERS THEN
    -- Unknown errors - re-raise
    log_error(SQLCODE, SQLERRM);
    RAISE;
END;

Save Exception in Bulk Operations (FORALL)

DECLARE
  TYPE t_emp_ids IS TABLE OF NUMBER;
  v_emp_ids t_emp_ids := t_emp_ids(1, 2, 99999, 3);
  v_error_count NUMBER;
BEGIN
  FORALL i IN 1..v_emp_ids.COUNT SAVE EXCEPTIONS
    UPDATE employees SET salary = salary * 1.1
    WHERE employee_id = v_emp_ids(i);
EXCEPTION
  WHEN OTHERS THEN
    v_error_count := SQL%BULK_EXCEPTIONS.COUNT;
    DBMS_OUTPUT.PUT_LINE('Errors: ' || v_error_count);

    FOR i IN 1..v_error_count LOOP
      DBMS_OUTPUT.PUT_LINE('Index: ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    END LOOP;
END;
/

Transaction Control with Exceptions

DECLARE
  v_savepoint VARCHAR2(30) := 'before_update';
BEGIN
  SAVEPOINT before_update;

  UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO before_update;
    log_error(SQLCODE, SQLERRM);
    RAISE;
END;
/

---

Autonomous Transaction for Logging

CREATE OR REPLACE PROCEDURE log_error(
  p_code IN NUMBER,
  p_message IN VARCHAR2
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (error_date, error_code, error_message)
  VALUES (SYSDATE, p_code, p_message);
  COMMIT;  -- Commits only this insert, not the main transaction
END;
/

-- Usage
BEGIN
  -- Some operation that fails
  RAISE NO_DATA_FOUND;
EXCEPTION
  WHEN OTHERS THEN
    log_error(SQLCODE, SQLERRM);  -- Error is logged even if main txn rolls back
    RAISE;
END;
/

---

Best Practices

1. Always handle expected exceptions explicitly

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Specific handling
  WHEN OTHERS THEN
    -- Catch-all for unexpected

2. Never silently swallow exceptions

-- BAD
EXCEPTION
  WHEN OTHERS THEN NULL;

-- GOOD
EXCEPTION
  WHEN OTHERS THEN
    log_error(SQLCODE, SQLERRM);
    RAISE;

3. Use RAISE_APPLICATION_ERROR for business errors

IF v_status = 'CLOSED' THEN
  RAISE_APPLICATION_ERROR(-20001, 'Account is closed');
END IF;

4. Log error context

EXCEPTION
  WHEN OTHERS THEN
    log_error(SQLCODE, SQLERRM || ' for ID=' || v_id);

---

Related Resources

• ORA-06512: At Line
• ORA-01403: No Data Found
• ORA-01422: Single-row Returns More
• ORA-04091: Table Mutating