RE: Cats, Pigeons and Open Cursors

From: "Craig Munday" <Craig.Munday@xxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 17 Jun 2004 15:14:30 +1000
Nuno,

What happens to the Java clients that may have a reference to the REF
CURSOR that the procedure has just closed?  If a Java client has called
a procedure and a Ref has been returned, whose responsibility should it
be to close the ResultSet created?  I would have thought that it was the
client's responsibility?

Craig Munday
Software Development Manager
Giesecke & Devrient Australasia P/L, 32 Birnie Ave, Lidcombe 2141
Phone: +61 2 9646 6200 Fax: +61 2 9749 1678
Craig.Munday@xxxxxxxxxxxx http://www.gdaus.com.au

-----Original Message-----
From: Nuno Souto [mailto:dbvision@xxxxxxxxxxxxxxx] 
Sent: Tuesday, 8 June 2004 8:03 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Cats, Pigeons and Open Cursors

Piet de Visser allegedly said,on my timestamp of 8/06/2004 7:13 PM:
> Question:
>  - Is there a way to refer to ref-cursors inside a package,
>    and to have a list if ref-cursors closed conditionally
>    by using : IF refcur1%ISOPEN  THEN CLOSE refcur1 ; END IF; ?
> 
>    More precisely, we want the package to keep track of 
>    all cursors it has opened (we can keep a list, no problem
>    if that requires some extra code). We then want the same
>    procedure or another procedure to go in, read the list 
>    of open cursors, an close any that are still open.
> 
> Is this feasible ?
> Suggestions anyone ?

Well, there are probably a zillion solutions.  The one I have used
quite successfully is as follows:

1- in the package declaration, declare a type of REF CURSOR.
Like this:
type zot_the_cursor is REF CURSOR;
and declare all your functions to return this type. As in:
function blah (par1 in number) return zot_the_cursor;
2- In the package body, for every function start it with:
IF zot_the_cursor%ISOPEN then
   close zot_the_cursor;
END IF;
just like you suggested.

It worked for me.  Release 8.1.7.4.

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision@xxxxxxxxxxxxxxx
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
RE: Cats, Pigeons and Open Cursors

Other related posts:
