exe

EXCEPTION HANDLING

What is an exception?

How to handle exceptions?

 Predefined exceptions
When NO_DATA_FOUND exception is not raised?
User-defined exception
Reraising an exception
Associating an exception With An Oracle Error
Exception propagation
When is a PL/SQL block successful or failure?

What is an Exception?

In PL/SQL, errors and warnings are called as exceptions. Whenever a predefined error occurs
in the program, PL/SQL raises an exception. For example, if you try to divide a number by
zero then PL/SQL raises an exception called ZERO_DIVIDE and if SELECT can not find a record
then PL/SQL raises exception NO_DATA_FOUND.
PL/SQL has a collection of predefined exceptions. Each exception has a name. These
exceptions are automatically raised by PL/SQL whenever the corresponding error occurs.
In addition to PL/SQL predefined exceptions, user can also create his own exceptions to deal
with errors in the applications. Understanding how to handle exception raised by PL/SQL is as
important as understanding how to write code to achieve task. Because exception handling is
an important part of any application and application is not complete without exception
handling.

How to handle exceptions?

When PL/SQL raises a predefined exception, the program is aborted by displaying error
message. But if program is to handle exception raised by PL/SQL then we have to use
Exception Handling part of the block.
Exception handling part is used to specify the statements to be executed when an
exception occurs. Control is transferred to exception handling part whenever an
exception occurs. After the exception handler is executed, control is transferred to

next statement in the enclosing block. If there is no enclosing block then control
returns to Host (from where you ran the PL/SQL block).
The following is the syntax of exception handling part.
WHEN exception-1 [or exception -2] ... THEN
statements;
[WHEN exception-3 [or exception-4] ... THEN
statements; ] ...
[WHEN OTHERS THEN
statements; ]
exception-1, exception-2 are exceptions that are to be handled. These exceptions are either
pre-defined exceptions or user-defined exceptions.
The following example handles NO_DATA_FOUND exception. If SELECT statement doesn’t
retrieve any row then PL/SQL raises NO_DATA_FOUND exception, which is handled in
exception handling part.
declare
…
begin
select …
exception
when no_data_found then
statements;
end;
When two or more exceptions are given with a single WHEN then the statements are executed
whenever any of the specified exceptions occur.
The following exception handling part takes the same action when either NO_DATA_FOUND or
TOO_MANY_ROWS exceptions occur.
declare
...
begin
select ...
exception
when no_data_found or too_many_rows then
statements;
end;