Cats, Pigeons and Open Cursors

From: Piet de Visser <piet.de.visser_at_logicacmg.com>
Date: Tue, 8 Jun 2004 11:13:58 +0200
Message-ID: <D1D50F9C5151D2119FF000104BB3F9B706DB9E3F@NL-ROT-MAIL02>

List,

We have a nice problem between Java and Pl/sql, and since PL/sql is slightly out of my comfort zone, I'm submitting it to the list (fairly sure we are not the first to stumble on this).

A procedure is used to open an number of ref-cursors and pass them back to java as out-parameters. Which cursors are opened varies and depends on in-parameters.

When Java is done using the cursors,
it Should close all the opened cursors.

Sometimes the closing is skipped or forgotten, and the nr-open-cursors increases rapidly. Since connections are rarely closed,
but rather re-used, the db runs out of open cursors.

Given the fact that we do not quite trust the Java code to always close all cursors, we would like to build a sure-fire way in Pl/sql to close all previously opened cursors.

First suggestion was to create another procedure that checks all cursors using ISOPEN%cursor123 and closes them. This doesn't work because REF-cursors can apparently not be declared outside of functions or procedures.

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 ?

Regards,

PdV

Tribute to All quotes on Developers, Cats, --
Statues and Pigeons. It is all True. --
Disclaimers are like Art --
You can read in them whatever you want --

This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.

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

Received on Tue Jun 08 2004 - 04:13:12 CDT