I would say that (A) will be the most performant
because you are giving Oracle the EXTRA information
that you only want one row. (b) and (c) could
possibly produce a massive result set of which you
only then fetch 1 row.
hth
connor
- "Norrell, Brian" <BNorrell_at_QuadraMed.com> wrote: >
I have seen code that depends on the 8.1 behavior.
> It does bring up a
> question I have always wondered about. The question
> for the internals
> ubergeeks is: which of the following is best to use?
> (All should have the
> same result, so which one has the best performance
> and why?)
>
> A)
> declare
> y number;
> begin
> select x into y from z where rownum = 1;
> foo(y);
> end;
>
> B)
> declare
> y number;
> begin
> begin
> select x into y from z;
> exception
> when too_many_rows then null;
> end;
> foo(y);
> end;
>
> C)
> declare
> y number;
> cursor c is
> select x into y from z;
> begin
> open c;
> fetch c into y;
> close c;
> foo(y);
> end;
>
> D) something else more clever or obscure ???
>
> Brian Norrell
> Manager, MPI Development
> QuadraMed
> 511 E John Carpenter Frwy, Su 500
> Irving, TX 75062
> (972) 831-6600
>
>
> -----Original Message-----
> [mailto:Prasada.Gunda1_at_hartfordlife.com]
> Sent: Thursday, June 21, 2001 2:34 PM
> To: Multiple recipients of list ORACLE-L
> v7&v8i
>
>
>
> We noticed a different behavior with too_many_rows
> exception in v7 and v8i.
> Is this a bug or that's the way it should work in
> 8i?
>
> I really appreciate your feedback on this.
> Thanks & Regards,
> Prasad
>
> declare
> v_table_name user_tables.table_name%TYPE;
> begin
> v_table_name := null;
> select table_name
> into v_table_name
> from user_tables;
> dbms_output.put_line('v_table_name :
> '||nvl(v_table_name,'null'));
> exception
> when too_many_rows then
> dbms_output.put_line('too_many_rows exception,
> v_table_name : '
> ||nvl(v_table_name,'null'));
> when others then
> dbms_output.put_line(sqlerrm);
> end;
> /
>
> In v7.3, It keeps value remains null in v_table_name
> column and v8i it is
> assigning the first received value.
> In both cases, It is raising too_many_rows
> exception.
>
> Output in v7.3:
>
> too_many_rows exception, v_table_name : null
>
> PL/SQL procedure successfully completed.
>
> Output in v8.1.7, v8.1.6, v8.1.5
>
> too_many_rows exception, v_table_name :
> CHAMPION_LIFE_DGNSS_CATEGORY
>
> PL/SQL procedure successfully completed.
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author:
> INET: Prasada.Gunda1_at_hartfordlife.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: Norrell, Brian
> INET: BNorrell_at_QuadraMed.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).
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Get your free @yahoo.co.uk address at
http://mail.yahoo.co.uk
or your free @yahoo.ie address at
http://mail.yahoo.ie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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 Thu Jun 21 2001 - 15:00:34 CDT
