Bulk collect fetch all rows from table in single shot so if table have so many rows then it can cause consume too much session memory and can raise an error .for this developer should set limit parameter carefully by keeping performance or memory overhead in mind.
Suppose I have 10000 rows in my emp table but my session doesn't have enough memory to hold 10000 row on that case we can use limit parameter.
Suppose I have 10000 rows in my emp table but my session doesn't have enough memory to hold 10000 row on that case we can use limit parameter.
declare c_limit PLS_INTEGER=100;
cursor emp_cur is select empno from emp where deptno=&depnto;
type l_emp_id_t is table of emp.empno%type;
l_emp_ids l_emp_id_t ;
begin
open emp_cur;
loop
fetch
emp_cur bulk collect into l_emp_ids limit c_limit;
exit when l_emp_ids.COUNT=0;
end loop;
end;
cursor emp_cur is select empno from emp where deptno=&depnto;
type l_emp_id_t is table of emp.empno%type;
l_emp_ids l_emp_id_t ;
begin
open emp_cur;
loop
fetch
emp_cur bulk collect into l_emp_ids limit c_limit;
exit when l_emp_ids.COUNT=0;
end loop;
end;