Open Cursor

[roshan]

Eventhough I close the resultSet, statement and connection explicitly in the code, I am getting the open cursor problem.
Does JBoss cache statements, and can it be a reason for this open cursor problem?
It would be great if anyone could help me in this regard.

Roshan

[jfair]

This is how I solved my open cursors problem. I was using Oracle 8.1.7 and jboss 3.0.4.

(1) The OPEN_CURSORS parameters in your initSID.ora file contains the maximum number of open cursors per user. Check what this value is for your database by looking in the .ora file.

(2) Set breakpoints in your code on each PreparedStatement.execute line (I'm assuming you're using an IDE such as Eclipse). Step through your code and ensure you're closing all prepared statements - I suggest using a finally block so that statements are always closed. In a SQL tool (like SQLPlus), log on as user sys and execute the following query while you're debugging:

select * from v$parameter
where name = 'open_cursors'

This will tell you the current number of open cursors and help you figure out what parts of your code aren't properly closing prepared statements etc... By closing a prepared statement, the underlying recordset is also closed.

jfair

[matthewharrison]

We have the same problem (Oracle 8.1.7, JBoss-2.4.4 - yes we're hoping to upgrade imminently)
We are definitely closing everything (resultset, statement, connection) in finally blocks.
Our DB guys found the problem lies in Oracle: if a prepared statement returns a resultset, Oracle keeps the cursor open until the connection is closed - really closed.
Since the connection is pooled - closing it merely returns the connection to the pool, and the cursor stays open.
We tried setting GCEnabled to true, to make JBoss close idle connections. But JBoss failed to re-open the connections later. After a while the app ground to a halt because it had no connections and cuoldn't get any more...
Has this been fixed in a later version?

(& is there a bug tracker for JBoss we can look at?
I can't seem to find it)

rgds,

Matthew Harrison.

[matthewharrison]

Actually, each resultset is closed directly after use.
the statement and connection are (only one of each) closed in the finally block.

[davidmboon]

this problem seems to be related to prepared-statement-cache-size in your datasource configuration, normally not set...adjust this to something reasonable, or exactly to the same value as the value of 'open_cursors' and you should be set.