asked on

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

Sean Stuber

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;

Sean Stuber

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.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

toooki

ASKER

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?