Oracle 10g Exceptions

Oracle Concepts

Selvaraj V Anna University Chennai. Chennai – 25.

Oracle Exceptions - Oracle Defined and User defined 1.

What are exceptions in Oracle?

Exceptions are the ways in which Oracle could handle runtime errors in a PL/SQL block. Exceptions are always useful to debug the code as well as ensure a smooth end for a Program without interupting the calling Program/Block. 2.

What are the different types of Oracle Exceptions? The main classification is Oracle Defined and User defined Exceptions Oracle defined: There are many built in exceptions which we can use. Most commonly used ones are: a. b. c. d. e. f. g. h.

NO_DATA_FOUND TOO_MANY_ROWS_FOUND VALUE_ERROR ZERO_DIVIDE INVALID_NUMBER DUP_VALUE_ON_INDEX CURSOR_ALREADY_OPEN INVALID_CURSOR

User Defined: User defined exceptions are defined in many ways. a. New user defined Exceptions can be created using existing Oracle defined Exceptions. b. Entirely new Exceptions can be according to a users need. Eg: Raise an exception if employee salary entered is less than 0. There are mainly 3 ways to use User Defined Exceptions

A. EXCEPTION declare exc_user Exception; --declare exception begin begin --code-exception when others then raise exc_user; exception raised exception when exc_user --handler for exc_user. exception handled when others then --handler for others end;

--

B. PRAGMA EXCEPTION_INIT declare exc_user Exception; --declare exception PRAGMA EXCEPTION_INIT(exc_user, -); -- Oracle code with '-'sign begin --code-exception when exc_user --handler for exc_user --handled when error occurs with the specified Oracle Code when others then --handler for others C. RAISE_APPLICATION_ERROR declare exc_user Exception; exception begin if () then

--declare

RAISE_APPLICATION_ERROR(-20001, exc_user); --code greater than -20000 exception when exc_user then --handler for exc_user-- --handled when error occurs with the specified Oracle Code when others then --handler for others 3. What is Exception propagation in Oracle? Exceptions which are not handled in a sub block gets propagated to the outer block. When an exception occurs, it terminates from the line where the exception occurs and the control goes to the calling program or the next outer block. If not handled in the outer block, it terminates that block and propagates to the next outer block and so on. And, if exceptionoccurs in the outermost block, then the whole program gets terminated.

4.

An inner block has got an exception with the same name as in the outer block. The exception doesnot have any handler defined in the inner block, but has got a handler defined in the outer block. Will the Exception get handled assuming that when others exception handler is not defined.

Eg: Declare A Exception ; Begin Declare A Exception; name decalare in inner block Begin if 1=1 then raise A; end if; end; A in inner block exception when A then

--Exception with same

--No handler for exception

--handler-- End; Answer: The exception A in the inner block WILL get propagated to the outer block but WILL NOT get handled in the outer block. Oracle considers both Exceptions to be extremely different, even if they have the same name.