Re: SQL%ROWCOUNT includes counts from subqueries?

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Thu, 3 Jun 2010 06:04:15 -0700 (PDT)
Message-ID: <f7449f14-e7b0-44f6-ba7a-870738dcce4a_at_m33g2000vbi.googlegroups.com>

On Jun 3, 6:14 am, Thomas Gagne <TandGandGA..._at_gmail.com> wrote:
> I have an update statement that if run by itself updates 14 rows, but
> when I test the value of SQL%ROWCOUNT it's value is 28.
>
> I'm wondering if SQL%ROWCOUNT is counting both the rows found in a
> subquery as well as the rows updated by the subquery.
>
> Basically
>
> update tablex
>        set (col1, col2, col3) = (select /* correlated subquery */ )
>    where tablex.id in (select /* 14 ids from tablex */ )
>
> 14 rows updated
>
> (but SQL%ROWCOUNT = 28!)
>
> --
> Visit<http://it.toolbox.com/blogs/anything-worth-doing/>
> for more great reading.

Can you post a test case (table DDL, sample data, the PL/SQL you're running?) I could not reproduce this on 10.2.0.4 - SQL%ROWCOUNT equals the number of rows affected by the update - but maybe my test case is not representative. Here's what I tried:

create table tablex (id number, col1 number, col2 number, col3 number);

insert into tablex
 select level, 1, 1, 1 from sys.dual
   connect by level < 21;

declare
 cnt number;
begin
  update tablex x1

     set (col1, col2, col3) =
     (select col1+1, col2+1, col3+1
       from tablex x2
      where x2.id = x1.id)

   where id in (select id from tablex where id < 15);   cnt := sql%rowcount;
  dbms_output.put_line(cnt);
end;

This updates 14 rows and sql%rowcount is also 14.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Jun 03 2010 - 08:04:15 CDT