RE: Different behavior of Too_many_rows exception in pl/sql betwe
Title: RE: Different behavior of Too_many_rows exception in pl/sql betwe
if you are looking for one row only, then A and C will do. Since C involves a little bit of more coding and A does the same thing, A would be a better choice. But if you are coding to trap too many rows, then you should check for exceptions, all depends on your requirement.
rgds
amar
-Original Message-
From: Norrell, Brian [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 22, 2001 12:36 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Different behavior of Too_many_rows exception in pl/sql
betwe
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:[EMAIL PROTECTED]]
Sent: Thursday, June 21, 2001 2:34 PM
To: Multiple recipients of list ORACLE-L
v7v8i
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: Different behavior of Too_many_rows exception in pl/sql betwe
On Fri, Jun 22, 2001 at 12:45:34AM -0800, Amar Kumar Padhi wrote: if you are looking for one row only, then A and C will do. Since C involves a little bit of more coding and A does the same thing, A would be a better choice. But if you are coding to trap too many rows, then you should check for exceptions, all depends on your requirement. Unless, of course, the select statement in question has an order by clause. Since rownum is now generated before the ordering is applied, you'll get the first row of the unordered set, and that row will then be sorted. Not good. rgds amar -Original Message- Sent: Friday, June 22, 2001 12:36 AM To: Multiple recipients of list ORACLE-L betwe 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; -- Tommy Wareing Oxford English Dictionary -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tommy Wareing INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Different behavior of Too_many_rows exception in pl/sql betwe
Where is the fun in that? To quote a previous coworker Any clod can have
facts, but having an opinion is an art. Besides, you missed the two key
words of the question: and why? I was hoping to spark a nice extended
discussion on the inner workings of Oracle that everyone could enjoy while
remaining on topic.
Also, my current project sadly has SQLServer as a back end, so I have
limited access to real procedural database code (Transact-SQL is a beast).
This makes obtaining empirical evidence problematic. I mainly lurk on the
list to keep somewhat up to date in case I ever have to go back to the land
of scalability and multiple platforms.
Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600
-Original Message-
Sent: Thursday, June 21, 2001 4:13 PM
To: Multiple recipients of list ORACLE-L
betwe
Brian:
Why don't you do what us ubergeeks do? Test. in a loop, try run each
version, say 1000 times and tell us how long they take to run in your
environment.
(I want to know, but am too lazy to do this for myself right now!)
Kevin
-Original Message-
Sent: Thursday, June 21, 2001 4:36 PM
To: Multiple recipients of list ORACLE-L
betwe
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:[EMAIL PROTECTED]]
Sent: Thursday, June 21, 2001 2:34 PM
To: Multiple recipients of list ORACLE-L
v7v8i
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_nameuser_tables.table_name%TYPE;
begin
v_table_name := null;
select table_name
intov_table_name
fromuser_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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Toepke, Kevin M
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
RE: Different behavior of Too_many_rows exception in pl/sql betwe
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:[EMAIL PROTECTED]]
Sent: Thursday, June 21, 2001 2:34 PM
To: Multiple recipients of list ORACLE-L
v7v8i
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_nameuser_tables.table_name%TYPE;
begin
v_table_name := null;
select table_name
intov_table_name
fromuser_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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: Different behavior of Too_many_rows exception in pl/sql betwe
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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED]]
Sent: Thursday, June 21, 2001 2:34 PM
To: Multiple recipients of list ORACLE-L
v7v8i
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_nameuser_tables.table_name%TYPE;
begin
v_table_name := null;
select table_name
intov_table_name
fromuser_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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: Different behavior of Too_many_rows exception in pl/sql betwe
Brian:
Why don't you do what us ubergeeks do? Test. in a loop, try run each
version, say 1000 times and tell us how long they take to run in your
environment.
(I want to know, but am too lazy to do this for myself right now!)
Kevin
-Original Message-
Sent: Thursday, June 21, 2001 4:36 PM
To: Multiple recipients of list ORACLE-L
betwe
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:[EMAIL PROTECTED]]
Sent: Thursday, June 21, 2001 2:34 PM
To: Multiple recipients of list ORACLE-L
v7v8i
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_nameuser_tables.table_name%TYPE;
begin
v_table_name := null;
select table_name
intov_table_name
fromuser_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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Toepke, Kevin M
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
