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;
CodePudding user response:
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.