PL SQL Exceptions

PL/SQL Exceptions

While the PL/SQL parser can analyze the syntax of your PL/SQL program when you compile it, there is no guarantee that the database with which it interacts will even be up and running later when the PL/SQL program executes or that the database objects that are names within your PL/SQL program still exist, let alone reflect the structure that your program requires.

In order to handle these unpredictable situations, PL/SQL provides a mechanism known as exceptions. When an exception occurs in the course of your PL/SQL program unit's execution, the exception is said to be raised. After the exception is raised, execution within the particular block terminates. Any open cursors are automatically closed, and any loops are exited. Execution control leaves the processing section of the block.

Furthermore, when execution exits the block due to a raised exception, control will pass into the exception-handling section of the block, if such a section has been included with the block.

The exception-handling section is an optional section in which you declare exception handlers. An exception-handler is a section of the PL/SQL code that is included with the PL/SQL block, and that will only execute if and when the associated exception is raised within the processing section.

Types of PL/SQL Exceptions
There are two general categories of exceptions:

System-defined exceptions
User-defined exceptions [Via: Oracle Ocp Dvlpr Pl/Sql Prgrm Units W/Cd By O'Hearn]

While writing a PL/SQL program:
- Always add exception handler in a PL/SQL program.

- Always try to use the named exceptions, such as NO_DATA_FOUND, TOO_MANY_ROWS rather than using WHEN OTHERS in exception handler.


An Example of PL/SQL Exception

DECLARE
v_student_id Number := &sv_student_id;
v_enrolled VARCHAR2(3) := 'NO;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Check if the student is enrolled');
SELECT 'YES'
INTO v_enrolled
FROM enrollment
Where student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE ('The student is enrolled into one course');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('The student is not enrolled');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('The student is enrolled in too many courses');
END;

The above example contains two exceptions in a single exception-handling section. The first exception, NO_DATA_FOUND, is raised if there are no records in the ENROLLMENT table for a particular student. The second exception TOO_MANY_ROWS is raised if a particular student is enrolled in more than one course.

Built-in exceptions are raised implicitly. Therefore, you only need to specify what action must be taken in the case of a particular exception.

[Via: Oracle Pl/Sql By Example, 4/E By Rosenzweig]

Few useful links on PL/SQL Exception
1. A useful link on Handling PL/SQL Errors
It mainly covers:

Overview of PL/SQL Runtime Error Handling
Advantages of PL/SQL Exceptions
Summary of Predefined PL/SQL Exceptions
Defining Your Own PL/SQL Exceptions
How PL/SQL Exceptions Are Raised
How PL/SQL Exceptions Propagate
Reraising a PL/SQL Exception
Handling Raised PL/SQL Exceptions
Tips for Handling PL/SQL Errors
Overview of PL/SQL Compile-Time Warnings

2. Recommended book for understanding PL/SQL exceptions in-depth is Oracle Pl/Sql By Example, 4/E By Rosenzweig