• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 912
  • Last Modified:

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.
0
queryanalyzer
Asked:
queryanalyzer
1 Solution
 
MikeOM_DBACommented:


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


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

0
 
queryanalyzerAuthor Commented:
Thanks guys, I had to put the 'exit' at the end.
Apperciate your time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now