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

Hello Maheswara,

Because you are using an aggregate function, you are getting one row back regardless of the number of rows actually in your table. Try the following from SQL*Plus:

SET NULL *null*

SELECT MIN(rowid)
FROM messages_received
WHERE originator = 'someone';

The SQL*Plus SET NULL command will cause SQL*Plus to display *null* whenever a null value is returned by a query. That should make it obvious to you what is really happening here.

If you need to know the number of messages originated by someone, you might try doing:

SELECT min(rowid),COUNT(*)
FROM messages_received
WHERE originator = 'someone';

Best regards,

Jonathan

Friday, November 03, 2000, 2:36:00 PM, you wrote:

RM> Hi All,

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

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

RM> BEGIN

RM> select min(rowid) into my_rowid
RM>   from messages_received
RM>   where
RM>        originator = 'blabla';
RM> dbms_output.put_line (sql%rowcount);

RM> end k2;

RM> /

RM> show errors;

RM> TIA RM> Rao

RM> Maheswara.Rao_at_SunGardP3.com

-- 
Best regards,
 Jonathan                            mailto:jonathan_at_gennick.com