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:peter.gram_at_miracleas.dk>
>
> 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: peter.gram_at_miracleas.dk <mailto:peter.gram_at_miracleas.dk>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guang Mei
  INET: gmei_at_incyte.com

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