Exception Handling in Oracle PL/SQL

There are number of reasons due to which run time errors may be raised during the execution of a PL/SQL block. With PL/SQL, a mechanism called exception handling lets you “bulletproof” your program so that it can continue operating in the presence of errors.

When an error occurs, an exception is raised; normal execution is stopped and control is transferred to an exception-handling section of PL/SQL program. A specific section can be defined in the program to handle exceptions. Separate subroutines called exception handlers can be created to perform all exception processing. Once an exception is raised and control is transferred to the exception part of a program, it cannot return to the execution part of the
program.

Overview of  Exception handling  in  oracle PL/SQL

Errors are handles in two ways, one is to trap the error and other is to propagate to the calling environment.

1.Trapping an exception

If the exception is raised in the executable section of the block, processing branches to the corresponding exception handler in the exception section of the block. If PL/SQL successfully handles the exception, then the exception does not propagate to the enclosing block or environment. The
PL/SQL block terminates successfully as shown in figure

Trapping Exception

2.Propagating an Exception

If the exception is raised in the executable section of the block and there is no corresponding exception handler, the PL/SQL block terminates with failure and the exception is propagated to the calling environment

The Basic syntax for using Exceptions

Syntax
EXCEPTION
WHEN exception1 [OR exception2 ………] THEN
statement1;
statement2;
[WHEN exception3 [OR exception4 ………] THEN
statement1;
statement2;
..…………..]
[WHEN OTHERS THEN
statement1;
statement2;
……..]

Statement

It is one ore more PL/SQL or SQL statements

Others
It is an optional exception-handling clause that traps unspecified exceptions.

WHEN OTHERS Exception Handler
The exception-handling section traps only those exceptions specified; any other exceptions are not trapped unless you use the OTHERS exception handler.This traps any exception not yet handled.For this reason, OTHERS is the last exception handlerdefined. The OTHERS handler traps all exceptions not already trapped.

Some Guidelines to Trap Exception

>> Begin the exception-handling section of the block with the keyword EXCEPTION.
>> Define several exception handlers, each with its own set of
actions, for the block.
>> When an exception occurs, PL/SQL processes only one handler
before leaving the block.
>>Place the OTHERS clause after all other exception-handling
clauses.
>>You can have at most one OTHERS clause.
>> Exceptions cannot appear in assignment statements or SQL
statements.

Exception Handling Example 1

Declare

n_emp_salary number(10)=0;

BEGIN

n_emp_salary number=n_emp_salary number/10;

EXCEPTION

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE('Zero Divide Error');rong>

END;

/

Written by admin on December 21st, 2010 with comments disabled.
Read more articles on Exception Handling.

Comments disabled

Comments on this article have been disabled.