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 -----
From: Ajay K <ajay_at_tems.com>
To: Tom Pall <tom_at_cdproc.com>
Cc: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Friday, November 03, 2000 4:11 PM
Subject: Re: Why SQL%ROWCOUNT is returning 1 even when no records are found

> I guess in pl/sql function or procedure sql%rowcount will always return 1 row after
> select statement(u can't select more then 1 into) .
> It is often used with update or delete statement to check the number of rows
> affected(right?).
>
> Ajay
>
> om Pall wrote:
>
> > I don't write very much pl/sql these days, however I suspect the reason
> > you're getting 1 returned is that you are using an aggregate function, for which
> > sql%rowcount is inappropriate.
> > ----- 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;
> > >
> > > TIA
> > >
> > > Rao
> > >
> > > Maheswara.Rao_at_SunGardP3.com
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Rao, Maheswara
> > > INET: Maheswara.Rao_at_Sungardp3.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California -- Public Internet access / Mailing Lists
> > > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Tom Pall
> > INET: tom_at_cdproc.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
Received on Fri Nov 03 2000 - 16:29:30 CST