I am in the midst of developing a J2EE application. The application implements JSPs, Servlets, Session Beans, and Entity Beans. We are using Websphere 4.0.3 and the backend database is Oracle 8.1.5 and we use stored procedures heavily.
The problem we are currently experiencing is this: We continually get Maximum Number Of Cursors Exceeded. It seems as though a new cursor is opened for every call to a stored procedure and the cursor is never closed. I explicityle close all recordsets and callable statements on the java side.
I have increased the open_cursors parameter in the init.ora file on the server, however this is not a solution it is a temporary fix. I have also set the _closed_cached_open_cursors parameter = true. All this to no avail. Can anyone tell me if there is a solution to this problem? Does websphere have a problem handling cursors and thus stored procedures?
I know you say you have checked that every database object is properly closed, but I would recheck, as this problem comes from the effect of pooled database connections and unterminated cursors on the Oracle database; If that's not the problem you should suspect your JDBC driver is not performing the closing operation for out statements, and this is quite unlikely.
I would say 90% of the chance that your code is not closing the db connection at the end of the jdbc call. Make sure u put the connection.close statements in finally clause of ur jdbc code so that in any case, be it normal flow or exceptional flow, u close db conections.