Maximum Cursors exceeded SQLException-- Configuration problem or Cursor leak?

Question

I am calling an SQL procedure through Java. I am getting SQL Exception in the logs while executing my code- java.sql.SQLException: ORA-01000: maximum open cursors exceeded

I had gone through similar questions and tried this-

increased open_cursors from 30000 to 40000.
closed the statement in try and finally block.

But it did not solve the problem. Is something wrong with my code?

Here is my Java code-

public static void buildingHelpContent(String p_id) throws Throwable{
        Connection conn = ExtractHP.getConnection();
        CallableStatement cs = null;
        log.debug("arguments for COMP.Help.build_hp_data  p_id=  "+p_id);
        try {
            cs = conn.prepareCall("{call COMP.Help.build_hp_data(?)}");
            cs.setString(1, p_id);
            cs.execute();
            if(cs!=null)            
                cs.close();

        } catch (SQLException e) {
            log = ExtractHP.getLogger();
            log.debug("!!! Java Exception !!!\n");
            log.error("Exception while executing the procedure for ID ...."+ p_id, e);          
        }
        finally{
            if(cs!=null)
                cs.close();
        }
    }

Now that you've added a close call to the finally block, I suggest you remove the close code from the try block. And as @user7294900 says, add if(conn!=null) conn.close(); to the finally block. — Bob Jarvis - Reinstate Monica
Hi @BobJarvis I have added conn.close(); in finally block. But getting below exception now: java.sql.SQLException: Closed Connection — Richa Sharma

user7294900 user7294900 · Accepted Answer · 2019-06-11T09:12:47

You didn't close the connection, you can use try-with-resources block (without finally):

    log.debug("arguments for COMP.Help.build_hp_data  p_id=  "+p_id);
    try (Connection conn = ExtractHP.getConnection();
        CallableStatement cs = conn.prepareCall("{call COMP.Help.build_hp_data(?)}")){

In java 6 also close connection in finally :

finally{
        if(cs!=null)
            cs.close();
        if(conn!=null)
            conn.close();
    }

Maximum Cursors exceeded SQLException-- Configuration problem or Cursor leak?

1 Answers