open cursors and prepared statement cache size

[smaring]

I'm running JBoss 3.2.2 with Oracle 8.1.7 (+ 8.1.7.4 patch) on Windows with JDK 1.4.2_03

My init.ora file had "open_cursors = 300" and my app would, after running many queries, eventually crash with "ORA-01000 maximum open cursors exceeded". I am 100% confident that all of my ResultSets and PreparedStatements were being closed properly. I figured my only saving grace was in the connection.close(), but that does not kill the session with Oracle, it only puts the connection back into the JBoss pool.

I then added:
<prepared-statement-cache-size>100</prepared-statement-cache-size>

to my oracle-ds.xml in hopes that JBoss would properly recycle the connection when the prepared statement count got too high. However, what happens now is that on the very first occurance of a PreparedStatement execution I get:
java.sql.SQLException: Closed Statement

and I am 100% certain that my code is not prematurely closing the statement.

Anybody know what is going on here?

Thanks.
Steve Maring
Tarpon Springs, FL

[smaring]

Well, I removed the "<prepared-statement-cache-size>" element from my oracle-ds.xml and configured <min-pool-size> to 10, instead of 1. My <idle-timeout-minutes> is 1

So far it seems to be doing the trick. I think what is happening is that my connections that get put back in the pool are sitting around idle long enough to be recycled and have their Oracle session, along with the cursors, KILLED like the dirty rotten bastards that they are!

hehehe