Handling Exceptions in Oracle PL/SQL

By /

Exception handling is a critical part of Oracle PL/SQL programming. In real-world database applications, runtime errors are inevitable—missing data, invalid user inputs, constraint violations, or unexpected system issues. Robust exception handling prevents abrupt failures and ensures application stability.

This blog explains Oracle PL/SQL Exception Handling from fundamentals to advanced usage, using easy language, visual diagrams, HR schema examples, and real-time troubleshooting scenarios.

1. What Is an Exception?

An exception is a runtime error that occurs during PL/SQL program execution and interrupts the normal flow of statements.

Simple Definition:

An exception is raised when Oracle encounters an unexpected situation while executing PL/SQL code.

Common Examples:

  • NO_DATA_FOUND
  • TOO_MANY_ROWS
  • ZERO_DIVIDE
  • Constraint violations (ORA-00001, ORA-02291)

2. Understanding Exceptions with PL/SQL

PL/SQL provides a structured and readable mechanism to handle runtime errors gracefully instead of terminating execution abruptly.

Benefits:

  • Prevents application crashes
  • Displays meaningful messages
  • Supports logging and debugging
  • Enables business-rule enforcement

3. Handling Exceptions Flow

Execution follows this sequence:

  1. PL/SQL executes statements inside the BEGIN block
  2. If no error occurs → program ends normally
  3. If an error occurs:
    • Oracle stops execution
    • Searches for a matching exception handler
  4. If found → executes handler
  5. If not → exception propagates to outer block or calling program

4. Exception Types in Oracle PL/SQL

Types of Exceptions

1. Predefined Exceptions

Declared automatically by Oracle.

Examples:

  • NO_DATA_FOUND
  • TOO_MANY_ROWS
  • ZERO_DIVIDE

2. Non-Predefined Exceptions

Oracle errors without predefined names.
Handled using error numbers with PRAGMA EXCEPTION_INIT.

3. User-Defined Exceptions

Created by developers for custom business logic.

5. Syntax to Trap Exceptions

BEGIN
— executable statements
EXCEPTION
WHEN exception_name THEN
— handling code
WHEN OTHERS THEN
— default handler
END;

Key Points:

  • EXCEPTION block executes only if an error occurs
  • WHEN OTHERS must be the last handler

6. Handling the Exception: A Simple Example (HR Schema)

DECLARE
   v_salary employees.salary%TYPE;
BEGIN
   SELECT salary
   INTO v_salary
   FROM employees
   WHERE employee_id = 999;

   DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee does not exist');
END;

Explanation

  • If employee_id = 999 does not exist
  • Oracle raises NO_DATA_FOUND
  • Program prints a user-friendly message

7. Guidelines for Trapping Exceptions

✔ Handle specific exceptions first
✔ Use WHEN OTHERS only for logging or re-raising
✔ Display meaningful error messages
❌ Never suppress errors silently

Best Practice:

WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE(SQLERRM);
   RAISE;

8. Trapping Predefined Oracle Server Errors

Common predefined exceptions:

ExceptionDescription
NO_DATA_FOUNDNo rows returned
TOO_MANY_ROWSMultiple rows returned
ZERO_DIVIDEDivision by zero
DUP_VAL_ON_INDEXUnique constraint violation

Example:

BEGIN
   DBMS_OUTPUT.PUT_LINE(100 / 0);
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Division by zero is not allowed');
END;

9. Trapping Non-Predefined Oracle Server Errors

Some Oracle errors do not have names, such as:

  • ORA-00001 (Unique constraint violation)
  • ORA-02291 (Foreign key violation)

These must be trapped using:

PRAGMA EXCEPTION_INIT

10. Non-Predefined Error Trapping: Example

DECLARE
   e_dup_email EXCEPTION;
   PRAGMA EXCEPTION_INIT(e_dup_email, -1);
BEGIN
   INSERT INTO employees(employee_id, email, last_name, hire_date, job_id)
   VALUES (300, 'SKING', 'Test', SYSDATE, 'IT_PROG');
EXCEPTION
   WHEN e_dup_email THEN
      DBMS_OUTPUT.PUT_LINE('Duplicate email not allowed');
