We help IT Professionals succeed at work.

ROLLBACK till SAVEPOINT in nested loops

RimmaC
RimmaC asked
on
Medium Priority
5,776 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


Comment
Watch Question

Top Expert 2006
Commented:
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 :)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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
Top Expert 2006

Commented:
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 :)
Top Expert 2006

Commented:
ooops, they should ALL be "if NOT error_flag", there is one "if error_flag", this is wrong.

Author

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.