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).