Home » SQL & PL/SQL » SQL & PL/SQL » control exceptions
control exceptions [message #241958] Thu, 31 May 2007 03:48 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
how to control exceptions when a stored proc calls multiple stored procs and also has various loops.


CREATE PROCEDURE PROC_MAIN AS 
BEGIN

DECLARE
 cur_member IS
 SELECT * FROM TABLE1
 FOR UPDATE NOWAIT;
BEGIN

  FOR recMember IN cur_member 
    
    execute PROC1;

    execute PROC2;

    execute PROC3; 

   UPDATE TABLE
   WHERE CURRENT OF cur_member 
  END LOOP;

EXCEPTION
WHEN OTHERS THEN
  => Should the exception be raised
END;
END;
/




CREATE PROCEDURE PROC1 AS 
BEGIN

BEGIN

    insert into table2    

    EXCEPTION
       WHEN OTHERS THEN
       => Should the exception be raised
    END;
END;
/



Ignore the exceptions like NO_DATA_FOUND and etc etc. Its understood they must be handled.

My question is when there are multiple procs involved, should we raise the exception to throw an error (for any technical failure) ? If so which proc should it be raised. Can't find an example which explains how to handle exceptions for multiple procedures that run together as batch.

Thanks
Yog
Re: control exceptions [message #241961 is a reply to message #241958] Thu, 31 May 2007 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
WHEN OTHERS THEN
=> Should the exception be raised

Yes, for all exception that you don't want to ignore.
You can use:
CREATE PROCEDURE PROC1 AS 
BEGIN
 ...
  BEGIN
    insert into table2 SELECT ...
  EXCEPTION
    WHEN NO_DATA_FOUND THEN NULL;
    WHEN OTHERS THEN 
      <do something, if nothing has to be done don't use WHEN OTHERS>
      RAISE;
  END;
...
END;
/

Regards
Michel
Re: control exceptions [message #241970 is a reply to message #241961] Thu, 31 May 2007 04:23 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
thank you Michel.

If the exception is raised in the PROC1 as shown, where does the control gets transferred in the MAIN_PROC .

To the next statement or the EXCEPTION Block.

Also, If we add smaller blocks of BEGIN...EXCEPTION END, and do a raise does the control gets transferred to main block exception ?

e.g.

CREATE PROCEDURE PROC_MAIN AS 
BEGIN

DECLARE
 cur_member IS
 SELECT * FROM TABLE1;

BEGIN

  FOR recMember IN cur_member 
    
    begin
    execute PROC1;
    
    exception
    when others 
      raise;       
--Once exception is raised does it exit the loop and go to the end of program EXCEPTION block (at the bottom of the proc).    
    end;
    execute PROC2;

    execute PROC3;  
  END LOOP;

EXCEPTION
WHEN OTHERS THEN
  => Should the exception be raised
END;
END;
/

Re: control exceptions [message #241973 is a reply to message #241970] Thu, 31 May 2007 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Each time and as soon as an exception is raised the control is passed to the exception block of the current block or the first outer block if there is no exception part in current block or if exception is raised inside this exception part.

Regards
Michel
Re: control exceptions [message #242038 is a reply to message #241973] Thu, 31 May 2007 08:21 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Only catch exceptions in the outer most procedure. Exception handling should (ideally) not be done in subordinate procedures, except for expected, non-erroneous exceptions, such as a no_data_found for a procedure that should not fail if nothing is found.
Previous Topic: How many Subqueries can i write?
Next Topic: Functions problem in 9i
Goto Forum:
  


Current Time: Sat Dec 03 01:11:30 CST 2016

Total time taken to generate the page: 0.07826 seconds