Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: sql%found problem

RE: sql%found problem

From: Toepke, Kevin M <ktoepke_at_cms.cendant.com>
Date: Tue, 18 Jul 2000 09:08:47 -0400
Message-Id: <10562.112254@fatcity.com>


It may just be the sample code, but I'm wondering why you're using the COUNT function to perform an existance check. I've never known a case where that is the fastest way to do this.

A better way to do an existance check is

DECLARE
    CURSOR c_exists IS

        SELECT 1
        FROM   str;
    v               NUMBER(1);

BEGIN
    OPEN c_exists;
    FETCH c_exists INTO v;
    IF (c_exists%FOUND) THEN

        DBMS_OUTPUT.PUT_LINE('Yes');
    ELSE
        DBMS_OUTPUT.PUT_LINE('No');
    END IF;
END; However, if you do need to know the number of rows that meet your criteria, you have to do the COUNT().

Kevin
>
> HAI all,
>
> The problem not with SQL%found ,problem with count function.
>
> If you use count function definitely it will return value
> Zero or more than
> zero.
> It means SQL Stmt executed successfully and returning rows
> also,so It will
> never go to 'No' section in your coding .
>
> By the way for your information
> If you used Count funtion in SQL Stmt You no need to define
> Exception WHEN
> NO_DATA_FOUND since the count function never return 'no rows';
>
>
> Regards.
> SUL.
>
>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: Tuesday, July 18, 2000 06:35 AM
>
>
> > Select count statement always returns a number whether
> there exists rows
> in the table or not.
> >
> > If there are no rows the outcome is 1 row with count as 0.
> > If there are some rows in the table output is 1 row with
> count of Number
> of rows.
> > As always there is a row returned SQl %found is always TRUE
> > So as per ur logic, it will always go to YES part...
> >
> > Saji
> >
> > ---------------------------------------- Message
> History ----------------------------------------
> >
> >
> >
> > Please respond to ORACLE-L_at_fatcity.com
> >
> > To: ORACLE-L_at_fatcity.com
> > cc:
> >
> >
> >
> > hi all,
> > i had a problem over this query.could u tell me what is
> wrong in that
> query.
> > ========
> > declare
> > v number(10);
> > begin
> > select count(name) into v from str;
> > if sql%found then
> > dbms_output.put_line('yes '||v);
> > else
> > dbms_output.put_line('no '||v);
> > end if;
> > end;
> > it displays only "yes'' even though the table contins or
> not contain any
> rows.
> > could u tellme what went wrong.
> > paul
> >
> >
> >
> >
> > --
> > Author:
> > INET: saji.rajp_at_db.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access /
> Mailing Lists
> >
> --
> Author: Sultan
> INET: ssyed_at_fine.co.ae
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
Received on Tue Jul 18 2000 - 08:08:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US