RE: Cats, Pigeons and Open Cursors

To: <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 17 Jun 2004 09:49:48 +1000
Justin,

I tend to agree with what you are saying in your email below.  In my
experience the most resource leaks that I have encountered occur when
exceptions are thrown in the code, and they have not been cleaned up
correctly.  These are relatively straight forward to pick in a code
review if a development team has adhered to the coding style that you
have suggested in your email.

The other case that is much harder to find is when a developer has
re-used a variable that was referring to an open resource.  Here is an
example,

declare r as a result set
try
{

r = statement1.execute()

<< process ResultSet r >>

r = statement2.exceute()

<< process ResultSet r >>

}
catch (SQLException e)
{
   << handle exception >>
}
finally
{
  if (r != null )
     r.close();
}

The reference to the open ResultSet generated by statement1.execute() is
lost when statement2.execute() is invoked.  That is, a resource leak has
occurred even though variable r is closed in the finally clause.

Like you I'm also a fan of the exception management features in modern
languages and believe that they can help handle and manage errors.  Here
is an alternative to the code sample that you provided below.  It
removes the need to code the close routines twice and makes the code a
little more resilient to resource leaks.

try
{
  << open cursor >>

  << process results >>

}
catch (SQLException e )
{
  << Handle the exception in some way - log it, perform cleanup etc. >>
  << throw further exception if required >>
}
finally
{
   <<close anything you opened>>
}

The finally clause will always get executed, even if a further exception
is thrown in the catch clause.

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: Justin Cave [mailto:justin@xxxxxxxxxxx] 
Sent: Wednesday, 9 June 2004 5:02 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Cats, Pigeons and Open Cursors 

is something I fancy myself to be.  I probably should have further
qualified
the statement to say that it is relatively easy to ensure that cursors
are
closed in virtually every case.  Handling every possible run-time
exception
is probably tough to do, but hopefully you don't regularly run out of
memory
in the JVM.

I would argue that it is a heck of lot easier with OO languages to
handle
errors than it was in the old procedural days.  Of course, just like
those
of us whose mothers made us clean our rooms, those of us that learned
how to
do things like free our own memory have some advantages over developers
that
were trained that the language would collect all their garbage.  With
languages like Java, you can almost always end up with something like

try {
  << open cursor >>

  << process results >>

  << close cursor >>
}
exception(SQLException e ) {
  <<close anything you opened>>
}

Which is a lot easier to deal with than trying to remember to check 15
different return values to check for an error code.

Sure, there are some persistence frameworks that abstract all this away,
but
those are generally well written to do this sort of thing internally.
Your
application may have to indicate to the framework that you are done with
the
results of the query, but at that point you've basically got the same
problem as above of making sure you have to appropriate exception
handler in
place.

I guess my point is, if the Java side of the house is complaining that
they
can't possibly be expected to close all the cursors in a custom
application,
they are either
- Making up excuses to get out of work
- Not particularly well trained in enterprise Java development
- Using alpha frameworks that they found on some random web page, using
frameworks they wrote themselves, poorly, or using frameworks that they
haven't taken the time to learn properly
- In need of some education about resource management, and enterprise
databases
- Operating under the belief that there is a magical "garbage collect"
call
that you can make in the database that will fix the problem rather than
having to look through source code for bugs.  


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

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of DENNIS WILLIAMS
Sent: Tuesday, June 08, 2004 7:45 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Justin Cave
Sent: Tuesday, June 08, 2004 4:57 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Piet de Visser
Sent: Tuesday, June 08, 2004 3:31 AM
To: oracle-l@xxxxxxxxxxxxx
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:
 - 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@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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
