• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Oracle Exception question

I have an Oracle (11gR2) Package MyPack1  that has a procedure Cal_All and this calls 4 different independent procedures.
I have put the sample content of the procedure MyPack1.

I wanted to know:
-- I want  MyPack1 to exit the procedure immediately if any EXCEPTION happens. Do I use "Rollback;" or "Exit" under the procedure MyPack1 ? In both situations: when I need to rollback and when I do not need to rollback..?

-- Also in the  package MyPack1 , how do I ensure that if MyProc1 got some EXCEPTION, it exits MyPack1 immediately and does not go to MyProc2. And likewise if MyProc2 got some EXCEPTION, it does not execute MyProc3 and so on..

CREATE OR REPLACE PACKAGE BODY MyPack1 IS
  PROCEDURE Cal_All AS

  BEGIN
    MyProc1;

    MyProc2;

    MyProc3;

    MyProc4;

  END;
END;

create or replace procedure MyProc1
is
begin
... do some stuff

    begin
       -- your update
       exception when others then
          ...--I want to EXIT;
    end;


 ... do more stuff

    begin
        ...
    end;


end;

Open in new window

0
toooki
Asked:
toooki
  • 3
1 Solution
 
sdstuberCommented:
begin
       -- your update
       exception when others then
          -- I want to EXIT;
        RAISE;   -----   just this one word RAISE, and it will re-raise your exception to the calling procedure
    end;
0
 
sdstuberCommented:
by allowing each procedure to reraise it's exceptions you can maintain the error stack to any depth.

if you don't reraise, you'll have to return a code or set some global to indicate what you want to happen after each procedure ends.
0
 
sdstuberCommented:
as for rollback,  if you should or should not depends on the exception was and the transaction you are processing when the exception occurs.

some transactions are safe to commit midway through (a purge process for instance, you mightaccept partial delete and then continue later to purge the rest after correcting the error)

also, some exceptions aren't really errors, - no data found while processing to end of file is expected that's when you know you're done


by default,  if you don't commit or rollback the transaction will be left pending and propagate up out of your procedure.
if you let the exception propagate all the way to a user interface, most will rollback by default.

0
 
toookiAuthor Commented:
Thank you for the explanation...It helps a lot.

Still one question to verify then:
Within MyProc1 exception  (also within  MyProc2/MyProc3/MyProc4 exceptions) I am writing:

------------
exception when others then
insert into myLogTab("something is wrong in myProc1/MyProc3/MyProc4 -- will change this line depending on which procedure I am in");
RAISE;
------------

And I keep MyPack1 as it is there in the question (without any exception code in it). This will cause MyPack1 not to execute MyProc2 if exception block was called inside MyProc1. Likewise MyPack1 will not execute MyProc3 if exception block was called inside MyProc1 or MyProc2. And so on..right?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now