I am getting the following Oracle error "maximum open cursors exceeded".
In a web application "weblogic 5.1 sp8, Oracle 8.06". I am explicitly closing the resultSet, the prepared statement and issuing a commit, but still the number of cursor and resources are not released.
You have to chance initxxx.ora file in Oracle configuration, you have to search parameter Open_Cursors = 300 (300 examples). Oracle default Open_Cursor= 50.
XXX = Database name
We had that parameter set to 500.
What I am looking for
1. How can I relinquish DBMS resources?
2. Any idea on how to demarcate (start and end) a transaction?
This seems to be a well-known issue with WLS/Oracle. You should search the BEA JDBC interest newsgroup, maybe someone there has come up with a definitive conclusion. We've encountered the same problem with WLS 5.1 (sp >= 6) and Oracle 8.1.6, running on Solaris. We used Oracle's type 2 and type 4 drivers.
Usually, the Oracle driver is blamed. But, from the newsgroup, I inferred that the problem also appeared with BEA's jDriver and I know it doesn't occur on other app servers, e.g IBM WAS 3.5 (so it may also be a problem of the WLS DataSource/Connection pool).
After downloading an updated version (Solaris only) of the 8.1.6 driver from the Oracle site and using the newest WLS 5.1 service pack, the problem seemed to have disappeared. On Windows, Oracle's 8.1.7 thin driver seemed to fix it. In both cases, I'm not 100 percent sure since I didn't bother anymore afterwards.
In conclusion, if you don't find a solution on the BEA site try to
1) Get the latest driver from the Oracle site (if you use an Oracle driver at all)
2) Put it before weblogic.jar in the classpath
3) Try using the OCI driver for connection pools
Hope, this helps