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