SQL row count differently than array count which is defined at 10000. Please suggest. Expecting SQL row count should be same as 10000

user_11243158 0
Viewed 184
if [ "${ARRAY_SIZE}" = "" ];then
  ARRAY_SIZE=10000
fi

declare
type t_reqseqArray is table of TEMP_TABLE_BKP.REQ_SEQ%type;
v_reqseqArray t_reqseqArray;
v_array_limit number(5) := ${ARRAY_SIZE};
v_commit_rate number(5) := ${COMMIT_RATE};
v_fetch_count number(5) := 0;
cursor c_reqseq is select req_seq from TEMP_TABLE_bkp;

v_TEMP_TABLE_bkp number(16) := 0;
v_array number(16) := 0;

begin
  open c_reqseq;
  loop
    v_fetch_count := v_fetch_count + 1;
        fetch c_reqseq bulk collect into v_reqseqArray limit v_array_limit;


        v_array := v_reqseqArray.count;
        dbms_output.put_line('Array count, '||to_char(v_array,'9999999')||' Request Sequences got');

        forall idx in 1..v_reqseqArray.count
         update TEMP_TABLE_bkp set req_seq=req_seq+1 where req_seq = v_reqseqArray(idx);
        v_TEMP_TABLE_bkp := sql%rowcount;

                exit when c_reqseq%notfound;
        if (v_fetch_count >= v_commit_rate) then
          dbms_output.put_line('Commit point reached, '||to_char(v_TEMP_TABLE_bkp,'9999999')||' Request Sequences got updated.');
          ${COMMIT};
          v_fetch_count := 0;
        end if;
  end loop;
  close c_reqseq;
end;
/

And result shows, Array count as 10000 where as SQL row count showed 12907.

Array count, 10000 Request Sequences got 12907 Commit point reached, 12907 Request Sequences got updated. Array count, 10000 Request Sequences got 8660 Commit point reached, 8660 Request Sequences got updated. Array count, 4001 Request Sequences got 2434

plsql oracle arrays
1 Answers

sql%rowcount is the number of rows updated by the whole forall .. update statement, not the number of times FORALL executed DML. Single update can affect many rows.

You can check also SQL%BULK_ROWCOUNT pseudo collection, it contains the number of rows affected for each DML statement executed by FORALL and you will see, that for some values v_reqseqArray(idx) it updated many rows.

Also bear in mind that your transaction sees your updates: cursor contains old values, the table contains updated, and UPDATE can see updates done in the same transaction.

BTW this is classic fetch across commit example.

user_27003440
Related