I have below plsql bulk script which print the output with each batch size and batch completion time, after the entire update is completed.
set timing on echo on verify on feedback on
set serveroutput on
whenever sqlerror exit failure rollback
declare
type update_id is table of tbl_source%rowtype;
tab update_id := update_id();
start_time number;
end_time number;
c_limit int; -- bulk collect limit
cursor load_cur is
select * from tbl_source;
begin
open load_cur;
start_time := dbms_utility.get_time;
loop
fetch load_cur
bulk collect into tab limit &c_limit;
begin
forall i in tab.first..tab.last
update tbl_target
set value = tab(i).name
where id = tab(i).id
;
dbms_output.put_line('Bulk (' || tab.count || ' rows): ' || (dbms_utility.get_time - start_time)/100 || 'seconds');
exception
when others then
dbms_output.put_line('Updated '|| sql%rowcount || ' rows.');
dbms_output.put_line(sqlerrm);
commit;
end;
exit when load_cur%notfound;
end loop;
end_time := dbms_utility.get_time;
dbms_output.put_line('Bulk Update Time taken: ' || (end_time - start_time)/100 || 'seconds');
close load_cur;
end;
Bulk (50000 rows): 112.87seconds
Bulk (50000 rows): 186.74seconds
Bulk (50000 rows): 255.5seconds
Bulk (50000 rows): 326.01seconds
Bulk (50000 rows): 428.44seconds
Bulk (50000 rows): 503.91seconds
Bulk (50000 rows): 570.92seconds
Bulk (50000 rows): 636.56seconds
Bulk (50000 rows): 686.8seconds
Bulk (50000 rows): 748.89seconds
Bulk (50000 rows): 811.76seconds
Bulk (50000 rows): 865.96seconds
Bulk (50000 rows): 915.68seconds
Bulk (50000 rows): 964.28seconds
Bulk (50000 rows): 1018.27seconds
Bulk (50000 rows): 1098.99seconds
Bulk (8359 rows): 1110.22seconds
Bulk Update Time taken: 1110.22seconds
PL/SQL procedure successfully completed.
Elapsed: 00:18:30.822
But I need the batch size and completion time to be printed as soon as the 1st batch is completed and so on for the next batches. So I would know how many batches got completed and how many are pending.
