Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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); 
                @Clive I've got a procedure with INSERT INTO..COMMIT and also in the same procedure after the insert, I have UPDATE SET WHERE EXISTS..COMMIT, but my i := SQL%rowcount; is returning all the rows instead of the rows that was updated only. What could be?
– Guilherme Matheus
                May 22, 2020 at 18:58
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.
                SQL%ROWCOUNT is a function, you can't just "use it" - you need to do something with it - whether storing in a variable, or sending it as input to another procedure, or adding it to something else.
– Jeffrey Kemp
                Aug 8, 2012 at 4:48
                I think that Ali H's point is that it's not necessary to assign it to a variable until you have another SQL statement that would affect the row count.  That being said, I agree that it should be assigned to a variable to avoid causing a bug later should someone add another SQL statement before it is called. And, this answer from Ali H should be a comment on Clive's answer rather than posted as a separate answer
– Kirby
                Feb 25, 2014 at 19:36
SQL%ROWCOUNT can also be used without being assigned (at least from Oracle 11g).
As long as no operation (updates, deletes or inserts) has been performed within the current block, SQL%ROWCOUNT is set to null. Then it stays with the number of line affected by the last DML operation:
say we have table CLIENT
create table client (
  val_cli integer
 ,status varchar2(10)
We would test it this way:
begin
  dbms_output.put_line('Value when entering the block:'||sql%rowcount);
  insert into client 
            select 1, 'void' from dual
  union all select 4, 'void' from dual
  union all select 1, 'void' from dual
  union all select 6, 'void' from dual
  union all select 10, 'void' from dual;  
  dbms_output.put_line('Number of lines affected by previous DML operation:'||sql%rowcount);
  for val in 1..10
      update client set status = 'updated' where val_cli = val;
      if sql%rowcount = 0 then
        dbms_output.put_line('no client with '||val||' val_cli.');
      elsif sql%rowcount = 1 then
        dbms_output.put_line(sql%rowcount||' client updated for '||val);
      else -- >1
        dbms_output.put_line(sql%rowcount||' clients updated for '||val);
      end if;
  end loop;  
Resulting in:
Value when entering the block:
Number of lines affected by previous DML operation:5
2 clients updated for 1
no client with 2 val_cli.
no client with 3 val_cli.
1 client updated for 4
no client with 5 val_cli.
1 client updated for 6
no client with 7 val_cli.
no client with 8 val_cli.
no client with 9 val_cli.
1 client updated for 10
      update client set status = 'updated' where val_cli = val;
      if sql%rowcount = 0 then
        dbms_output.put_line('no client with '||val||' val_cli.');
        dbms_output.put_line(sql%rowcount||' client updated for '||val);
        counter := counter + sql%rowcount;
      end if;
  end loop;
   dbms_output.put_line('Number of total lines affected update operation: '||counter);
---------------------
select * from client;
--------------------------------------------------------
  
Result will be like below:
                After running update statement if you check the count on what you actually updated - This doesn't give any generic solution. For example, if my table T has one column c1 which contains "1" as value for all and now I update all rows for that column to "2", how will partitioning by null help?
– nanosoft
                Mar 20, 2017 at 9:02