Hello,
I know we can use BULK COLLECT to load all the records from a cursor into a set of collection variables, plus we can also use LIMIT clause to set the max number of records per load. It would be very convenient if Oracle can support the feature of BULK COLLECT IN BATCHES.
For example, here is the data from my cursor:
channel hour grade
---------------------------------
1 00:00 0.3
1 01:00 0.4
1 02:00 0.4
1 03:00 0.2
2 00:00 0.3
2 01:00 0.4
2 02:00 0.4
2 03:00 0.2
2 04:00 0.1
Case 1) open cur for
select channel, hour, grade
from some_table
where …
order by channel, hour;
loop
fetch cur bulk collect into
channel_arr, hour_arr, grade_arr
IN BATCHES OF CHANNEL;
exit when channel_arr.count = 0;
-- process data in a batch
end loop;
close cur;
Execution:
The 1st loop loads all the 4 records of channel 1 into the arrays.
The 2nd loop loads all the 5 records of channel 2 into the arrays.
Execution exits on the 3rd loop.
Case 2) open cur for
select channel, hour, grade
from some_table
where …
order by channel, hour;
loop
fetch cur bulk collect into
channel_arr, hour_arr, grade_arr
IN BATCHES OF CHANNEL LIMIT 3;
exit when channel_arr.count = 0;
-- process data in a batch
end loop;
close cur;
Execution:
The 1st loop loads the first 3 records of channel 1 into the arrays.
The 2nd loop loads the 4th record of channel 1 into the arrays.
The 3rd loop loads the first 3 records of channel 2 into the arrays.
The 4th loop loads the last 2 records of channel 2 into the arrays.
Execution exits on the 5th loop.
Optionally, it will be great if a new cursor attribute is available to tell if the current load is a new batch or in the same batch as the previous load. This is useful for case 2) where LIMIT is specified along with BULK COLLECT IN BATCHES. For case 1) every load is a new batch.
Thank you.
David