Number of rows affected by an UPDATE in PL/SQL
You use the sql%rowcount variable.
You need to call it straight after the statement which you need to find the affected row count for.
For example:
set serveroutput ON; DECLARE i NUMBER; BEGIN UPDATE employees SET status = 'fired' WHERE name LIKE '%Bloggs'; i := SQL%rowcount; --note that assignment has to precede COMMIT COMMIT; dbms_output.Put_line(i); END;
For those who want the results from a plain command, the solution could be:
begin DBMS_OUTPUT.PUT_LINE(TO_Char(SQL%ROWCOUNT)||' rows affected.');end;The basic problem is that SQL%ROWCOUNT is a PL/SQL variable (or function), and cannot be directly accessed from an SQL command. By using a noname PL/SQL block, this can be achieved.
... If anyone has a solution to use it in a SELECT Command, I would be interested.
alternatively, SQL%ROWCOUNTyou can use this within the procedure without any need to declare a variable