Search code examples
oracleplsqloracle-sqldeveloper

Oracle/PLSQL Processing all data in a for LOOP after BULK COLLECT

I have a problem with processing data within the for loop after bulk collect.

OPEN my_cursor;
LOOP
FETCH my_cursor BULK COLLECT INTO var_t LIMIT 100000;
DBMS_OUTPUT.PUT_LINE('There is ' || var_t.COUNT || ' records found.');
EXIT WHEN my_cursor%NOTFOUND; 
FOR i IN var_t.FIRST .. var_t.LAST
LOOP
var_read_cnt := var_read_cnt + 1;
END LOOP;
END LOOP;
close my_cursor;
DBMS_OUTPUT.put_line('read/inserted : '|| var_read_cnt);

The output looks like this

There is 100000 records found. There is 2397 records found. manipulated : 100000

The fetch and bulk part are correct i have indeed 100000+2397 records. But i am not able to process 2397 records in the loop because i am not getting in the loop for them. What is the issue here ?

Solution

  • Your exit condition is incorrect.

    EXIT WHEN my_cursor%NOTFOUND;

    If the fetch statement fetches fewer rows than is allowed by the limit, %notfound will be true. But you still want to process the rows that you fetched. The simplest approach is to change your code to

    EXIT WHEN var_t.count = 0;

    or to move your exit condition to the end of the loop rather than the beginning.