Number of rows affected by an UPDATE in PL/SQL

alternatively, SQL%ROWCOUNT you can use this within the procedure without any need to declare a variable

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.

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;