I am not an Oracle guy, so can you tell me what causes this problem. I can say that the JBossCMP code opens a statement, executes, loads the results and closes the statement all within one method, but my guess the problem is not leaks.

Transaction creation depends on how you have configured the transaction attributes in the ejb-jar.xml. By default you get a transaction for each create, update, get, remove. I suggest you use a wrapping session facade or manage the transactions your self with a UserTransaction.

For each statement JBossCMP closes the statement, resultset and connection immediately after reading the data.

Are you actually getting a cursor leak?

Try writing a simple benchmark and see if it leaks from one run to the next. You may be able to track this down. Also are you using XA? XA has ver different preformance charistics then a standard JDBC connection.

About 18 months ago I ran into a similar problem with running out of cursors in Oracle in a stand-alone Java application. I tried closing each statement directly after use and throwing away the reference, but nothing worked. I never got resolution from Oracle, but it has been quite some time.

Anything yet? Anybody? A workaround? An appropriate sacrifice to the computer gods?

Seriously, this problem is a showstopper. We're trying to load several thousand entities using the container, and I get through maybe 30 before I run out of cursors. I've looked through the JBoss code and it does indeed look as if the result set and prepared statement are being closed shortly after they're being created (in a finally block, so it can't be missed).

My boss is threatening me with BEA. Help! :)

Are you using the absolute latest JDBC driver from Oracle?

It is easy to identify because it contains a manifest with the following content (together with a lot of other stuff):

Oracle JDBC Driver version - 9.0.2.0.0

We're working with thousands of entities and Oracle without this kind of problem.

We had a similar issue, don't know if this will help...

We traced our code back to the connection initialisation, and found a statement to set the date format. The comment next to the code said "Don't know why we need to do this really, but anyway...."

I guess it's in some standards book somewhere - the guy who wrote the code wouldn't have done it on purpose.

This statement grabbed a cursor and was not closed in the finally block.

Once the connection was set up, 'control' returned to the main classes, and any reference to the date format statement was lost. Obviously, it could no longer be shut.

Btw, we tried upgrading the jdbc drivers to the latest, but the problem was code in the end.....

As I say, I don't know if this will help - I hope so, and maybe it might prompt you to look somewhere similar and hopefully find your issue.

Regards,

Bruce

It may be worthwhile tracing this from the other end, by
getting Oracle to tell you who opened these cursors and
what query they are executing. A capable DBA should be
able to do this. See for example the script on this page:
http://www.think-forward.com/sql/opencur.htm