exception handling in nested pl/sql blocks

[Richard Pascual]

Had a quick conceptual question for the group:

If I had a nested PL/SQL block, I am not sure what will happen with the exception handling. The example code is:

declare
   ... program declarations here ...
begin
   ...some code here...

   if (condition) then
   -- inner pl/sql block begins here
   begin
      ... do something here ...
   -- inner exception block
   exception
      when others then
      ... exception handling ...
   end;
   end if;
   ... some more code here ...

exception
   when others then
   ... exception handling ...
end;


The reason for the nested begin-end block is because I wanted to encapsulate the block of code that corresponds to when (condition) is TRUE. What I am not sure of is the effect of my decision to do so. If the inner block fails, and executes the inner exception block, I am not sure what happens next:

(1) does the pl/sql block continue executing after the inner "end" statement?
(2) does the pl/sql block continue automatically to the outer exception clause?

I am in the process of testing my theories but thought I'd also check in with the group to see if there is someone out there who has already encountered this situation before.

Thanks!

Rich Pascual

[Richard Pascual]

Found it:

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm

In summary:

(1) if the inner pl/sql block fails, then it looks for an exception block to follow. If there is no exception block found, then it exits to the outer pl/sql block and tries to find a matching exception condition in the outer block.

(2) if the inner pl/sql block fails, AND it finds a exception block and condition to match the error in the inner block, then it executes the exception instructions and exits out of the inner block... but continues to execute the code from the outer pl/sql block.

(3) if you want the entire block of pl/sql code to exit out if the inner block fails, then use the RAISE command at the end of your exception handling. The RAISE command re-raises the pl/sql error against the outer exception block and skips the remaining code in the outer pl/sql block.

If you are totally confused with the "inner" and "outer" references I have made, check out the link at the beginning of this post and look at the diagrams at the bottom of the page. They are very clear and explain the concepts well.

... good stuff ... i guess i answered my own question. hopefully the rest of the group finds this informative.

Rich Pascual

[Michael Moore]

Not really addressing your question but it's a nice idea to label inner blocks like this:

DECLARE

   x   NUMBER;

BEGIN

   NULL;

   IF x = 1

   THEN

     -- inner pl/sql block begins here

     <<error_trap>>

      BEGIN

         NULL;

      -- inner exception block

      EXCEPTION

         WHEN OTHERS

         THEN

            NULL;

      END error_trap;

   END IF;

   NULL;

EXCEPTION

   WHEN OTHERS

   THEN

      NULL;

END;

[Gopakumar Pandarikkal]

As the exception is definitely handled by the "when others" execution will continue after the end of inner block end. In situations when the inner exception handler cannot handle the exception, the exception directly goes to the outer block exception handler,there also it is not matching with any exception handling condition,it is escalated further. If nothing is handli.g the exception it goes the front end tool which called the procedure(may be java application) This is what they call propagation of exception or the bubble effect.
I have not practically verified it.

regards
Gopa

--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

[Richard Pascual]

Michael: Thanks for the tip. I like the practice of naming nested pl/sql blocks as it eases readability... <<response>>a most definitely useful approach; end response;

Gopa: You may not have seen my follow up post to my original question but it seems that your findings or understanding of this "bubble effect" confirms what I discovered in the Oracle documentation. Thanks for your reply.
 
Rich Pascual

[Gopakumar Pandarikkal]

Pl sql blocks improves readability of course. More over when you insert new code in an existing procedire,it is better to have a declare begin end block. Because the variables you declare here will be local and will not conflict with the variables with the same names n the main program .

regards
Gopa

On Nov 30, 2010 9:51 AM, "Richard Pascual" <rich...@gmail.com> wrote:

Michael: Thanks for the tip. I like the practice of naming nested pl/sql blocks as it eases readability... <<response>>a most definitely useful approach; end response;

Gopa: You may not have seen my follow up post to my original question but it seems that your findings or understanding of this "bubble effect" confirms what I discovered in the Oracle documentation. Thanks for your reply.
 
Rich Pascual

On Mon, Nov 29, 2010 at 7:38 PM, Gopakumar Pandarikkal <panda...@gmail.com> wrote:
>

> As the ...