Hi Tom,
question regarding when does the collection element gets delete during fetch using bulk collect.

count in emp is 14. The eg code is as below:

set serveroutput on;
declare
type emp_type is table of emp%rowtype index by pls_integer;
lv_emp_type emp_type;
cursor c1 is select * from emp;
begin
open c1;
loop
fetch c1 bulk collect into lv_emp_type limit 1;
exit when lv_emp_type.count=0;
dbms_output.put_line('count in the collection::::'||lv_emp_type.count);
-----lv_emp_type.delete;
end loop;
end;

Note : lv_emp_type.delete is commented. The result of the above code is :

count in the collection::::1
count in the collection::::1
count in the collection::::1
count in the collection::::1
count in the collection::::1
count in the collection::::1
count in the collection::::1
count in the collection::::1
count in the collection::::1
count in the collection::::1
count in the collection::::1
count in the collection::::1
count in the collection::::1
count in the collection::::1

Since the line lv_emp_type.delete is commented, the count in the collection should have increased in every iteration as 1,2,3,4,5,6......14. But the collection element is getting deleted somehow? where does this delete happen?
I was always being told befeore the next loop delete the collection elements manually, but here its happening automatically. Please tell me more on this.

loop
   fetch c1 bulk collect ...
   .... process any data found here ...
  
   EXIT WHEN C1%NOTFOUND;
end loop;