如何解决ORA-0300：最大打开游标超过异常[重复]

Question

我得到了ORA-0300：超出了最大打开游标数

     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);
        }      
    }  

Answer 1

您正在使用准备好的语句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);
  }
}

但是，通过使用SQLMERGE语句，可以避免第二次往返数据库。

Answer 2

您会得到这个错误，因为您多次使用相同的PrePapred语句：

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

为了解决问题，您必须关闭语句并为新查询创建一个新语句，或者为了更好的方式，在不同的方法中分离操作，每个方法创建并释放其语句和连接。