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

From: yong huang <yong321_at_yahoo.com>
Date: Sun, 5 Nov 2000 12:57:55 -0800 (PST)
Message-Id: <10671.121161@fatcity.com>

Hi, Rao,

There's nothing special about sql%rowcount. select min(somecolumn) from anytable always returns one row. To verify this, try setting feedback to 1 instead of leaving it at the default 6 (SET FEEDBACK 1) and run the query in plain SQL. An empty table gives one row when you select min(itscolumn) from it. The data of the row is NULL.

Alternatively, try changing your min(rowid) to rowid in your PL/SQL and you'll get ORA-1403 "no data found" error before it hits your sql%rowcount.

Yong Huang
yong321_at_yahoo.com

you wrote:

 From: "Rao, Maheswara"
 Date: Fri, 3 Nov 2000 14:35:25 -0500
 Subject: Why SQL%ROWCOUNT is returning 1 even when no records are found

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;

TIA Rao

Maheswara.Rao_at_SunGardP3.com

and:

 From: "Rao, Maheswara"
 Date: Fri, 3 Nov 2000 18:40:03 -0500
 Subject: 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

---

Do You Yahoo!?
Thousands of Stores. Millions of Products. All in one Place. Received on Sun Nov 05 2000 - 14:57:55 CST