Bulk collection issue with limit

scharan07 · #1 (**permalink**) 09-01-10, 01:38

Hi,

i written a piece of code in procedure to get a bulk collection data in to collection type with limit of 5. while iterating there has only 4 values in the cursor it is not able to retrive the data for those 4 records

Code:

CURSOR CURSOR_RESULTS IS 
  SELECT ID FROM ACC WHERE ISHIDDEN=1; 
  TYPE ID_TYPE IS TABLE OF ACC.ID%TYPE;
  LIST1 ID_TYPE;
BEGIN
    OPEN CURSOR_RESULTS;
    LOOP
    DBMS_OUTPUT.PUT_LINE('CURSUR ITERATION');
    FETCH CURSOR_RESULTS BULK COLLECT INTO LIST1 LIMIT 5;
    EXIT WHEN CURSOR_RESULTS%NOTFOUND;
      WALKER:= LIST1.FIRST;
      FOR I IN 1..LIST1.COUNT LOOP
        ID:=LIST1(WALKER);
        WALKER:=LIST1.NEXT(I);
        DBMS_OUTPUT.PUT_LINE('ID ITERATION'||ID);
      END LOOP;  
    END LOOP;
    CLOSE CURSOR_RESULTS;

Quote:

my total ids present in are 14 records which are 41,42,43,44,,45,46,47,48,49,1,2,3,51

but my out put is

Quote:

CURSUR ITERATION
ID ITERATION41
ID ITERATION42
ID ITERATION43
ID ITERATION44
ID ITERATION45
CURSUR ITERATION
ID ITERATION46
ID ITERATION47
ID ITERATION48
ID ITERATION49
ID ITERATION1
CURSUR ITERATION

after last "CURSUR ITERATION" i am expecting another 4 "ID ITERATION"s which are not coming.

because of limit 5, which it has only 4 records thats why it is not get the records into collection..?

magicwand · #2 (**permalink**) 09-01-10, 08:23

scharan07,

the line

Code:

EXIT WHEN CURSOR_RESULTS%NOTFOUND;

is in the wrong place.

The data of the last fetch is in your LIST1 table, but doesn't get printed, because the NOTFOUND is raised before the output - loop.

If you program it

Code:

BEGIN
    OPEN CURSOR_RESULTS;
    LOOP
      DBMS_OUTPUT.PUT_LINE('CURSUR ITERATION');
      FETCH CURSOR_RESULTS BULK COLLECT INTO LIST1 LIMIT 5;
      WALKER:= LIST1.FIRST;
      FOR I IN 1..LIST1.COUNT LOOP
        ID:=LIST1(WALKER);
        WALKER:=LIST1.NEXT(I);
        DBMS_OUTPUT.PUT_LINE('ID ITERATION'||ID);
      END LOOP;  
      EXIT WHEN CURSOR_RESULTS%NOTFOUND;
    END LOOP;
    CLOSE CURSOR_RESULTS;
END;

you will get the expected results.

(As always when no version is mentioned, I assume the latest, which is 11gR2 at the moment)

scharan07 · #3 (**permalink**) 09-02-10, 00:52

Thanks magicwand it is working fine.

i am using oracle 10g version.

magicwand · #4 (**permalink**) 09-02-10, 08:25

scharan07,

one more thing:

You should enclose the FOR - loop in an IF statement to avoid an error if <#of output lines> mod 5 = 0;

I'd suggest:

Code:

      ...
      FETCH CURSOR_RESULTS BULK COLLECT INTO LIST1 LIMIT 5;
      IF LIST1.COUNT > 0 THEN
         WALKER:= LIST1.FIRST;
         FOR I IN 1..LIST1.COUNT LOOP
           ID:=LIST1(WALKER);
           WALKER:=LIST1.NEXT(I);
           DBMS_OUTPUT.PUT_LINE('ID ITERATION'||ID);
         END LOOP;  
      END IF;
      EXIT WHEN CURSOR_RESULTS%NOTFOUND;
      ...

scharan07 · #5 (**permalink**) 09-03-10, 01:24

Yes. sure i got your point i will include that into my code

Thanks once again magicwand.

Internet Services

Web Development

Digital Marketing

Consumer Tech