0

Getting ORA-01000: maximum open cursors exceeded from Java Application. I checked also, after every iteration, the cusrsor size keep on increasing. At some point of time, when it exceeds the max cusrsor size getting the exception.

public void processData(List<String> list)
{
    PreparedStatement pstmt = null;
    ResultSet resultData = null;
    Connection conn = myConnection.getConnection();
    for( int i=0; i<list.size(); i++)
    {
        try {

            String sqlQuery = list.get(i);
            PreparedStatement pstmt = conn.prepareStatement(sqlQuery, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet resultData = pstmt.executeQuery();
            // Then processing the data.
            } catch (Exception e) {
            e.printStackTrace();
            } 
            finally {
            if( resultData != null )
                resultData.close();
            if( pstmt != null)
                pstmt.close();
        }
    }
}

I have multiple jvm instances and utilizing the same connections. After every closing of resultset and statement, still the cursor size keeps on increasing. When I am running more than 5 jvm instances, getting the same error. But its not consistent.

John
  • 145
  • 1
  • 9
  • 1
    Does this even compile? You use `resultData` and `pstmt` in your `finally` block but their scope is only inside of the `try` block. Anyways, this is a duplicate of https://stackoverflow.com/a/12246631/10272286 – SizableShrimp Jan 29 '19 at 06:14
  • Sorry for typo error. Modified the code – John Jan 29 '19 at 07:22
  • @John `Connection conn` object must be closed in the finally block along with `PreparedStatement` and `ResultSet` – Jacob Jan 29 '19 at 07:25
  • Instead of closing the connection, we are returning the connection to corresponding connection pool. Also is there any way I can opt through which i can tell database to release the cursor after closing of ResultSet. – John Jan 29 '19 at 09:04

0 Answers0