Handling Exceptions in Oracle PL/SQL

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:

PL/SQL executes statements inside the BEGIN block
If no error occurs → program ends normally
If an error occurs:
- Oracle stops execution
- Searches for a matching exception handler
If found → executes handler
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:

Exception	Description
NO_DATA_FOUND	No rows returned
TOO_MANY_ROWS	Multiple rows returned
ZERO_DIVIDE	Division by zero
DUP_VAL_ON_INDEX	Unique 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

Issue	Fix
Program terminates	Add exception block
Generic error	Use SQLERRM
Errors hidden	Avoid empty WHEN OTHERS
Debugging difficulty	Log SQLCODE & SQLERRM

18. Assignment Questions

Theory

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

Practical

Handle TOO_MANY_ROWS using HR schema
Create user-defined exception for invalid job_id
Trap ORA-02291 using PRAGMA EXCEPTION_INIT
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.