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

SQL%ROWCOUNT works with FORALL

See the example below:

-- Data preparation

CREATE TABLE EMPLOYEES (
    EMPID     NUMBER,
    EMPNAME   VARCHAR2(100)
);

INSERT INTO EMPLOYEES VALUES (
    1,
    'a'
);

INSERT INTO EMPLOYEES VALUES (
    2,
    'b'
);

INSERT INTO EMPLOYEES VALUES (
    3,
    'c'
);

INSERT INTO EMPLOYEES VALUES (
    4,
    'a'
);

INSERT INTO EMPLOYEES VALUES (
    5,
    'e'
);

SELECT * FROM EMPLOYEES;

-- FORALL update in the block

SET SERVEROUT ON

DECLARE
    TYPE T_BULK_COLLECT_TEST IS
        TABLE OF EMPLOYEES%ROWTYPE;
    L_TAB   T_BULK_COLLECT_TEST;
    CURSOR C_DATA IS
    SELECT
        *
    FROM
        EMPLOYEES;

BEGIN
    OPEN C_DATA;
    FETCH C_DATA BULK COLLECT INTO L_TAB;
    FORALL I IN 1..L_TAB.COUNT
        UPDATE EMPLOYEES
        SET
            EMPNAME = L_TAB(I).EMPNAME || L_TAB(I).EMPNAME
        WHERE
            EMPID = L_TAB(I).EMPID;

    DBMS_OUTPUT.PUT_LINE('1) SQL%ROWCOUNT IS ' || SQL%ROWCOUNT);
    CLOSE C_DATA;

    DELETE FROM EMPLOYEES;
    DBMS_OUTPUT.PUT_LINE('2) SQL%ROWCOUNT IS ' || SQL%ROWCOUNT);
    COMMIT;

    OPEN C_DATA;
    FETCH C_DATA BULK COLLECT INTO L_TAB;
    FORALL I IN 1..L_TAB.COUNT
        UPDATE EMPLOYEES
        SET
            EMPNAME = L_TAB(I).EMPNAME || L_TAB(I).EMPNAME
        WHERE
            EMPID = L_TAB(I).EMPID;

    DBMS_OUTPUT.PUT_LINE('3) SQL%ROWCOUNT IS ' || SQL%ROWCOUNT);
    CLOSE C_DATA;
END;
/

-- Output after data is updated
SELECT * FROM EMPLOYEES;

So as per my example, It is storing a number of rows affected in SQL%ROWCOUNT even if we are using FORALL. ---- BUT BUT BUT, If I remove 'COMMIT' after 'DELETE' statement then I am also facing the same issue as described by you.

So the solution to your problem is COMMIT statement. Try to run your code with commit statement just after delete.

Hope, This is useful to you.

Updated

BEGIN
    OPEN C_DATA;
    FETCH C_DATA BULK COLLECT INTO L_TAB;
    FORALL I IN 1..L_TAB.COUNT
        UPDATE EMPLOYEES
        SET
            EMPNAME = L_TAB(I).EMPNAME || L_TAB(I).EMPNAME
        WHERE
            EMPID = L_TAB(I).EMPID;

    DBMS_OUTPUT.PUT_LINE('1) SQL%ROWCOUNT IS ' || SQL%ROWCOUNT);
    CLOSE C_DATA;

    DELETE FROM EMPLOYEES;
    DBMS_OUTPUT.PUT_LINE('2) SQL%ROWCOUNT IS ' || SQL%ROWCOUNT);
    SAVEPOINT A; --- IMP

    OPEN C_DATA;
    FETCH C_DATA BULK COLLECT INTO L_TAB;
    FORALL I IN 1..L_TAB.COUNT
        UPDATE EMPLOYEES
        SET
            EMPNAME = L_TAB(I).EMPNAME || L_TAB(I).EMPNAME
        WHERE
            EMPID = L_TAB(I).EMPID;

    DBMS_OUTPUT.PUT_LINE('3) SQL%ROWCOUNT IS ' || SQL%ROWCOUNT);
    CLOSE C_DATA;
END;
/

Answer 2

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