Solved

ROLLBACK till SAVEPOINT in nested loops

Posted on 2006-11-07
5
4,265 Views
Last Modified: 2012-05-05
Hello,

I have a procedure that does many inserts into many different tables inside main and inner loops when new empl_id exists.
What I need to do is to rollback all the inserts (in main and inner loops) if one of them fails.

FOR i IN C1 -- Main cursor
   LOOP
     SAVEPOINT start_add;
   
     begin
     Insert into Table1;
         Exception
         when others
         rollback to start_add;
     end;
     
    begin
     Insert into Table2;
         Exception
         when others
         rollback to start_add;
     end;    

           FOR j in C2(i.empl_id) -- inner cursor
      LOOP      
                    begin
                    Insert into Table3;
                    Exception
                   when others
                   rollback to start_add;
                   end;
                   
                   Insert into Table4;
                    Exception
                   when others
                   rollback to start_add;
                   end;

                END LOOP - inner cursor
          commit; -- for one emplid
END LOOP; --main loop

the error I am getting is ORA-01002: fetch out of sequence for inner cursor

Thanks for any help


0
Comment
Question by:RimmaC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
rbrooker earned 250 total points
ID: 17893767
Hi,

if you are rolling back to the start from inside the inner cursor, then you need to exit the loop as well.
name your loop:

<<loop_name>>
FOR  j in C2(....

then:
begin
insert into table3;
exception
when others then
rollback to start_add;
exit loop_name;
end;

this will exit the loop allowing you to continue to the next employee.

good luck :)
0
 

Author Comment

by:RimmaC
ID: 17899577
don't have the error anymore, which is a good thing and I thank Rbrooker very much

the bad thing is - it is still acting strange (or maybe it is supposed to be like this, it just not what I need)

in the scenario below it fails for insert into Table 3, undo inserts for Table2 and Table 3 - BUT it still goes below for this 'bad' empl_id and inserts data in Table4 (c3 inner loop) and Table5 - last table for main cursor. Doesn't seam logical to me.


FOR i IN C1 -- Main cursor
   LOOP
     SAVEPOINT start_add;
   
     begin
     Insert into Table1;
         Exception
         when others
         rollback to start_add;
     end;
     
    begin
     Insert into Table2;
         Exception
         when others
         rollback to start_add;
     end;    
           <<c2>>
           FOR j in C2(i.empl_id) -- inner cursor
     LOOP    
                    begin
                    Insert into Table3;
                    Exception
                   when others
                   rollback to start_add;
                   exit c2;
                   end;
            END LOOP -- c2
           <<c3>>
           FOR j in C3(i.empl_id) -- inner cursor
             LOOP        
                   Insert into Table4;
                    Exception
                   when others
                   rollback to start_add;
                   exit c3;
                   end;
                END LOOP; - c3 inner cursor

      Insert into Table5;
                    Exception
                   when others
                   rollback to start_add;

          commit; -- for one emplid
END LOOP; --main loop
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17900291
Hi,

in this case, you need an error flag...

something like :

FOR i IN C1 -- Main cursor
   LOOP
     SAVEPOINT start_add;
   
     error_flag := false; <---
     begin
     Insert into Table1;
         Exception
         when others
         error_flag := true; <---
         rollback to start_add;
     end;
     
    if not error_flag then <---
    begin
     Insert into Table2;
         Exception
         when others
         error_flag := true <---
         rollback to start_add;
     end;    

           if error_flag then <---
           <<c2>>
           FOR j in C2(i.empl_id) -- inner cursor
           LOOP    
                    begin
                    Insert into Table3;
                    Exception
                   when others
                   error_flag := true; <---
                   rollback to start_add;
                   exit c2;
                   end;
            END LOOP -- c2

           if not error_flag then <---
           <<c3>>
           FOR j in C3(i.empl_id) -- inner cursor
             LOOP        
                   Insert into Table4;
                    Exception
                   when others
                   rollback to start_add;
                   error_flag := true;
                   exit c3;
                   end;
                END LOOP; - c3 inner cursor

      if not error_flag then <---
      Insert into Table5;
                    Exception
                   when others
                   rollback to start_add;

          commit; -- for one emplid
END LOOP; --main loop

using an error flag will only allow the inserts to execute if the previous insert completed successfully.
good luck :)
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17900309
ooops, they should ALL be "if NOT error_flag", there is one "if error_flag", this is wrong.
0
 

Author Comment

by:RimmaC
ID: 17902698
thank you.

or I can raise an exception in inner loop and then put an exception with the rais error at the end of the main loop with rollback
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question