Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29

    Unanswered: Bulk collection issue with limit

    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;
    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
    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..?

  2. #2
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    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)
    Last edited by magicwand; 09-01-10 at 10:12. Reason: typos fixed, essential line highligted
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  3. #3
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29

    Cool

    Thanks magicwand it is working fine.

    i am using oracle 10g version.

  4. #4
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    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;
          ...
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  5. #5
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    Yes. sure i got your point i will include that into my code

    Thanks once again magicwand.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •