Re: pl/sql open cursor question
Jared, Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste. I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences: Functions: create or replace function a return varchar2 is begin for srec in (select dummy from ctest) loop return srec.dummy; end loop; return null; end; / create or replace function b return varchar2 is cursor c1 is select dummy from ctest; v_dummy varchar2(1) := null; begin open c1; fetch c1 into v_dummy; close c1; return v_dummy; end; / create or replace function b2 return varchar2 is v_dummy varchar2(1) := null; begin select dummy into v_dummy from ctest; return v_dummy; exception when no_data_found then return null; end; / Testrun: set serveroutput on size 2 exec runstats_pkg.rs_start; declare l_loop number := 0; l_dummy varchar2(1); begin for l_loop in 1..1000 loop l_dummy := a; end loop; end; / exec runstats_pkg.rs_middle; declare l_loop number := 0; l_dummy varchar2(1); begin for l_loop in 1..1000 loop l_dummy := b; end loop; end; / exec runstats_pkg.rs_stop(1); For the test of b2 b was simply replaced by b2) The results: a vs b: Run1 ran in 18 hsecs Run2 ran in 18 hsecs run 1 ran in 100% of the time Name Run1 Run2 Diff LATCH.enqueue hash chains 28 26 -2 LATCH.enqueues 28 26 -2 LATCH.library cache 2,067 2,069 2 LATCH.redo allocation 33 31 -2 LATCH.library cache pin 2,046 2,048 2 STAT...enqueue requests 16 14 -2 STAT...enqueue releases 16 14 -2 STAT...calls to get snapshot s 4,011 4,009 -2 STAT...active txn count during 16 8 -8 STAT...consistent gets - exami 16 8 -8 STAT...calls to kcmgcs 16 8 -8 STAT...cleanout - number of kt 16 8 -8 STAT...CPU used by this sessio 33 23 -10 STAT...consistent gets 3,026 3,016 -10 STAT...CPU used when call star 33 23 -10 STAT...redo entries 46 34 -12 LATCH.cache buffers chains 6,226 6,212 -14 STAT...db block changes 63 49 -14 STAT...db block gets 100 68 -32 STAT...session logical reads 3,126 3,084 -42 STAT...redo size 30,224 29,720 -504 STAT...recursive calls 1,001 2,001 1,000 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 11,543 11,525 -18 100.16% a vs b2: Run1 ran in 17 hsecs Run2 ran in 23 hsecs run 1 ran in 73.91% of the time Name Run1 Run2 Diff LATCH.enqueue hash chains 28 26 -2 LATCH.enqueues 28 26 -2 LATCH.library cache 2,067 2,069 2 STAT...bytes received via SQL* 984 986 2 LATCH.library cache pin 2,046 2,048 2 LATCH.redo allocation 34 31 -3 STAT...CPU used by this sessio 32 29 -3 STAT...enqueue releases 17 14 -3 STAT...enqueue requests 17 14 -3 STAT...CPU used when call star 32 29 -3 STAT...calls to get snapshot s 4,013 4,009 -4 STAT...active txn count during 17 8 -9 STAT...cleanout - number of kt 17 8 -9 STAT...calls to kcmgcs 17 8 -9 STAT...consistent gets - exami 17 8 -9 STAT...consistent gets 3,029 3,016 -13 STAT...recursive cpu usage 12 25 13 STAT...redo entries 49 34 -15 STAT...db block changes 69 48 -21 LATCH.cache buffers chains 6,235 6,207 -28 STAT...db block gets 111 66 -45 STAT...session logical reads 3,140 3,082 -58 STAT...redo size 30,648 29,660 -988 STAT...recursive calls 1,001 2,001 1,000 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 11,557 11,519 -38 100.33% (9.2.0.2/SuSE 8.1) I find it quite strange that results vary from run to run. E.g. in a vs b active txn count was 16 for a, whilst it was 17 for a vs. b2. Why this difference? B variants are consequently cheaper in redo size, session logical reads. Recursive calls is conseqently 1000 higher for b/b2. What counts is runtime, and a and b have no differences, b2, is appr. 25% slower. b/b2 consume slightly less latches, but the diff is 1 %. So, IMHO what remains is the question of taste. I started 22 years ago as software engineer, worked in RD departments (of commercial software firms) and built a 4GL, including a compiler and a universal interpreter for the code produced. Maybe bearing the burdens of my career as a stone-age 'C'-developer influences my 'taste' of beautiness of code (or the lack off it). Unless I've put some flaws in my testruns (which I'm glad to hear of), I rest my case. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === At 17:44 28-12-03 -0800, you wrote: Carel, It might seem that the loop construct would be more expensive, but it didn't appear that way on my test system. ( 9.2.0.4, RH 8.0 ) function a: create or replace function a return varchar2 is begin for srec in (select dummy from ctest) loop return srec.dummy; end loop; return null; end; / function b: create or replace function b return varchar2 is cursor c1 is select dummy from ctest; v_dummy varchar2(1) := null; begin open c1; fetch c1 into v_dummy; close
Re: pl/sql open cursor question
There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. Technically, if the implicit code and the explicit code were written to do exactly the same thing, then the implicit code should be faster because of a couple of under-cover optimisations. (This has been true for several years, I believe). Currently (9.2.0.X-ish) there is a bug that I recently found on metalink which says something about the FETCHes from an implicit cursor using more CPU than the FETCHes from an explicit cursor. Bottom line - test it in the environment where you are using it, and on the version you are running in production. In almost all cases, the difference will probably be imperceptible, anyway. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10:49 AM Jared, Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste. I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: pl/sql open cursor question
There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. That isn't a factor, as I never use the results from the first run for that very reason. Jared Jonathan Lewis [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/30/2003 03:29 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pl/sql open cursor question There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. Technically, if the implicit code and the explicit code were written to do exactly the same thing, then the implicit code should be faster because of a couple of under-cover optimisations. (This has been true for several years, I believe). Currently (9.2.0.X-ish) there is a bug that I recently found on metalink which says something about the FETCHes from an implicit cursor using more CPU than the FETCHes from an explicit cursor. Bottom line - test it in the environment where you are using it, and on the version you are running in production. In almost all cases, the difference will probably be imperceptible, anyway. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10:49 AM Jared, Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste. I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: pl/sql open cursor question
I wasn't thinking of the boundary conditions, I was thinking of the totally different mechanisms that appear because you are running pl/sql rather than (say) a loop in Pro*C that sends a pure SQL statement 1,000 times to the database. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 5:49 PM There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. That isn't a factor, as I never use the results from the first run for that very reason. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: pl/sql open cursor question
At 09:49 30-12-03 -0800, you wrote: There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. That isn't a factor, as I never use the results from the first run for that very reason. Jared Neither did I. But what wonders me is that even after the firest run (preceded by a flush of the shared_pool) every subsequent run (whithout flush, of course) gave other figures. The ones I included in my message where the most representative figures of over 20 runs. I stopped processes like vmware etc to get the system as stable as possible, but differences stayed pretty significant. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === Jonathan Lewis [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/30/2003 03:29 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: Re: pl/sql open cursor question There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. Technically, if the implicit code and the explicit code were written to do exactly the same thing, then the implicit code should be faster because of a couple of under-cover optimisations. (This has been true for several years, I believe). Currently (9.2.0.X-ish) there is a bug that I recently found on metalink which says something about the FETCHes from an implicit cursor using more CPU than the FETCHes from an explicit cursor. Bottom line - test it in the environment where you are using it, and on the version you are running in production. In almost all cases, the difference will probably be imperceptible, anyway. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10:49 AM Jared, Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste. I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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: pl/sql open cursor question
cursor for loops automatically close cursors. dont use when others then null on code you are putting in an application. if you have a bug you will have a hard time finding it. Its a fundamental flaw. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 28, 2003 10:54 AM I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: pl/sql open cursor question
Hi if we assume it is implements this way (see below) there will only be one cursor since c_gid is a bind variable and there for the cursor will be sharded from call to call of the function. create or replace function XYZ (gid in number) return varchar2 is cursor cur1(c_gid number) is select C1 from tab1 where id = c_gid; begin for x in cur1(gid) loop return x.c1; end loop; return null; exception when others then return null; end; It will only be one coursor Guang Mei wrote: I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Best regards/Venlig hilsen Peter Gram MiracleA/S Kratvej 2 DK - 2760 Mlv Cell:(+45) 2527 7107 Phone:(+45) 4466 8855 Fax:(+45) 4466 8856 Home:(+45) 3874 5696 Email:[EMAIL PROTECTED]
Re: pl/sql open cursor question
I thought just the execution plan was shared? I thought the definition of a cursor, was the memory area used to store the data. That data does not stay persistent in memory with a cursor for loop it closes. correct me if Im wrong? - Original Message - From: Peter Gram To: Multiple recipients of list ORACLE-L Sent: Sunday, December 28, 2003 12:39 PM Subject: Re: pl/sql open cursor question Hi if we assume it is implements this way (see below) there will only be one cursor since c_gidis a bind variable and there for the cursor will be sharded from call to call of the function.create or replace function XYZ (gid in number) return varchar2 is cursor cur1(c_gid number) is select C1 from tab1 where id = c_gid;begin for x in cur1(gid) loop return x.c1; end loop; return null;exception when others then return null;end;It will only be one coursor Guang Mei wrote: I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Best regards/Venlig hilsen Peter Gram MiracleA/SKratvej 2DK - 2760 Måløv Cell:(+45) 2527 7107Phone:(+45) 4466 8855Fax:(+45) 4466 8856Home:(+45) 3874 5696Email:[EMAIL PROTECTED]
Re: pl/sql open cursor question
Hi: I thought in the orginal code (cursor cur1 is select C1 from tab1 where ID = gid;), gid is a parameter passed in so it is already a bind variable. I don't see any difference to what you proposed. Your method is just make cur1 take a paramter? Am I wrong here? Also what happens when your function is called from different sessions? Is cursor_shared = force need to be set in init.ora? Guang On Sun, 28 Dec 2003, Peter Gram wrote: Hi if we assume it is implements this way (see below) there will only be one cursor since c_gid is a bind variable and there for the cursor will be sharded from call to call of the function. create or replace function XYZ (gid in number) return varchar2 is cursor cur1(c_gid number) is select C1 from tab1 where id = c_gid; begin for x in cur1(gid) loop return x.c1; end loop; return null; exception when others then return null; end; It will only be one coursor Guang Mei wrote: I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Best regards/Venlig hilsen /*Peter Gram*/ mailto:[EMAIL PROTECTED] Miracle A/S http://www.miracleas.dk/ Kratvej 2 DK - 2760 Måløv Cell: (+45) 2527 7107 Phone: (+45) 4466 8855 Fax: (+45) 4466 8856 Home: (+45) 3874 5696 Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: pl/sql open cursor question
What I don't understand is the loop construction: Actually only one (row) is read form the cursor, and then the function is left with a return. Because it's an unconditional return, the code within the loop will either execute once, or never. When no data is found NULL is returned. When an error occurs NULL is returned as well. So, why a loop? Wouldn't it be better to have something like: create or replace function XYZ(gid in number) return varchar2 is l_c1 tab1.C1%TYPE; /* local variable to store C1 */ begin select c1 into l_c1 from tab1 where id = gid; return l_c1; exception when no_data_found then return some_error_code; /* let the caller know that no data is found */ when others then return 'ERROR: '||TO_CHAR(SQLCODE); /* return the error-code, preceded by the text ERROR for identification */ end; Sure, a loop prevents an ORA-1422, but I don't think a loop construction should be abused for this. Just think about all loop controlling code that needs to be set up by the interpreter. tab1.ID should be unique, so a 1422 normally cannot occur. Robust programming however asks us to prevent any error. I would prefer to think about how a 1422 should be handled, and write some code accordingly. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === At 09:39 28-12-03 -0800, you wrote: Hi if we assume it is implements this way (see below) there will only be one cursor since c_gid is a bind variable and there for the cursor will be sharded from call to call of the function. create or replace function XYZ (gid in number) return varchar2 is cursor cur1(c_gid number) is select C1 from tab1 where id = c_gid; begin for x in cur1(gid) loop return x.c1; end loop; return null; exception when others then return null; end; It will only be one coursor Guang Mei wrote: I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Best regards/Venlig hilsen Peter Gram Miracle A/S Kratvej 2 DK - 2760 Måløv Cell: (+45) 2527 7107 Phone: (+45) 4466 8855 Fax: (+45) 4466 8856 Home: (+45) 3874 5696 Email: [EMAIL PROTECTED]
Re: pl/sql open cursor question
dont use when others then null on code you are putting in an application. if you have a bug you will have a hard time finding it. Its a fundamental flaw. One place where I have found it justified, is in logon trigger where users must be able to log on, despite any errors which occur in a logon trigger... (continued) ..of course with some kind of error logging mechanism implemented. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: pl/sql open cursor question
dont use when others then null on code you are putting in an application. if you have a bug you will have a hard time finding it. Its a fundamental flaw. One place where I have found it justified, is in logon trigger where users must be able to log on, despite any errors which occur in a logon trigger... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: pl/sql open cursor question
Carel, It might seem that the loop construct would be more expensive, but it didn't appear that way on my test system. ( 9.2.0.4, RH 8.0 ) function a: create or replace function a return varchar2 is begin for srec in (select dummy from ctest) loop return srec.dummy; end loop; return null; end; / function b: create or replace function b return varchar2 is cursor c1 is select dummy from ctest; v_dummy varchar2(1) := null; begin open c1; fetch c1 into v_dummy; close c1; return v_dummy; end; / The resource consumption for a 1000 iterations of each: ( a is the first column ) 17:38:42 poirot.jks.com - [EMAIL PROTECTED] SQL @run_stats NAME RUN1 RUN2 DIFF -- -- -- LATCH.Consistent RBA 0 1 1 LATCH.cache buffers lru chain 1 0 -1 LATCH.lgwr LWN SCN0 1 1 LATCH.mostly latch-free SCN 0 1 1 LATCH.session idle bit0 1 1 STAT...calls to get snapshot scn: kcmgss 3012 3013 1 STAT...calls to kcmgcs7 6 -1 STAT...cleanout - number of ktugct calls 0 1 1 STAT...consistent gets - examination 0 1 1 STAT...session cursor cache hits 1 2 1 STAT...parse count (total)1 2 1 STAT...opened cursors current 1 2 1 STAT...opened cursors cumulative 1 2 1 STAT...messages sent 0 1 1 STAT...free buffer requested 1 0 -1 STAT...execute count 1003 1004 1 STAT...deferred (CURRENT) block cleanout 4 3 -1 applications STAT...calls to kcmgas0 1 1 STAT...user commits 0 1 1 STAT...active txn count during cleanout 0 1 1 LATCH.enqueues0 1 1 LATCH.dml lock allocation 0 2 2 LATCH.session allocation 0 2 2 STAT...db block changes 25 27 2 STAT...enqueue releases 0 2 2 STAT...consistent gets 3010 3012 2 LATCH.cache buffers chains 6130 6133 3 STAT...redo entries 17 20 3 STAT...recursive cpu usage4 7 3 STAT...db block gets 30 33 3 LATCH.redo writing0 3 3 LATCH.undo global data1 4 3 LATCH.library cache 7 4 -3 LATCH.enqueue hash chains 0 4 4 LATCH.redo allocation18 22 4 LATCH.library cache pin 7 3 -4 LATCH.messages0 5 5 STAT...session logical reads 3040 3045 5 STAT...commit cleanouts 0 7 7 STAT...commit cleanouts successfully com 0 7 7 pleted STAT...redo size 27184 27820636 STAT...recursive calls 2004 3007 1003 42 rows selected. The for loop actually appears to be somewhat less expensive in terms of database resources. Jared On Sun, 2003-12-28 at 11:39, Carel-Jan Engel wrote: What I don't understand is the loop construction: Actually only one (row) is read form the cursor, and then the function is left with a return. Because it's an unconditional return, the code within the loop will either execute once, or never. When no data is found NULL is returned. When an error occurs NULL is returned as well. So, why a loop? Wouldn't it be better to have something like: create or replace function XYZ(gid in number) return varchar2 is l_c1 tab1.C1%TYPE;/* local variable to store C1 */ begin select c1 into l_c1 fromtab1 where id = gid; return l_c1; exception when no_data_found then return some_error_code; /* let the caller know that no data is found */ when others then return 'ERROR: '||TO_CHAR(SQLCODE); /* return the error-code, preceded by
