Home » SQL & PL/SQL » SQL & PL/SQL » Error handling NEED HELP!
Error handling NEED HELP! [message #38997] Wed, 05 June 2002 08:13 Go to next message
sverch
Messages: 582
Registered: December 2000
Senior Member
Hi,

I have a standalone procedure ("calling procedure") that calls about 30 another standalone procedures("called procedures"). I was asked to write a code that stops the entire process if any of these "called" procedures have a certain Oracle errors. I.e. if a "called" procedure had an error number of
such and such range then instead of simply handling an exception and moving to the next procedure the code in the "calling" procedure should stop the entire processing.
I tried to play with IN OUT parameters and with IF..THEN logic, but there is something flaky there and I cannot yet figure out what.
Any ideas?
Re: Error handling NEED HELP! [message #38998 is a reply to message #38997] Wed, 05 June 2002 09:48 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I might approach this with a loop construct that requires only a single exception handler instead of one around each proc.

begin
  for i in 1..3 loop
    begin
      if    i = 1 then
        proc1;
      elsif i = 2 then
        proc2;
      elsif i = 3 then
        proc3;
      end if;
    exception
      when no_data_found or too_many_rows then
        -- maybe log this somewhere
        exit;
    end;
  end loop;
end;


The exception handling in your called procedures would have to either not trap for or re-raise the specific "fatal" conditions. They would have to handle any other exceptions.

I would also highly recommend that you do not put a:

when others then
  null;


block anywhere in your calling or called procedures. This will only suppress errors that should be handled at some level.
Re: Error handling NEED HELP! [message #39003 is a reply to message #38997] Wed, 05 June 2002 16:48 Go to previous message
sverch
Messages: 582
Registered: December 2000
Senior Member
Todd,

This is simple and elagant!!!
And it is MUCH better than what I ended up with.

THANK YOU!
Previous Topic: I need an SQL statement for this...
Next Topic: HOW TO QUERY LOBS
Goto Forum:
  


Current Time: Wed Nov 21 02:45:31 CST 2018