SQL%ROWCOUNT is not providing desired outcome with dynamic SQL Having BULK COLLECT and FORALL in Oracle PL/SQL

Question

SQL%ROWCOUNT is returning the count considered(10) for the run, not the exact number of records updated. Expectation is that SQL%ROWCOUNT should provide the actual number of records updated . Please suggest me how to achieve the task.

Code which triggers dynamic SQL

FORALL indx IN 1 .. l_account_data.COUNT  --assume 10 as count
  SAVE EXCEPTIONS
  EXECUTE IMMEDIATE dynamic_sql_query USING l_account_data (indx);
  DBMS_OUTPUT.put_line ('Successful UPDATE of '|| TO_CHAR (SQL%ROWCOUNT) || ' record');
  COMMIT;

dynamic_sql_query

BEGIN
   
   SELECT clmn_x, clmn_y
     BULK COLLECT INTO l_subscr_data
     FROM table_x e, table_y c
    WHERE c.ref_id = :account_no AND e.account_no = c.account_no;


   FORALL indx IN 1 .. l_subscr_data.COUNT
      UPDATE table_z ciem                --this update will update multiple records for each account
         SET ciem.ext_id = ciem.sub_no || ROWID
       WHERE ciem.sub_no = l_subscr_data (indx).clmn_x
             AND ciem.subscr_no_resets =   l_subscr_data (indx).clmn_y
             AND ciem.status  IN (1,2);
             
END;

Your outer code doesn't know what the dynamic code is doing. I guess you could put the inner count into a bind variable and add that to the `using` clause. But does it actually need to be dynamic? — Alex Poole, Mar 09 '22 at 08:26
Does this answer your question ? https://stackoverflow.com/questions/56456344/forall-and-sqlrowcount-when-forall-is-never-entered — Koen Lostrie, Mar 09 '22 at 08:47

score 0 · Accepted Answer · answered Mar 09 '22 at 09:20

Your outer execute immediate call isn't aware of what is happening inside the dynamic SQL; it doesn't know what it's doing, or how many rows it may or may not have affected.

To get an accurate count you would need to modify your dynamic statement to add something like:

FOR indx IN 1 .. l_subscr_data.COUNT LOOP
   :total_count := :total_count + coalesce(SQL%BULK_ROWCOUNT(indx), 0);
END LOOP;

and change your outer call to (a) pass an extra IN OUT bind variable to track the total count, and (b) use a FOR LOOP rather than FORALL, because that only seems to retain the value after the first dynamic call (not sure if that's documented, or a bug). So something like:

...
l_total_count number := 0;
...
FOR indx IN 1 .. l_account_data.COUNT LOOP
   EXECUTE IMMEDIATE dynamic_sql_query
      USING l_account_data (indx), in out l_total_count;
END LOOP;
DBMS_OUTPUT.put_line ('Successful UPDATE of '|| TO_CHAR (l_total_count) || ' record');

db<>fiddle demo with made-up data.

SQL%ROWCOUNT is not providing desired outcome with dynamic SQL Having BULK COLLECT and FORALL in Oracle PL/SQL

dynamic_sql_query

1 Answers1