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.

Gnqz · Answer 1 · 2022-04-12T14:41:41.030

0

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.

edited Apr 12 '22 at 14:41

answered Apr 12 '22 at 12:55

Gnqz

3,292
3
25
35

I have tried this code also but still it is throwing the same error – Paakiya Apr 12 '22 at 14:05
Have you closed the already open cursors in the DB or restarted the DB? – Gnqz Apr 12 '22 at 14:29
No I didn't do anything in the DB. – Paakiya Apr 12 '22 at 14:33
I don't have an access to process in the DB. Is there any way to overcome from this issue in the code itself? – Paakiya Apr 12 '22 at 14:44
Don't think so, if it's different sessions that just remain hanging there might be a script that closes idle sessions, but you can't count on such. – Gnqz Apr 12 '22 at 14:50
Are sessions still up? – Gnqz Apr 12 '22 at 17:08
Yeah finally it is working .Missed to put try . After modified the code it is working. Thank you – Paakiya Apr 12 '22 at 17:56
You can mark as accepted answer so people will know it's of any use. – Gnqz Apr 12 '22 at 20:04

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

1 Answers1