RE: Cats, Pigeons and Open Cursors

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@au.gi-de.com http://www.gdaus.com.au

-----Original Message-----
From: Nuno Souto [mailto:dbvision_at_optusnet.com.au] Sent: Tuesday, 8 June 2004 8:03 PM
To: oracle-l_at_freelists.org
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_at_optusnet.com.au
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------