END;

11. Oracle Server Errors Overview

  • Raised automatically by Oracle
  • Start with ORA-XXXXX
  • Indicate data integrity or system-level issues

12. Functions for Trapping Exceptions

SQLCODE

Returns numeric error code

SQLERRM

Returns descriptive error message

Example:

BEGIN
   DELETE FROM employees WHERE employee_id = 999;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Code: ' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE('Message: ' || SQLERRM);
END;

13. Trapping User-Defined Exceptions

DECLARE
   e_low_salary EXCEPTION;
   v_salary NUMBER := 2000;
BEGIN
   IF v_salary < 3000 THEN
      RAISE e_low_salary;
   END IF;
EXCEPTION
   WHEN e_low_salary THEN
      DBMS_OUTPUT.PUT_LINE('Salary below minimum threshold');
END;

14. Propagating Exceptions in a Subblock

BEGIN
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Inner block handled’);
RAISE;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Outer block handled’);
END;

15. RAISE_APPLICATION_ERROR Procedure

Used to raise custom business errors:

BEGIN
   IF SYSDATE > DATE '2025-12-31' THEN
      RAISE_APPLICATION_ERROR(-20001, 'Date limit exceeded');
   END IF;
END;

✔ Error range: -20000 to -20999

16. Real-Time Use Cases

  • Banking transaction validation
  • HR data integrity checks
  • Payroll salary limits
  • Centralized error logging systems

17. Common Troubleshooting Scenarios

IssueFix
Program terminatesAdd exception block
Generic errorUse SQLERRM
Errors hiddenAvoid empty WHEN OTHERS
Debugging difficultyLog SQLCODE & SQLERRM

18. Assignment Questions

Theory

  1. Difference between predefined and user-defined exceptions?
  2. Why should WHEN OTHERS be last?
  3. What is exception propagation?

Practical

  1. Handle TOO_MANY_ROWS using HR schema
  2. Create user-defined exception for invalid job_id
  3. Trap ORA-02291 using PRAGMA EXCEPTION_INIT
  4. Log errors into an error table

More Practical Exception Handling Examples in Oracle PL/SQL

Example 1: Handle TOO_MANY_ROWS Exception

Purpose

To handle scenarios where a SELECT INTO statement returns more than one row.

Real-Time Use Case

Fetching employee details using non-unique columns like department_id.

Code

DECLARE
   v_salary employees.salary%TYPE;
BEGIN
   SELECT salary
   INTO v_salary
   FROM employees
   WHERE department_id = 90;

   DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('More than one employee found for the department');
END;

Explanation

  • department_id = 90 has multiple employees
  • SELECT INTO expects only one row
  • Oracle raises TOO_MANY_ROWS
  • Exception handler prevents abrupt termination

Example 2: Handle NO_DATA_FOUND in DELETE Operation

Purpose

To detect when a DELETE operation affects no rows.

Real-Time Use Case

Deleting an employee record safely.

Code

BEGIN
   DELETE FROM employees WHERE employee_id = 999;

   IF SQL%ROWCOUNT = 0 THEN
      RAISE NO_DATA_FOUND;
   END IF;

   DBMS_OUTPUT.PUT_LINE('Employee deleted successfully');
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee not found for deletion');
END;

Explanation

  • SQL%ROWCOUNT checks affected rows
  • If zero rows deleted → custom raise of NO_DATA_FOUND
  • Useful in admin or HR maintenance programs

Example 3: Handling DUP_VAL_ON_INDEX Exception

Purpose

To prevent duplicate employee insertion.

Real-Time Use Case

During onboarding, duplicate employee IDs or emails may be entered.

Code

BEGIN
   INSERT INTO employees(employee_id, email, last_name, hire_date, job_id)
   VALUES (101, 'SKING', 'Test', SYSDATE, 'IT_PROG');

   DBMS_OUTPUT.PUT_LINE('Employee inserted');
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      DBMS_OUTPUT.PUT_LINE('Duplicate employee ID or email detected');
END;

