Skip to Main Content
  • Questions
  • Request a new feature: bulk collect in batches

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, David.

Asked: February 25, 2020 - 5:33 pm UTC

Answered by: Connor McDonald - Last updated: February 26, 2020 - 1:53 am UTC

Category: PL/SQL - Version: all versions

Viewed 100+ times

You Asked

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

and we said...

Nice idea.

We have a community ideas area where you can propose new features and enhancements and the community vote on them.

https://community.oracle.com/community/groundbreakers/database/database-ideas


and you rated our response

  (1 rating)

Reviews

Alternative to above in oracle

February 26, 2020 - 10:54 am UTC

Reviewer: Manjunath

Similar functionality can be achieved in oracle by parameterzed cursor variables. Modification of below block will have similar effect but different syntax. Plus cursor variables are reusable.

DECLARE
    CURSOR c_user_objects(p_object_id number)
    IS
        SELECT *
        FROM user_objects
        WHERE object_id=p_object_id and rownum < 5;
        
    type uo_type is table of c_user_objects%rowtype;
    uo_array uo_type;
BEGIN
    open c_user_objects(10);
    fetch c_user_objects BULK COLLECT INTO uo_array limit 2;
    for i in 1..uo_array.count loop
        dbms_output.put_line(uo_array(i).object_name);
    END LOOP;
    CLOSE c_user_objects;
 
END;

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.