Xref: alice comp.databases.oracle.server:48320
Path: alice!news-feed.fnsi.net!newsfeed1.earthlink.net!nntp.earthlink.net!posted-from-earthlink!not-for-mail
From: "Stan" <sbrubaker@earthlink.net>
Newsgroups: comp.databases.oracle.server
Subject: Re: Got It!
Date: Wed, 5 May 1999 10:33:18 -0600
References: <7gmo5n$j9$1@birch.prod.itd.earthlink.net> <7gnr4i$65k$1@oak.prod.itd.earthlink.net> <37318237.34857051@192.86.155.100>
X-Posted-Path-Was: not-for-mail
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
X-ELN-Date: 5 May 1999 16:33:25 GMT
X-ELN-Insert-Date: Wed May  5 09:35:13 1999
Organization: EarthLink Network, Inc.
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
Lines: 29
Message-ID: <7gprsl$f3e$1@oak.prod.itd.earthlink.net>

Tom,

Unbelievable.  I set a break point before I did the commit in my Java
program and then did the queries you suggested.  The text showed both the
text I sent through the JDBC driver to call the stored procedures, and the
implicit cursors (INSERTs, etc) which were used in the PL/SQL.  When I
stepped past the commit, only the implicit cursors disappeared.  The JDBC
text stayed.

In Java, record sets, statements, database connections, etc are supposed to
do an automatic close when the variable falls out of scope or is otherwise
de-referenced.  I was using the CallableStatement local to a method which
called stored procedures thinking that this behavior would be true when the
method ended.  The JDBC thin driver apparently opens up a cursor for every
CallableStatement and keeps a reference to that statement until an explicit
close().  Once I added the statement.close() after the statement.execute(),
it worked.  I didn’t run into this until now because I was using regular SQL
calls rather than PreparedStatements.

I remember complaining to my computer screen (okay I was yelling) the other
day that I couldn’t understand why Oracle was using more than a few cursors
at a time in my package.  Sure enough, now it uses only a few cursors.  I
could set the OPEN_CURSORS value to 20 and it would work.

Thanks a million for helping me sleuth,

STAN  :^)