Hi All,

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?

What driver are you using?

Are you closing everything in finally blocks? Are you able to step through your code or debug it to make sure its actually invoking close on the appropriate resources?

Just a few thoughts, maybe you've solved it already.

Check out this link (needs otn login):

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/java.817/a83724/tips3.htm#1004681


Memory Leaks and Running Out of Cursors

If you receive messages that you are running out of cursors or that you are running out of memory, make sure that all your Statement and ResultSet objects are explicitly closed. The Oracle JDBC drivers do not have finalizer methods. They perform cleanup routines by using the close() method of the ResultSet and Statement classes. If you do not explicitly close your result set and statement objects, significant memory leaks can occur. You could also run out of cursors in the database. Closing a result set or statement releases the corresponding cursor in the database.

Similarly, you must explicitly close Connection objects to avoid leaking and running out of cursors on the server side. When you close the connection, the JDBC driver closes any open statement objects associated with it, thus releasing the cursor objects on the server side.

I think the sort of driver you are using could be your problem.

We had a similar problem where we provided code for someone else to integrate but they were losing connections on there server and therefore blamed our code.

In the end it turned out they where using the standard Oraclce driver and we were using the thin driver.

I think you have to use the thin driver for connection pooling.

Its worth a try anyway.

Owen

Don't ask me why or how...But there is an undocumented solution for this problem from websphere,weblogic and oracle tech support. The solution is to wrap the connection close() statement in the finally within a try catch block.You would never catch an exception but the problem would be solved and all the cursors will be closed. Amazing, isn't it?

try
{
  con = creatConnection()
  .....
  ...
}
catch(exception ex)
{
 ....
}
finally
{
   try
   {
    statement.close()
    con.close()
   }
   catch(exception e){}
}

Make sure you also have a good look at your stored procedures - if you don't close your cursors there it will also cause trouble.

We have the same problem...
Be carefull, in a transaction, all the cursors are still open... even if you close them..., until the transaction it's commited/rollback...

This it's happend when the transaction it's too big...
Regards,

Hi,

Please ensure that u close the connection on each and every finally block of ur code.

This is due to the opening of a cursor for each and every dml commands to be executed in the db. In order to avoid it close the connection.

There is also a work around for this problem.

U hav to go to the DB and increase the cursor limit.