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 - Слава Україні, Jun 11 '19 at 11:28
The answers to [this question](https://stackoverflow.com/questions/12192592/java-sql-sqlexception-ora-01000-maximum-open-cursors-exceeded) might help you — Joakim Danielson, Jun 11 '19 at 13:26
Hi @BobJarvis I have added conn.close(); in finally block. But getting below exception now: java.sql.SQLException: Closed Connection — Richa Sharma, Jun 24 '19 at 09:06

Ori Marko · Answer 1 · 2019-06-11T09:18:48.663

1

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

edited Jun 11 '19 at 09:18

answered Jun 11 '19 at 09:12

Ori Marko

56,308
23
131
233

Thanks for the reply. Unfortunately, this code is very old and is written in Java 1.6. Cannot use try-with-resources. – Richa Sharma Jun 11 '19 at 09:17
@RichaSharma see my updated answer, add `conn.close();` to `finally` – Ori Marko Jun 11 '19 at 09:19
+1 for your suggestion @user7294900. After adding the conn.close() in finally block, now I am getting a new exception: java.sql.SQLException: Closed Connection Is there any problem in the Java code with Throw and Throwable? – Richa Sharma Jun 24 '19 at 09:13
@RichaSharma I suggest you open a new question with code inside `ExtractHP.getConnection()` – Ori Marko Jun 24 '19 at 10:05

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

1 Answers1