Home » SQL & PL/SQL » SQL & PL/SQL » bull collect loop syntax
bull collect loop syntax [message #244448] Tue, 12 June 2007 21:43 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
I am using the bulk collect syntax exactly same as in page
http://www.orafaq.com/node/1399

Looks like there is a problem with syntax in this example. When I execute and when it reached the following line, it goes out of loop. However the cursor select did have 4 rows.
EXIT WHEN empl_cur%NOTFOUND;

Anyone know what is the problem with the this syntax ?

DECLARE
    CURSOR empl_cur IS
    SELECT ename
    FROM   emp;

    TYPE empl_tab_typ IS TABLE OF EMPL_CUR%ROWTYPE;
    empl_tab EMPL_TAB_TYPE;
BEGIN
    OPEN empl_cur;
    LOOP
        FETCH empl_cur BULK COLLECT INTO empl_tab LIMIT 1000;
        EXIT WHEN empl_cur%NOTFOUND;

        FOR i IN empl_tab.FIRST .. empl_tab.LAST LOOP
            <... some statements ...>
        END LOOP;
    END LOOP;
    CLOSE empl_cur;
END;


Thanks
Yog
Re: bull collect loop syntax [message #244449 is a reply to message #244448] Tue, 12 June 2007 21:56 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
Also what does the LIMIT 1000 mean ?

If the cursor retuens 2500 rows does it mean it will loop 3 times and execute the firet 1000, second 1000, third 1000 etc ?

(OR) Will it execute only 1000 records ?

Thanks
Yog
Re: bull collect loop syntax [message #244450 is a reply to message #244448] Tue, 12 June 2007 22:09 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Sorry about that - untested code.

The EXIT WHEN statement needs to go below the FOR loop, and you need to check %ROWCOUNT for empty fetches.

Something like:
DECLARE
    CURSOR empl_cur IS
    SELECT dummy
    FROM   dual;
    
    TYPE empl_tab_typ IS TABLE OF EMPL_CUR%ROWTYPE;
    empl_tab empl_tab_typ;
BEGIN
    OPEN empl_cur;
    LOOP
        FETCH empl_cur BULK COLLECT INTO empl_tab LIMIT 1000;
        EXIT WHEN empl_cur%ROWCOUNT = 0;
        FOR i IN empl_tab.FIRST .. empl_tab.LAST LOOP
            dbms_output.put_line('Found one');
        END LOOP;
        exit when empl_cur%NOTFOUND;
    END LOOP;
    CLOSE empl_cur; 
END;

I'll fix the article when I get a moment.

LIMIT 1000 means that it will fetch no more than 1000 rows at a time. The first fetch will return 1-1000, the secon returns 1001-2000, etc.

Ross Leishman
Previous Topic: TRUNC(SYSDATE,'mm')-1 and appending a time
Next Topic: update statement
Goto Forum:
  


Current Time: Sun Dec 11 00:32:01 CST 2016

Total time taken to generate the page: 0.07701 seconds