PL/SQL Exception Handling
Introduction
❖ 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 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:
1. Predefined Exceptions – Built-in exceptions that cover common errors.
2. User-Defined Exceptions – Custom exceptions declared by the programmer.
3. Unnamed System Exceptions – System exceptions that do not have predefined
names but can still be handled.
Syntax for Exception Handling
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
........
WHEN others THEN
exception3-handling-statements
END;
/
Example
DECLARE
numerator NUMBER := 10;
denominator NUMBER := 0;
result NUMBER;
BEGIN
result:= numerator / denominator;
DBMS_OUTPUT.PUT_LINE('Result: ' || result);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero');
END;
/
Predefined Exceptions in PL/SQL
Example 1: Handling DUP_VAL_ON_INDEX
Declare
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;
/
User-Defined Exceptions
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 (C_product_id NUMBER, quantity
NUMBER) IS
low_inventory EXCEPTION;
current_stock NUMBER;
BEGIN
SELECT stock INTO current_stock FROM products WHERE product_id = C_product_id;
IF current_stock < quantity THEN
RAISE low_inventory;
END IF;
EXCEPTION
WHEN low_inventory THEN
DBMS_OUTPUT.PUT_LINE('Insufficient stock!');
END;
/
CREATE OR REPLACE PROCEDURE check_inventory (
C_product_id IN NUMBER,
quantity IN NUMBER
) IS
low_inventory EXCEPTION;
current_stock NUMBER;
BEGIN
SELECT stock
INTO current_stock
FROM products
WHERE product_id = C_product_id;
IF current_stock < quantity THEN
RAISE low_inventory;
END IF;
EXCEPTION
WHEN low_inventory THEN
DBMS_OUTPUT.PUT_LINE('Insufficient stock for product ID ' || C_product_id || '.');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Product ID ' || C_product_id || ' not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
Handling Unnamed System Exceptions
Unnamed system exceptions (e.g., ORA-02292) can be handled using their error codes with
PRAGMA EXCEPTION_INIT.
Example:
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;
/
WHEN OTHERS Clause
WHEN OTHERS acts as a catch-all handler but should be used cautiously.
● 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; /
