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;
/

`sql%rowcount` is the result of last dml. you update in a forall Loop was not executed, so `sql%rowcount` Shows the result of your delete Statement. whats wrong? — hotfix, Jun 05 '19 at 08:34
@hotfix thank you. nothing wrong. I am just curious on how to nicely deal with this scenario. my solution with the `if v_numbers.count > 0 then` seems clumsy to me and might even produce wrong results (can't think of a case). — Peter, Jun 05 '19 at 08:39
I do not quite understand what the problem is. why not to check if the collection is empty before? — hotfix, Jun 05 '19 at 08:45
@hotfix that's exactly my problem/question :-) what is the best practice here? is the best practice to check the count of the collection before the forall? — Peter, Jun 05 '19 at 08:47
@apc just tried it in *db<>fiddle [here](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=223ff87dd2dbc8a0eb13b726083e207e)* and i don't see a mystery. — hotfix, Jun 05 '19 at 09:30
Why not just add a check to see if v_numbers.count is > 0, and if so return the sql%rowcount, otherwise return 0? — Boneist, Jun 05 '19 at 09:31
@Boneist this is what I am doing, see example above. But I was wondering if this is best practice here as it felt weird 'inventing' this myself for such a standard 'problem' like getting the rowcount after a forall. — Peter, Jun 05 '19 at 09:36
It's pretty much the only way to handle this. Your update (as hotfix has already said) isn't being run at all, so sql%rowcount won't reflect anything related to it. You need to have a check that says, in effect, "if the statement didn't get run, return 0, otherwise return sql%rowcount". It's a pretty standard check in this situation afaik. — Boneist, Jun 05 '19 at 09:41

score 2 · Answer 1 · answered Jun 05 '19 at 12:25

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

Thank you! I understand that you would go the same way as in my example. Like you, I also think that `bulk_rowcount` is an overkill for my example, especially as it does the exact same thing as my example for empty collections `ret number := 0; [...] for i in 1..x.count loop [... loop not entered...] end loop; return ret;` to me is equivalent to what I do `v_rowcount := 0; if v_numbers.count > 0 then [... not entered ...] end if; DBMS_OUTPUT.put_line(v_rowcount);` Both versions return the number 0 if the collection is empty, without checking any sql% parameters. — Peter, Jun 06 '19 at 07:17
Yes. `bulk_rowcount` is interesting alternative which allows you to find number of affected rows for each element of collection what is not possible with `rowcount` and `forall`. But here I would use yours version. — Ponder Stibbons, Jun 06 '19 at 09:48

Popeye · Answer 2 · 2019-06-05T12:43:45.170

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;
/

hi thanks. yes sql%rowcount works with FORALL, but ONLY IF the pl/sql collection is not empty. In your example l_tab contains 5 objects. In my example the collection contains no objects and hence sql%rowcount is not set. My question is about how dealing with sql%rowcount after a FORALL where the collection is empty. See my example. ty. kr — Peter, Jun 05 '19 at 08:37
I have updated my answer according to your requirement. Please check and test with your code. — Popeye, Jun 05 '19 at 08:46
thank you, but I don't want to commit just for the sql%rowcount to be correct :-) — Peter, Jun 05 '19 at 08:49
Perfect, I am just explaining why it is not working. and the solution was given if useful to you. — Popeye, Jun 05 '19 at 12:37
I have updated the answer. Just use `SAVEPOINT`. No need of `COMMIT`. It will work. — Popeye, Jun 05 '19 at 12:42
Please refer: [link](http://www.dba-oracle.com/plsql/t_plsql_sparse.htm) -- **SQL%ROWCOUNT**: Refers to the total number of rows affected by the whole bulk operation. It is the sum of the SQL%BULK_ROWCOUNT values. — Popeye, Jun 05 '19 at 12:53
thank you very much for the smart idea with the `savepoint`. Works like a charm. But even when I call the savepoint `reset_sql_rowcount` (the shortest self explanatory I could come up with is 35 chars so too long `ResetSqlRowcountIfForallNotEntered`), I feel that the code needs an additional comment to explain why the savepoint is there so that the next person that reads/changes the code understands it. So I think I still prefer the solutions without a savepoint, as they seems more likely that the reader of the code understands what is going on in the code. — Peter, Jun 06 '19 at 07:36

FORALL and sql%rowcount when FORALL is never entered

2 Answers2

Linked