Guest

• Mar 18th, 2006

 

Can u elaborate ur question, exactly what u want ?

We use exceptions to check the status of our procs .. like what to do if the things are not going smoothly as expected ... ie we insert errors in audit tables with user,date-time,err code..

praveen.pinisetti

 Profile Answers by praveen.pinisetti 

• Apr 6th, 2006

 

There is a lot to explain the execution of exceptions.

Whenever error is occured,the corresponding exception will be raised and handled in Exception block.

But in the case of nested blocks,The exception raised will be caught by handler of Inner block.If inner block does not consist of Handler,It will be propagated to Next outer block and  so on.

If There is an error in declaration section and also in Exception section,That also be propagated to next outer block.

ashishdixit

 Profile Answers by ashishdixit 

• May 28th, 2008

 

Exception is equivalent of an error in the code.
This may be due to incorrect data or wrong coding techniques. What ever the reason may be,when an error occurs in the plsql code, the command terminates after searching for an EXCEPTION HANDLER. If there is an exception handler defined then control gets transferred to the handler and the logic implemented thereafter is executed.
Points to remember:
Control never goes back to the block where the exception has occured. It propogates forward, and looks for an exception handler.
In absence of an exception handler, the control goes to the end and program gets terminated.
a simple scenario is described below
select <somevalue> INTO <somevariable> from <sometable>
where <some filter condition>;

let us assume that the above query does not fetch anything.
In this case the error is a predefined exception (no_data_found). If you have this stt written inside your block and there is no exception handler defined then the program terminates and anything written after this does not get executed.

how to handle this situation:
add another block EXCEPTION (after the end of BEGIN and before you write END;)
EXCEPTION
when no_Data_found
then
do something
END;

in this case the control jumps from the BEGIN block and looks for a handler.
Now we have declared a handler(no_Data_found), so the control comes over to this block and does what ever has been specified.
after this it comes to the END; (or moves to the outer block if declared, in this case none) and terminates.

Point to remember: there is no way to transfer control back to the block where it has occurred. not even using a (less used) GOTO and LABEL structure.
To continue execution the work around is using nested begin blocks and ensuring an exception handler for each exception is defined in the same block.

Raise exception
sqlerrmsg
sqlerrcode
pragma exception_init
are some other topics that may be touched for a clearer understanding