Request a new feature of PL/SQL: BULK COLLECT IN BATCHES

User_EM2R6

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

If the feature of BULK COLLECT IN BATCHES was available, I would be able to load the data from my cursor in the following ways:

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.

Dave