BULK COLLECTION IN PL/SQL

BULK COLLECT

DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
enums NumTab; -- no need to initialize
names NameTab;
BEGIN
SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;
...
END;

Bulk Fetch with LIMIT - limits fetching rows to a specified number.
This number can be a variable or an expression. But it can't be negative.
You can use limit as described below:

open cursor;
loop
fetch c bulk collect into l_c1, l_c2, ....... LIMIT 1000;

for i in 1 .. l_c1.count
loop
process....
end loop;

forall i in 1 .. l_c1.count
insert into ..... values ( L_c1(i), .... );


forall i in 1 .. l_c1.count
update emp set sal = sal * 1.2 where dept_no = l_c1(i);

exit when c%notfound;
end loop;
close cursor

1000 is already a big number.

With bigger number, we could meet problems with OS limits.