Solved

ROLLBACK till SAVEPOINT in nested loops

Posted on 2006-11-07
5
3,948 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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

757 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now