ORA-06502: PL/SQL: numeric or value error in the case of empty cursor

Question

I keep getting ERROR: ORA-06502: PL/SQL: numeric or value error during the second run of a procedure. Following part when run first time works as it should. Reads 150 000 records process it, manipulates.. When I run it for the second time, my_cursor is empty because all 150 000 records are processed in the first run. I would except here to get NO_DATA_FOUND but i am keep getting ERROR: ORA-06502: PL/SQL: numeric or value error.

Print line with the log after second execution-> read/inserted : 0. This is also ok because no data are left to process...

OPEN my_cursor;
LOOP
    FETCH my_cursor BULK COLLECT INTO var_t LIMIT 100000; 
    FOR i IN var_t.FIRST .. var_t.LAST  
    LOOP
    var_read_cnt := var_read_cnt + 1;
    END LOOP;
    EXIT WHEN my_cursor%NOTFOUND; 
END LOOP;
close my_cursor;
EXCEPTION
WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line('OK no data found  SQLCODE: '||SQLCODE||' -- ERROR: '||SQLERRM);
WHEN OTHERS THEN  
    DBMS_OUTPUT.put_line(' Unexcpected exception SQLCODE: '||SQLCODE||' -- ERROR: '||SQLERRM);
END;
DBMS_OUTPUT.put_line('read/inserted : '|| var_read_cnt);

Answer 1

Use this :

LOOP
    FETCH my_cursor BULK COLLECT INTO var_t LIMIT 100000; 
    IF var_t.FIRST IS NOT NULL THEN
       FOR i IN var_t.FIRST .. var_t.LAST  
       LOOP
          var_read_cnt := var_read_cnt + 1;
       END LOOP;
    END IF;
    EXIT WHEN my_cursor%NOTFOUND; 
END LOOP;

NB, an OPEN cursor ... structure will never raise an NO_DATA_FOUND exception, so you can skip that exception handler.