Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problem looping through FETCH.. BULK COLLECT.. LIMIT

Posted on 2006-03-31
3
Medium Priority
?
883 Views
Last Modified: 2008-01-09
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
Comment
Question by:queryanalyzer
3 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 16346010


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


0
 
LVL 4

Accepted Solution

by:
Harish_Rajani earned 300 total points
ID: 16347684
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
 

Author Comment

by:queryanalyzer
ID: 16392243
Thanks guys, I had to put the 'exit' at the end.
Apperciate your time.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question