Problem looping through FETCH.. BULK COLLECT.. LIMIT

Hello,
    I am having problem looping through a cursor using bulk collect.

declare
  type typVar is table of tab1.field1%type index by binary_integer
  arrayVar typVar;
  cursor curTemp is select field1 from tab1;

begin
  open curTemp;
  loop  -- outer
    FETCH curTemp BULK COLLECT into arrayVar  LIMIT 5;
    exit when curTemp%notfound;
    for i in 1..arrayVar.count loop
       /* Processing */
    end loop;
  end loop;  -- outer
end;

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.
queryanalyzerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:


try this:
...
exit when curTemp%ROWCOUNT = 0;


Harish_RajaniCommented:
Hi
Try following:
I have just changed the position of ..
check out...


declare
  type typVar is table of tab1.field1%type index by binary_integer
  arrayVar typVar;
  cursor curTemp is select field1 from tab1;

begin
  open curTemp;
  loop  -- outer
    FETCH curTemp BULK COLLECT into arrayVar  LIMIT 5;
    for i in 1..arrayVar.count loop
       /* Processing */
    end loop;
    exit when curTemp%notfound;  <--- I have just changed the position of this exit...
  end loop;  -- outer
end;

Rgds,
HR

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
queryanalyzerAuthor Commented:
Thanks guys, I had to put the 'exit' at the end.
Apperciate your time.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.