?
Solved

ROLLBACK till SAVEPOINT in nested loops

Posted on 2006-11-07
5
Medium Priority
?
4,446 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 750 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

770 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