RE: sql%found problem

Kimberly, You found a bug in my code. The select should have been

     CURSOR c_exists IS
         SELECT 1
         FROM   str
         WHERE  rownum = 1;

As for using the Select from Dual/existance check method, that sometimes yields unbelievable performance and other times it'll majorly kill performance (at least on a 7.3.4 database.)

My advice is to check both methods and, if the performance is similar, go with what you are most comforatable with.

Kevin
>
>
> 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-----
> Kevin M
> Sent: Tuesday, July 18, 2000 7:25 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
> also send the HELP command for other information (like subscribing).
>
> --
> Author:
> INET: Kimberly.smith_at_gmd.fujitsu.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
Received on Wed Jul 19 2000 - 06:23:22 CDT