FORALL and sql%rowcount when FORALL is never entered

Question

I am curious how you guys deal with the problem that sql%rowcount is not set after a FORALL that is not entered at all. Example of how I solve it below (using a variable v_rowcount and the count of the collection the FORALL is based on). But I have the feeling that there is a smarter approach:

create table tst (id number); -- we start with an empty table

declare
    type type_numbers is table of number;
    v_numbers type_numbers;
    v_rowcount number;
begin

insert into tst values (1);                     
DBMS_OUTPUT.put_line(sql%rowcount); -- prints 1 which is correct, 1 row inserted

delete from tst;
DBMS_OUTPUT.put_line(sql%rowcount); -- prints 1 which is correct, 1 row deleted 

v_numbers := type_numbers(3,4,5);
forall v in 1 .. v_numbers.count
    update tst
    set id = v_numbers(v)
    where id = v_numbers(v);
DBMS_OUTPUT.put_line(sql%rowcount); -- prints 0 which is correct, 0 rows updated

insert into tst values (1);
DBMS_OUTPUT.put_line(sql%rowcount); -- prints 1 which is correct, 1 row inserted

delete from tst;
DBMS_OUTPUT.put_line(sql%rowcount); -- prints 1 which is correct, 1 row deleted  

v_numbers := type_numbers();
forall v in 1 .. v_numbers.count
    update tst
    set id = v_numbers(v)
    where id = v_numbers(v);
DBMS_OUTPUT.put_line(sql%rowcount); -- prints 1 which is WRONG, 0 rows updated (this is still the sql%rowcount of the DELETE above)

forall v in 1 .. v_numbers.count
    update tst
    set id = v_numbers(v)
    where id = v_numbers(v);
v_rowcount := 0;
if v_numbers.count > 0 then
    v_rowcount := sql%rowcount;
end if;
DBMS_OUTPUT.put_line(v_rowcount);   -- prints 0 which is correct, 0 rows updated

end;
/

Answer 1

I would do it as in your example, but there is also alternative. We can use sql%bulk_rowcount which may be better for forall. Here is a link to Oracle documentation and some examples. And this is my test table:

create table test (id, val) as (
  select 1, 'PQR' from dual union all
  select 2, 'AB1' from dual union all
  select 2, 'AB2' from dual union all
  select 3, 'XYZ' from dual );

and sample code block, where I used short function summing bulk_rowcounts:

declare 
    type t is table of number;
    a t;

    function bulkcount(x in t) return number is 
      ret number := 0;
    begin
      for i in 1..x.count loop
        ret := ret + sql%bulk_rowcount(i);
      end loop;
      return ret;
    end bulkcount;
begin
    a := t(2, 3, 7);
    forall i in a.first..a.last 
        delete from test where id = a(i);

    dbms_output.put_line('sql rowcount: '||sql%rowcount);
    dbms_output.put_line('sum of bull_rowcount: '||bulkcount(a));

    a := t();
    forall i in a.first..a.last
        update test set val = 'ZZZ' where id = a(i);

    dbms_output.put_line('sql rowcount: '||sql%rowcount);
    dbms_output.put_line('sum of bull_rowcount: '||bulkcount(a));
end;

Result:

sql rowcount: 3
sum of bull_rowcount: 3
sql rowcount: 3          -- "wrong", value from previous DML
sum of bull_rowcount: 0