Unit - 3 Exception Handling and Cursors
Exception Handling in PL/SQL
Introduction
Exception handling is a crucial aspect of PL/SQL programming that allows you to gracefully
handle errors that occur during program execution. It prevents abrupt termination and provides
mechanisms to recover from errors or take appropriate actions.
Types of Exceptions
● System-defined exceptions: Predefined exceptions raised by the Oracle database for
various error conditions (e.g., NO_DATA_FOUND, ZERO_DIVIDE,
TOO_MANY_ROWS).
● User-defined exceptions: Custom exceptions defined by the programmer to handle
specific error conditions.
Exception Handling Structure
Exception handling is implemented using an EXCEPTION block within a PL/SQL block.
DECLARE
-- Declarations
EXCEPTION
WHEN exception_name THEN
-- Exception handling code
WHEN OTHERS THEN
-- Default exception handler
END;
Key Components
● DECLARE section: Declares variables, cursors, and user-defined exceptions.
● BEGIN section: Contains the main program logic.
● EXCEPTION section: Handles exceptions raised during program execution.
Handling Exceptions
● WHEN clause: Specifies the exception to be handled.
● EXCEPTION handling code: Code executed when the specified exception occurs.
● OTHERS clause: Handles any unhandled exceptions.
Raising Exceptions
● RAISE statement: Explicitly raises an exception.
● DBMS_STANDARD.RAISE_APPLICATION_ERROR: Raises an application error with
an error code and message.
-------------------------------------------------------------------------------------------------------
Driving License : Age can not be negative or more than 80 Years
declare
age number;
wrong_entry EXCEPTION;
begin
age:=&age;
if age > 18 and age < 80 then
dbms_output.put_line('license malshe');
else if age < 17 and age >0 then
dbms_output.put_line('nahi male');
else
--dbms_output.put_line('wrong entry');
RAISE wrong_entry;
end if;
end if;
-- Exception code
EXCEPTION
when wrong_entry then
dbms_output.put_line('age can not be in minus or more than 80');
when others then
dbms_output.put_line('unknown error');
end;
Example : Exception on the Table : Insufficient Balance :
DECLARE
v_salary NUMBER;
insufficient_balance EXCEPTION;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE emp_id = 100;
IF v_salary < 5000 THEN
RAISE insufficient_balance;
END IF;
-- Other logic
EXCEPTION
WHEN insufficient_balance THEN
DBMS_OUTPUT.PUT_LINE('Insufficient balance.');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END;
-----------------------------------------------------
Example : Salary can on be Negative : on Table :
Declare
V_salary number;
Wrong_entry Exception;
Begin
Select salary into v_salary from emp where eno=3;
If v_salary < 0 then
RAISE Wrong_entry;
End if;
EXCEPTION
when Wrong_entry Then
dbms_output.put_line(‘Salary can NOT be in Minus Number’);
End;
--------------------------------------------------
Built In Exception Examples :
1) ZERO_DIVIDE
A zero division exception occurs in PL/SQL when you attempt to divide a number by
zero. This is an arithmetic error that can lead to unexpected results or program
termination.
Example:
DECLARE
num1 NUMBER := 10;
num2 NUMBER := 0;
result NUMBER;
division_by_zero EXCEPTION;
BEGIN
IF num2 = 0 THEN
RAISE division_by_zero;
END IF;
result := num1 / num2;
DBMS_OUTPUT.PUT_LINE('Result: ' || result);
EXCEPTION
WHEN division_by_zero THEN
DBMS_OUTPUT.PUT_LINE('Cannot divide by zero.');
END;
2) PL/SQL NO_DATA_FOUND Exception
The NO_DATA_FOUND exception is raised in PL/SQL when a SELECT INTO
statement attempts to retrieve a row from a table, but no row is found. This
exception is often used to handle situations where a specific record might not
exist.
Example:
DECLARE
employee_name VARCHAR2(50);
BEGIN
SELECT last_name
INTO employee_name
FROM employees
WHERE employee_id = 12345;
-- Process the employee name
DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee with ID 12345 not found.
------------------------------------------------------------------------------------
3) Value_ERROR :
DECLARE
number_var NUMBER;
string_var VARCHAR2(10) := '123abc'; -- Invalid number format
BEGIN
number_var := TO_NUMBER(string_var);
DBMS_OUTPUT.PUT_LINE('The converted number is: ' || number_var);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error: Invalid number format.');
END;
4) Too_many_Values
DECLARE
single_value NUMBER;
BEGIN
SELECT MAX(column_name) INTO single_value
FROM table_name;
-- If multiple rows have the maximum value, TOO_MANY_ROWS exception is
raised
DBMS_OUTPUT.PUT_LINE(single_value);
END;
Cursor
Cursors in PL/SQL are used to process sets of rows returned from a SELECT
statement. They provide a mechanism to fetch rows one by one, allowing for
more granular control over data processing.
Types of Cursors:
1. Implicit Cursors:
○ Automatically declared by the compiler for DML statements (INSERT,
UPDATE, DELETE).
○ Used to access the number of rows affected by the DML operation.
2. Explicit Cursors:
○ Manually declared and opened by the programmer.
○ Offer greater flexibility and control over data processing.
Steps to Use an Explicit Cursor:
1. Declaration:
○ Declare the cursor using the CURSOR keyword, specifying the
SELECT statement that defines the result set.
○ Optionally declare variables to store fetched values.
2. Opening:
○ Open the cursor using the OPEN statement to execute the SELECT
statement and make the result set available.
3. Fetching:
○ Fetch rows from the cursor using the FETCH statement into
declared variables.
○ The INTO clause specifies the variables where the fetched values
will be stored.
4. Closing:
○ Close the cursor using the CLOSE statement to release resources
associated with the cursor.
Syntax:
Declaring an Explicit Cursor:
CURSOR cursor_name IS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Opening, Fetching, and Closing a Cursor:
BEGIN
OPEN cursor_name; -- Open the cursor
FETCH cursor_name INTO var1, var2, ...; -- Fetch data into variables
CLOSE cursor_name; -- Close the cursor
END;
Cursor Loop:
You can loop through all the rows returned by a cursor using a loop:
BEGIN
OPEN cursor_name;
LOOP
FETCH cursor_name INTO var1, var2, ...;
EXIT WHEN cursor_name%NOTFOUND;
-- Process data
END LOOP;
CLOSE cursor_name;
END;
Example :
DECLARE
CURSOR employee_cursor IS
SELECT employee_id, first_name FROM employees;
v_employee_id employees.employee_id%TYPE;
v_first_name employees.first_name%TYPE;
BEGIN
OPEN employee_cursor;
LOOP
FETCH employee_cursor INTO v_employee_id, v_first_name;
EXIT WHEN employee_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' ||
v_first_name);
END LOOP;
CLOSE employee_cursor;
END;
Example on Customer Table :
DECLARE
id customers.c_id%type;
name customers.c_name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into id,name,c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(id || ' '|| c_addr);
END LOOP;
CLOSE c_customers;
END;
Key Attributes of Cursors:
● %ROWTYPE: Used to declare a record type that matches the structure of
the cursor's result set.
● %NOTFOUND: A Boolean attribute that indicates whether the last fetch was
successful.
● %ROWCOUNT: An integer attribute that returns the number of rows fetched
so far.
Example on Student Table :
Here’s an example of using an explicit cursor in PL/SQL to work with a students
table. Let's assume the students table has the following columns: student_id,
first_name, and last_name.
DECLARE
-- Declaring an explicit cursor
CURSOR student_cursor IS
SELECT student_id, first_name, last_name FROM students;
-- Variables to store data fetched by the cursor
v_student_id students.student_id%TYPE;
v_first_name students.first_name%TYPE;
v_last_name students.last_name%TYPE;
BEGIN
-- Opening the cursor
OPEN student_cursor;
-- Fetching rows from the cursor one by one
LOOP
FETCH student_cursor INTO v_student_id, v_first_name, v_last_name;
-- Exit loop when no more rows are fetched
EXIT WHEN student_cursor%NOTFOUND;
-- Display the fetched data
DBMS_OUTPUT.PUT_LINE('Student ID: ' || v_student_id ||
', Name: ' || v_first_name || ' ' || v_last_name);
END LOOP;
-- Closing the cursor
CLOSE student_cursor;
END;
