Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Cats, Pigeons and Open Cursors

RE: Cats, Pigeons and Open Cursors

From: Richard Ji <Richard.Ji_at_ztango.com>
Date: Tue, 8 Jun 2004 16:04:00 -0400
Message-ID: <E1732F21A9B99440B2117A3BEC76B91D01417E@ztangousexch.ad.ztango.com>


Craig Munday had a post about a tool he wrote to help you track down these type of things.

-----Original Message-----

From:	DENNIS WILLIAMS [mailto:DWILLIAMS_at_LIFETOUCH.COM]
Sent:	Tue 6/8/2004 9:33 AM
To:	'oracle-l_at_freelists.org'
Cc:	
Subject:	RE: Cats, Pigeons and Open Cursors 
Justin

    I agree with you 99%. My only quibble is with your statement "relatively easy to walk through the code". I won't pass myself off as a Java expert, but just want to point out that OOP like Java have a much more complex execution path than the older procedural languages most of us are familiar with. Transaction boundaries become very difficult to delineate, especially with event-driven GUI interfaces. Then there are things like J2EE that can handle database access automagically. Part of the value of modern development environments like Java is to utilize code others have written rather than writing every line of code yourself like most of us did in the older languages. I'm not denying that it may be necessary in some situations for the execution path to be traced in detail to determine the problem, just that the "relatively easy" may not be quite the case.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Justin Cave Sent: Tuesday, June 08, 2004 4:57 AM
To: oracle-l_at_freelists.org
Subject: RE: Cats, Pigeons and Open Cursors

I think you are running into a fundamental problem. Once you return a cursor to the Java application, the Java application owns it. Oracle doesn't generally allow you to come along and kill things owned by another session.

The only ways I'm aware of to close the cursor would be to do so in the Java application or to kill the entire session from PL/SQL. If your Java developers are moderately competent, it should be relatively easy to walk through the code and ensure that all the cursors are closed by the appropriate exception block. If there are a few corner cases where cursors are not closed, you can cycle connections from the connection pool or close long-open connections from PL/SQL. The former option will probably cause your Java application fewer problems.

Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Piet de Visser
Sent: Tuesday, June 08, 2004 3:31 AM
To: oracle-l_at_freelists.org
Subject: Cats, Pigeons and Open Cursors

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:

   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

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


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



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 - 15:02:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US