Re: SQL%ROWCOUNT value
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