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: paul <pjohnpeter_at_qssnet.com>
Date: Tue, 18 Jul 2000 13:44:41 -0500
Message-Id: <10562.112319@fatcity.com>


hi kimbwerly,

i will try this .its very new to me.could u tell me how it works.  actually my query should be like
selcec max(no) from table name where =primary key. if it exists i have to do some operations else do some other,

thanks
paul
-----Original Message-----
From: Kimberly.smith_at_gmd.fujitsu.com <Kimberly.smith_at_gmd.fujitsu.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Tuesday, July 18, 2000 1:37 PM
Subject: RE: sql%found problem

>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 Tue Jul 18 2000 - 13:44:41 CDT

Original text of this message

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