CODEWITHSUNDEEP
javasqloracle-databasedatabase-connectionsqlexception
Richa Sharma
Richa Sharma

Reputation: 75

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

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-

  1. increased open_cursors from 30000 to 40000.
  2. 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();
        }
    }

Upvotes: 1

Views: 203

Answers (1)

Ori Marko
Ori Marko

Reputation: 58892

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

Upvotes: 1

Related Questions