?
Solved

ROLLBACK till SAVEPOINT in nested loops

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses
Course of the Month9 days, 15 hours left to enroll

569 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