Number of rows affected by an UPDATE in PL SQL

Question

I have a PL SQL function  running on Oracle 10g  in which I update some rows  Is there a way to find out how many rows were affected by the UPDATE  When executing the query manually it tells me how many rows were affected  I want to get that number in PL SQL

User · Answer

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

User · Answer

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     loop       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 --  gt 1         dbms output put line sql rowcount    clients updated for    val         end if    end loop    end    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

User · Answer

Please try this one       create table client     val cli integer   status varchar2 10      --------------------- begin 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  end   --------------------- select   from client   --------------------- declare   counter integer    0  begin   for val in 1  10     loop       update client set status    updated  where val cli   val        if sql rowcount   0 then         dbms output put line  no client with    val    val cli           else         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   end   --------------------- select   from client   --------------------------------------------------------      Result will be like below      2 client 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 Number of total lines affected update operation  5

User · Answer

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

User · Answer

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

User · Answer

Use the Count    analytic function OVER PARTITION BY NULL  This will count the total   of rows

[oracle] Number of rows affected by an UPDATE in PL/SQL

Examples related to oracle

Examples related to plsql

Examples related to sql-update