Rollback Segment Too Old & Cursors Question

From: Dogan Cibiceli <dcib_at_gwl.com>
Date: Mon, 26 Apr 1999 15:40:59 -0600
Message-ID: <3724DD6B.90A5DF7@gwl.com>

Hi,
  This question was asked before but this is a continuation of that age old problem. I have a large table and I have a PL/SQL that fetchs over this table. After I am done with each row, I used to commit by changes and go on fetching another row from the same table. The table I fetch and update is the same table. I run into rollback segment too old problem since the fetch didnot like me committing. Upon advice I have written a small exception to close and open the cursor when rollback segment too old error happens. Everything worked fine and it finished till the end. Then when I did a search whether any row out there is not updated. I found one row. I believe this row is the row that rollback segment error happened and I closed opened the cursor. My question is during the next open, why is this row not included in the row set ? Should I do a rollback before closing and opening cursor ? If so why, I havenot at the time committed anything therefore I should have found out the row that the error happened. Program logic

 loop
 begin

    fetch cursor
......

   commit
 exception

    when rollback_segment_too_old then

           close cursor
           open cursor

  end
 end loop Received on Mon Apr 26 1999 - 16:40:59 CDT