How to minimize occurences of SQLExceptions due to ORA-01000 error?

Problem:

• Product Name: BES App Server
• Product Version: 5.x & 6.x
• Product Component: JDBC Connection Pool & EJB Container
• Platform/OS Version: All
• JDK/Compiler Version: Same versions bundled with BES

This article provide suggestions to minimize (if not prevent) the occurrences of SQL Exception thrown as a result of exhausting the Oracle's open cursor limit (ORA-01000 error)?

Resolution:

When the specified limit for open cursors is exceeded, Oracle database server reports an ORA-01000 error. BES App Server would, in turn, wrap up this low-level Oracle error as an SQLException and throw to the client.

Though you can simply increase the "OPEN_CURSORS" limit to avoid this problem, it would not be a permanent solution in most cases, as it just covers the root cause instead of eliminating.

Let us explore the common causes of the problem and possible solutions:

Abandoned JDBC Objects

The most pervasive bad code practice of not releasing the JDBC resources is the most common cause of this problem. For instance, the "Statement" objects are not properly released.

Always ensure that all JDBC objects, PreparedStatements in particular, are properly closed under normal and exceptional cases. i.e., close them in your "finally" block as shown below:

try
 {
     conn = getConnection();
     stmt = conn.prepareStatement(sql);
     // do your database stuff here
     // make sure to close resources once done with them
 }
 catch  (Exception exc)
 {
     // Handle your exceptions here
 }
 finally
 {
     try
     {
         if(rs != null)
         {
             rs.close();
             rs =  null;
         }
     }
     catch  (SQLException sqlexc) { // no-op }
     try
     {
         if(stmt != null)
         {
             stmt.close();
             stmt =  null;
         }
     }
     catch  (SQLException sqlexc) { // no-op }

     try
     {
         if(conn != null)
         {
             conn.close();
             conn =  null;
         }
     }
     catch  (SQLException sqlexc) { // no-op }
 }

 
Also explicitly release the objects, once finished with them, inside an iterative code block like for-loop

Inadequate OPEN_CURSORS Limit

Sometimes the default limit (50) may not be enough for your application. In such a case, with the help of your Oracle DBA, you can try to estimate the optimum value for "OPEN_CURSORS" parameter in your init*.ora file, by querying the number of open cursors for the session under load.

Miscalculated or Exuberant Statements Caching

Statement Cache in BES JDBC Connection Pool should be accordingly sized with respect to the Oracle"s OPEN_CURSORS limit. You can adjust the size of the cache with the help of BES Connection Pool property "maxPreparedStatementCacheSize".