java - Commit in PLSQL loop for update -

i using below function in java program copy column temporary table main table.

function test(tbl_name varchar2, tmp_tbl_name varchar2, id_col varchar2, req_col varchar2, batch_size number) return number begin execute immediate 'select count(1) ' || tmp_tbl_name total_records; offset := 0; while offset < total_records loop   max_results := offset + batch_size;   execute immediate 'select ' || id_col || ', ' || req_col || ' ' || tmp_tbl_name || ' seq_nbr between :offset , :max_results' bulk collect seq_ids, req_col_valuess using offset, max_results;   forall ind in seq_ids.first .. seq_ids.last     execute immediate 'update ' || tbl_name || ' set ' || req_col || ' = :req_col_val ' || id_col || ' = :id_col_val' using req_col_valuess(ind), seq_ids(ind);   offset := max_results;   commit; end loop; return 0; exception when others   raise cust_exception;  end; 

expected result when running batch_size of 100000, each commit 100000 records have updated id_col used above primary key. after running java program, in middle when checking updates in main table able see records of batches 6469 or 80148 being updated.

there 10 million records in temp table. if remove forall update statement iterating on data in proper batch size.

can clarify me on why happening

1) how many records have in temporary tables?

2) why don't use sys_refcursor , bulk collect limit ?

declare   v_cursor sys_refcursor;   res1 sys.dbms_debug_vc2coll; -- - predefined collection in oracle   res2 sys.dbms_debug_vc2coll;  v_batch_size pls_integer := 50; begin   open v_cursor 'select 1,2 dual connect level < 10000';  loop  fetch v_cursor bulk collect res1,res2 limit v_batch_size;  exit when res1.count =0;     dbms_output.put_line(res1.count);   -- forall ind in res1.first .. res1.last loop e.g    -- commit;  end loop;   close v_cursor; end;