How to solve ORA-0300: maximum open cursors exceeded exception

Question

I have got ORA-0300: maximum open cursors exceeded exception

     public void connectionexample(String email)
     {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;        
        try {            
            conn = db.getConnection();            
            ps = conn.prepareStatement(select_query);            
            ps.setString(1, email);
            rs = ps.executeQuery();
            if(rs.next()) {
                  ps = conn.prepareStatement(update_query);
                  ps.setString(1, email);                  
                  ps.executeUpdate();                                    
            } else {
                ps = conn.prepareStatement(insert_query);                  
                ps.setString(1, email);
                ps.executeUpdate();             
            }            
        } catch(Exception e) {                    
            LOG.error("Exception occured ", e);                     
        } finally {                    
            DATABASE.release(rs);
            DATABASE.release(ps);
            DATABASE.release(conn);
        }      
    }

What does `DATABASE.release()` do? Does it actually close the result set/statement/connection? — MT0, May 11 '17 at 12:09
public final static void release(ResultSet statement) { try { if (statement != null) { statement.close(); } } catch (Exception e) { log.fatal("release: Error closing statement " + statement, e); } } — Vala Manoj, May 11 '17 at 12:15
public final static void release(PreparedStatement statement) { try { if (statement != null) { statement.close(); } } catch (Exception e) { log.fatal("release: Error closing statement " + statement, e); } } — Vala Manoj, May 11 '17 at 12:15
public final static void release(Connection statement) { try { if (statement != null) { statement.close(); } } catch (Exception e) { log.fatal("release: Error closing statement " + statement, e); } } — Vala Manoj, May 11 '17 at 12:15
Please **edit your question** to include further information. Code snippets in comments are hard to read. — APC, May 11 '17 at 12:19

score 2 · Answer 1 · answered May 11 '17 at 12:12

You get this error because you are using the same PrepapredStatement multiple time :

ps = conn.prepareStatement(select_query);//<<----------------------Here
...
if (rs.next()) {
    ps = conn.prepareStatement(update_query);//<<-------------------Then here
    ...
} else {
    ps = conn.prepareStatement(insert_query);//<<-------------------or here
    ...
}

To solve your problem you have to close your statement and create a new one for the new query, or for better way, separate the actions in a different methods which each one create and release its statement and connection.

score 1 · Accepted Answer · answered May 11 '17 at 12:24

You are re-using the prepared statement ps:

public void connectionexample(String email)
{
  Connection conn = null;
  PreparedStatement ps = null;
  PreparedStatement ps2 = null; -- Use another variable for the second prepared statement
  ResultSet rs = null;        
  try {            
    conn = db.getConnection();            
    ps = conn.prepareStatement(select_query);            
    ps.setString(1, email);
    rs = ps.executeQuery();
    if(rs.next()) {
      ps2 = conn.prepareStatement(update_query); -- Assign this to the second variable
    } else {
      ps2 = conn.prepareStatement(insert_query);                  
    }            
    ps2.setString(1, email);
    ps2.executeUpdate();             
  } catch(Exception e) {                    
    LOG.error("Exception occured ", e);                     
  } finally {                    
    DATABASE.release(rs);
    DATABASE.release(ps);
    DATABASE.release(ps2);  -- Make sure you also close the second prepared statement
    DATABASE.release(conn);
  }
}

However, you could get rid of the second round-trip to the database by using the SQL MERGE statement.

How to solve ORA-0300: maximum open cursors exceeded exception

2 Answers2