Wanted guidance on generalized efficient approach to loop though select query result set (may be from few 100 rows to 50 Mil rows).
I do not want to use any cursors for the purpose. Please help with options like Rownum, record count or any other ways to batch the select query outputs to insert into a table.
Here's an article on using BULK COLLECT and BULK BIND
https://blogs.oracle.com/oraclemagazine/bulk-processing-with-bulk-collect-and-forall which *does* use cursors, but can be made to be very efficient.
Without cursors, the best way is a simple
insert into target
select from source
If you want to do it in pieces, but NOT use cursors (strange!) then you would need to run multiple operations, eg
insert into target
select *
from source
order by pk
fetch first 1000 rows only
then
insert into target
select *
from source
order by pk
offset 1000 rows fetch first 10 rows only;
and so forth, but that doesn't seem sensible to me