Explanation

  • Violates unique constraint on employee_id or email
  • Oracle raises DUP_VAL_ON_INDEX
  • User-friendly message is displayed

Example 4: Logging Errors Using SQLCODE and SQLERRM

Purpose

To capture and log error details for debugging.

Real-Time Use Case

Production systems log errors into tables instead of showing raw messages.

Code

BEGIN
   UPDATE employees SET salary = salary / 0
   WHERE employee_id = 100;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
END;

Explanation

  • Division by zero triggers error
  • SQLCODE gives numeric error
  • SQLERRM gives descriptive message
  • Useful for audit and support teams

Example 5: User-Defined Exception for Business Rule Validation

Purpose

To enforce a minimum salary rule.

Real-Time Use Case

HR policies often restrict minimum salary per role.

Code

DECLARE
   e_salary_low EXCEPTION;
   v_salary employees.salary%TYPE := 2500;
BEGIN
   IF v_salary < 3000 THEN
      RAISE e_salary_low;
   END IF;

   DBMS_OUTPUT.PUT_LINE('Salary is valid');
EXCEPTION
   WHEN e_salary_low THEN
      DBMS_OUTPUT.PUT_LINE('Salary does not meet company standards');
END;

Explanation

  • Business rule checked manually
  • Custom exception raised
  • Cleaner than embedding IF logic everywhere

Example 6: Handling Non-Predefined Error ORA-02291

Purpose

To trap foreign key constraint violations.

Real-Time Use Case

Assigning an employee to a non-existing department.

Code

DECLARE
   e_fk_violation EXCEPTION;
   PRAGMA EXCEPTION_INIT(e_fk_violation, -2291);
BEGIN
   UPDATE employees
   SET department_id = 999
   WHERE employee_id = 100;
EXCEPTION
   WHEN e_fk_violation THEN
      DBMS_OUTPUT.PUT_LINE('Invalid department ID');
END;

Explanation

  • Department 999 does not exist
  • ORA-02291 raised
  • Exception mapped using PRAGMA EXCEPTION_INIT

Example 7: Exception Propagation with Re-Raise

Purpose

To handle an exception partially and propagate it.

Real-Time Use Case

Centralized logging with final handling at top level.

Code

BEGIN
   BEGIN
      DELETE FROM employees WHERE employee_id = 999;

      IF SQL%ROWCOUNT = 0 THEN
         RAISE NO_DATA_FOUND;
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('Inner block logged issue');
         RAISE;
   END;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Outer block handled exception');
END;

Explanation

  • Inner block logs the error
  • RAISE propagates exception
  • Outer block performs final handling

Example 8: RAISE_APPLICATION_ERROR with Custom Message

Purpose

To stop execution with meaningful business error.

Real-Time Use Case

Blocking salary updates beyond policy limits.

Code

BEGIN
   IF 15000 > 12000 THEN
      RAISE_APPLICATION_ERROR(
         -20010,
         'Salary increment exceeds allowed limit'
      );
   END IF;
END;

Explanation

  • Custom error number and message
  • Ideal for APIs, reports, front-end integrations

Example 9: WHEN OTHERS with Re-Raise Best Practice

Purpose

To log error and still allow Oracle to report it.

Code

BEGIN
   UPDATE employees SET salary = salary / 0
   WHERE employee_id = 100;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
      RAISE;
END;

Explanation

  • Logs error for debugging
  • Re-raises exception
  • Prevents hiding critical issues

Example 10: Exception Handling Inside a Procedure

Purpose

To make procedures robust and reusable.

Code

CREATE OR REPLACE PROCEDURE get_employee_salary (
   p_emp_id IN employees.employee_id%TYPE
) IS
   v_salary employees.salary%TYPE;
BEGIN
   SELECT salary INTO v_salary
   FROM employees
   WHERE employee_id = p_emp_id;

   DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/

Explanation

  • Procedure handles its own exceptions
  • Prevents failure at calling level
  • Suitable for enterprise applications

19. Disclaimer & Copyright Clarification

Disclaimer

This article is for educational purposes only. All examples use the Oracle HR sample schema and should be tested in a non-production environment.

Scroll to Top