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    Handling PL/SQL Errors 

      

      link

 l

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