Re: COMMIT in for loop

amyleone_at_my-deja.com wrote:
> In article <376AA2B5.B5A1CED5_at_lucent.com>,
> Kenneth C Stahl <kstahl_at_lucent.com> wrote:
> > It may be a rollback segment problem.

> You must be right because when I try it again I get:

> ERROR at line 1:
> ORA-01555: snapshot too old: rollback segment number 8 with name
> "RBSLRG07" too
> small

The problem you are encountering is caused by fetching from a cursor after doing a commit. When you perform a commit, you should close and re-open all cursors. Oracle's problem report number 1005107.6 describes the situation rather completely.

This has some implications on how you write your code. Supposing that you are performing some logic on each record returned by a particular cursor; to resolve the problem, you should find some way of breaking the records returned by the cursor into chunks using cursor parameters, and then do a "commit" after each chunk.

That is, if your current process has a cursor:

cursor my_big_wad_of_records
is
select foo, bar, baz
from record_source;

and "foo" is an indexed number column, for example, with values between 1 and 50000, you could have:

cursor piece_of_the_big_wad (low integer, high integer) is
select foo, bar, baz
from record_source
where foo between low and high;

and then loop through the cursor ten times, starting with low := 1 and high := 5000; and successively setting low := high + 1 and high := low + 5000.

-bn
sacq_at_usa.net Received on Tue Jun 22 1999 - 18:41:42 CDT