RE: sql%found problem

From: <Kimberly.smith_at_gmd.fujitsu.com>
Date: Tue, 18 Jul 2000 09:31:56 -0700
Message-Id: <10562.112288@fatcity.com>

Actually, I have never performed any tests on this but I was told to do the following select (assume cursor)
select 1 from dual where exists (select 1 from str); The reasoning given to me is that just select 1 from str still checks the whole table where as using the dual table kicks it out as soon as it find the first record. Has anyone else been told the same or things that this
is indeed true?

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Toepke, Kevin M
Sent: Tuesday, July 18, 2000 7:25 AM
To: Multiple recipients of list ORACLE-L Subject: RE: sql%found problem

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
> San Diego, California -- Public Internet access / Mailing Lists

--
Author: Toepke, Kevin M
  INET: ktoepke_at_cms.cendant.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 Tue Jul 18 2000 - 11:31:56 CDT