Problem looping through FETCH.. BULK COLLECT.. LIMIT
Posted on 2006-03-31
I am having problem looping through a cursor using bulk collect.
type typVar is table of tab1.field1%type index by binary_integer
cursor curTemp is select field1 from tab1;
loop -- outer
FETCH curTemp BULK COLLECT into arrayVar LIMIT 5;
exit when curTemp%notfound;
for i in 1..arrayVar.count loop
/* Processing */
end loop; -- outer
Here is what seems to be happening. If there are 13 records in the 'tab1' table, only 10 (5+5) gets processed. The remaining 3 is leftout.
It looks like the 'limit 5' has something to do with it.
I assumed when FETCH is retrieving the last set of records, it would get whats left in the table, and that number need not be equal to the what LIMIT is set to.
In my case it is getting the first 5, processing it, then the next 5 and when it gets to the last 3 it is exiting from the loop.
Is this the normal behavior. Is so, how can i got around this problem.
I do need to limit the number of records fetched each time.
Thanks for your time.