Re: SQL%ROWCOUNT value

From: ddf <oratune_at_msn.com>
Date: Wed, 3 Dec 2008 07:08:35 -0800 (PST)
Message-ID: <e321c929-cfad-4306-a5c0-10e2b7b4d50c@13g2000yql.googlegroups.com>


Comments embedded.

On Dec 3, 8:51 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> Oracle 10g 10.2.0.3
>
> In the code below can someone tell me exactly what SQL%ROWCOUNT is
> returning?  Is it the number of rows processed or the number of rows
> updated?
>
> I'm trying to clarify since the individual who runs this says it's updating
> correctly but is questioning the value returned by SQL%ROWCOUNT and the
> count of rows updated the individual runs afterward since they don't match.
>
>   UPDATE clm_hdr a
>      SET person_id = NVL((
>                        SELECT b.person_id
>                          FROM mbr.v_mbr b
>                         WHERE b.mbr_key = '000' ||
> SUBSTR(a.mbr_sys_key,1,11)
>                                                 || a.mbr_sex
>                                                 ||
> SUBSTR(TO_CHAR(a.mbr_dob,'YYYYMMDD'),1,8)
>                                                 ||
> SOUNDEX(TRIM(a.mbr_first_nm))
>                          ),0)
>    WHERE a.person_id = 0;
>
>   v_hdr_cnt := SQL%ROWCOUNT;

It should be returning the number of rows actually processed:

SQL> set serveroutput on size 1000000
SQL>
SQL> select count(*)

  2 from emp
  3 where sal < 5000;

  COUNT(*)


        13

SQL>
SQL> CREATE TABLE employees_temp AS SELECT * FROM emp;

Table created.

SQL>
SQL> BEGIN
  2 UPDATE employees_temp SET sal = (select min(sal) * 1.05 from emp where sal > 5000) WHERE sal < 5000;   3 DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' salaries.');
  4 END;
  5 /
Updated 13 salaries.

PL/SQL procedure successfully completed.

SQL> David Fitzjarrell Received on Wed Dec 03 2008 - 09:08:35 CST

Original text of this message