java.sql.SQLException:ORA -01000:maximum open cursors exceeded in JAVA

Question

I am getting this error when I run my code

Below is the code I have used:

String sql="SELECT * FROM PERSONS WHERE PERSONJOB='ADMIN'";
Statement stmt=null;
ResultSet rs=null;
try
{
    stmt=conn.createStatement();
    rs=stmt.executeQuery(sql);
    While(rs.next())
    {
        String name=rs.getString(1);
        long id=rs.getLong(2);
        System.out.println(name);
        System.out.println(id);
    }
}
catch(Exception e)
{
    throw e;
}
finally
{
    rs.close();
    stmt.close();
}

I want to reuse the connection, so I didn't close the connection. After I closed the ResultSet and Statement, I am getting the "maximum open cursors exceeded" error. Anyone please help me to solve this error.

Answer 1

My guess is that the close() of your ResultSet is failing, which would result in multiple open cursors and eventually hit the max configured open cursor count. Could you modify your code to:

Statement stmt = conn.createStatement();
try 
{
  ResultSet rs = stmt.executeQuery("SELECT * FROM PERSONS WHERE PERSONJOB = 'ADMIN'");
  try 
  {
    while ( rs.next() ) 
    {
      String name = rs.getString(1);
      long   id   = rs.getLong(2);
      System.out.println(name);
      System.out.println(id);
    }
  } 
  finally 
  {
    try 
    { 
      rs.close(); 
    } 
    catch (Exception ignore) { }
  }
} 
finally 
{
  try 
  { 
    stmt.close(); 
  } 
  catch (Exception ignore) { }
}

EDIT: will be good to also clean up the already opened cursors with a combination of:

 SELECT oc.user_name, oc.sql_text, s.SID, s.SERIAL#
   FROM v$open_cursor oc
      , v$session     s
  WHERE oc.sid = s.sid
    
 EXEC SYS.KILL_SESSION(xxx,xxxxx); 

or restart the DB.