RE: Why SQL%ROWCOUNT is returning 1 even when no records are foun

Tom,

Thank you for taking time to answer the problem. I do agree with you on NO_DATA_FOUND clause and its actions in case of group functions. However, why SQL%ROWCOUNT is returning always 1 is not clear

Thanks,

Rao

Maheswara.Rao_at_SunGardP3.com

-----Original Message-----
From: Tom Pall [mailto:tom_at_cdproc.com]
Sent: Friday, November 03, 2000 5:31 PM
To: Multiple recipients of list ORACLE-L Subject: Re: Why SQL%ROWCOUNT is returning 1 even when no records are found

Correct.

You are using it inappropriately here.

This is from the pl/sql reference: "SQL group functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls a group
function will never raise NO_DATA_FOUND." So testing for %notfound won't work either.
Looks like you'll have to change your query. ----- Original Message -----
 To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>  Sent: Friday, November 03, 2000 1:36 PM

 Hi All,

Could any body help me in the following program problem. In the following program, SQL%ROWCOUNT is always returning 1 even when no record is found. We are stuck on this problem. We will be thankful for any light on why SQL%ROWCOUNT is returning 1 even when no records are found.

create or replace procedure k2 is
 my_rowid varchar2(18);

BEGIN select min(rowid) into my_rowid

    from messages_received
   where

             originator = 'blabla';
dbms_output.put_line (sql%rowcount);

end k2;

 /

show errors; Received on Fri Nov 03 2000 - 17:40:03 CST