Exception handling

An Exception is an error or warning situation, which occurs during program Execution. Ideally these situations should not occur in program.

The exceptions can be defined as follows:
•System-defined or pre-defined exception
•User-defined exception

Syntax:
DECLARE
Declaration section;
BEGIN
Executable statements ;
EXCEPTION
WHEN ex_name1 THEN
Error handling statements;
WHEN ex_name2 THEN
Error handling statements ;
WHEN Others THEN
Error handling statements ;
END;

•System-defined or pre-defined exception raised automatically by the oracle runtime engine whenever it detects an error condition , also known as NAMED SYSTEM EXCEPTION
•Unexpected Oracle errors can be Handled using OTHERS handler,also known as UNNAMED SYSTEM EXCEPTION
•Predefined exception handlers are declared globally in package STANDARD.
•Some of the pre-defined Exceptions are as follows:
1.CURSOR_ALREADY_OPEN
2.INVALID_CURSOR
3.TOO_MANY_ROWS
4.DUP_VAL_ON_INDEX

Handling Un-named Exception
•There are two ways to handle unnamed system exceptions:
     1.WHEN OTHERS clause
     2.Associate the System exception code to a name and use it as a named exception
•We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT.
•EXCEPTION_INIT will associate a predefined Oracle error number to a programmer defined exception name

Steps to be followed to use unnamed system exceptions are
1.They are raised implicitly.
2.If it is not handled in WHEN Others, it must be handled explicitly.
3.To handle the exception explicitly, Pragma EXCEPTION_INIT must be used.

Uses of EXCEPTION_INIT

•Giving Names to otherwise system anonymous exceptions.
•Assigning names to the application specific errors you raise using RAISE_APPLICATION_ERROR, this allows you to handle such errors by name, rather than simply by number.


Raising Exceptions:

There are 3 ways that an Exception may be raised in Your Application:
1.Implicitly by Oracle
2.By Using RAISE Statement
3.Using RAISE_APPLICATION_ERROR built-in procedure

RAISE statement

Following are the forms of RAISE Statement:
1.RAISE exception_name;
2.RAISE package_name.exception_name;
3.RAISE;


Scope of Exception Handling
–Once an exception is raised in a block, that block's executable section closes. But you get to decide what constitutes a block
- If an exception propagates out of the outermost block, then that exception goes un handled