create table delete_me (id number)
declare
i number;
begin
for i in 0..50 loop
insert into delete_me values(i);
end loop;
commit;
end;
Using Bulk Load and Fetch Limit
declare
CURSOR C1 IS
SELECT id from delete_me;
mc c1%rowtype;
i number := 0;
fetchLimit number := 7;
TYPE Tt IS TABLE OF number;
mt Tt;
begin
open c1;
loop
--Load nested table here
FETCH C1 BULK COLLECT INTO mt LIMIT fetchLimit ;
--Perform other operations here
FOR i IN mt.first..mt.last loop
dbms_output.put_line(mt(i));
end loop;
exit when c1%notfound;
dbms_output.put_line('Chunk done');
end loop;
close c1;
end;
Simple manual way
declare
CURSOR C1 IS
SELECT id from delete_me;
mc c1%rowtype;
i number := 0;
TYPE Tt IS TABLE OF number index by binary_integer;
mt Tt;
begin
open c1;
loop
--Load nested table here
fetch c1 into mc;
exit when (c1%notfound);
mt(i) := mc.id;
i := i+1;
end loop;
close c1;
--Perform other operations here
FOR i IN mt.first..mt.last loop
dbms_output.put_line(mt(i));
end loop;
end; |