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
> also send the HELP command for other information (like subscribing).
Received on Fri Nov 03 2000 - 16:11:18 CST