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: Different behavior of Too_many_rows exception in pl/sql

RE: Different behavior of Too_many_rows exception in pl/sql

From: JOE TESTA <JTESTA_at_longaberger.com>
Date: Fri, 22 Jun 2001 08:37:03 -0700
Message-ID: <F001.00333B12.20010622075557@fatcity.com>

Good point on discussion material, but not having a place to test it is not.  You can get darn near every piece of software to use with a one user dev license to use to your heart's content, hence the reason i'm using 9i right now, i'd never put anything on it in production but i at least get to place with it.
 
You got a pc that will run personal oracle 8i?  
joe
>>> BNorrell_at_QuadraMed.com 06/22/01 11:21AM
>>>Where is the fun in that?  To quote a previous coworker
"Any clod can havefacts, but having an opinion is an art".   Besides, you missed the two keywords of the question: "and why?"  I was hoping to spark a nice extendeddiscussion on the inner workings of Oracle that everyone could enjoy whileremaining on topic.Also, my current project sadly has SQLServer as a back end, so I havelimited access to real procedural database code (Transact-SQL is a beast).This makes obtaining empirical evidence   problematic.  I mainly lurk on thelist to keep somewhat up to date in case I ever have to go back to the landof scalability and multiple platforms.Brian NorrellManager, MPI DevelopmentQuadraMed511 E John Carpenter Frwy, Su 500Irving, TX 75062(972) 831-6600-----Original Message-----Sent: Thursday, June 21, 2001 4:13 PMTo: Multiple recipients of list ORACLE-LbetweBrian:Why don't you do what us "ubergeeks" do? Test. in a loop, try run eachversion, say 1000 times and tell us how long they take to run in yourenvironment.(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 PMTo: Multiple recipients of list ORACLE-LbetweI have seen code that depends on the 8.1 behavior.  It does bring up aquestion I have always wondered about.  The question for the internalsubergeeks is: which of the following is best to use? (All should have thesame 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 NorrellManager, MPI DevelopmentQuadraMed511 E John Carpenter Frwy, Su 500Irving, TX 75062(972) 831-6600-----Original Message-----[<A
href="mailto:Prasada.Gunda1_at_hartfordlife.com]">mailto:Prasada.Gunda1_at_hartfordlife.com]Sent: Thursday, June 21, 2001 2:34 PMTo: Multiple recipients of list ORACLE-Lv7&v8iWe 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,Prasaddeclarev_table_name   
user_tables.table_name%TYPE;beginv_table_name := null;select  table_nameinto    v_table_namefrom   
user_tables;dbms_output.put_line('v_table_name : '||nvl(v_table_name,'null'));exceptionwhen 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 isassigning 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 : nullPL/SQL procedure successfully completed.Output in v8.1.7, v8.1.6, v8.1.5too_many_rows exception, v_table_name : CHAMPION_LIFE_DGNSS_CATEGORYPL/SQL procedure successfully completed.-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author:
  INET: Prasada.Gunda1_at_hartfordlife.comFat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051San Diego, California        -- Public Internet access / Mailing
Lists--------------------------------------------------------------------To 
REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso 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.comFat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051San Diego, California        -- Public Internet access / Mailing
Lists--------------------------------------------------------------------To 
REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso 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: ktoepke_at_cms.cendant.comFat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051San Diego, California        -- Public Internet access / Mailing
Lists--------------------------------------------------------------------To 
REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso 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.comFat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051San Diego, California        -- Public Internet access / Mailing
Lists--------------------------------------------------------------------To 
REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso send the HELP command for other information (like subscribing). Received on Fri Jun 22 2001 - 10:37:03 CDT

Original text of this message

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