maximum open cursors exceeded exception in java code

Question

this my code to execute update query

public  boolean executeQuery(Connection con,String query) throws SQLException
    {
            boolean flag=false;
        try
        {
            Statement st = con.createStatement();
            flag=st.execute(query);
            st.close();
            st=null;
            flag=true;


        }
        catch (Exception e) 
        {
            flag=false;
            e.printStackTrace();
            throw new SQLException(" UNABLE TO FETCH INSERT");          
        }
        return flag;
    }

maximum open cursor is set to 4000 code is executing update tableA set colA ='x',lst_upd_date = trunc(sysdate) where trunc(date) = to_date('"+date+"','dd-mm-yyyy') update query for around 8000 times

but after around 2000 days its throwing exception as "maximum open cursors exceeded"

please suggest code changes for this. @TimBiegeleisen here is the code get connecttion

public  Connection getConnection(String sessId)
    {

        Connection connection=null;

         setLastAccessed(System.currentTimeMillis());

        connection=(Connection)sessionCon.get(sessId);

            try 
            {
                if(connection==null || connection.isClosed()  )
                {

                if ( ds == null )
                {
                    InitialContext ic = new InitialContext();
                    ds = (DataSource) ic.lookup("java:comp/env/iislDB");
                }

                connection=ds.getConnection();

                sessionCon.put(sessId, connection);

                }
            }
            catch (SQLException e) 
            {
                e.printStackTrace();    
            } 
            catch (Exception e) 
            {
                e.printStackTrace();
            }


        return connection;
    }

`

error stack is as bellow

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
    at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:118)
    at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:472)
    at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:499)
    at oracle.jdbc.driver.OracleConnection.privateCreateStatement(OracleConnection.java:683)
    at oracle.jdbc.driver.OracleConnection.createStatement(OracleConnection.java:560)
    at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.createStatement(DelegatingConnection.java:257)
    at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.createStatement(PoolingDataSource.java:216)
    at com.iisl.business.adminbo.computeindex.MoviIndexComputeBO.calculateMoviValue(MoviIndexComputeBO.java:230)

You never read the result and **you never close the Connection** — Jens, Sep 27 '16 at 05:11
Also, if an exception occurs, then you never close the `Statement` object. Should be done in a finally block or a try/resource — Michael Markidis, Sep 27 '16 at 05:13
If you are able to incorporate the above mentioned changes, go through [this](http://stackoverflow.com/questions/12192592/java-sql-sqlexception-ora-01000-maximum-open-cursors-exceeded) question for a very detailed explanation on the cause of this issue. — N00b Pr0grammer, Sep 27 '16 at 05:18
Yes, but in order for `AutoCloseable` to do what you think it does, you need to enclose it into a try-with-resources statement. — Joe C, Sep 27 '16 at 05:31
@JoeC This was in response to a comment saying `try-with-resources` was introduced in java8 — Scary Wombat, Sep 27 '16 at 05:44
@ScaryWombat try-with-resources exists since java 7, time passes. — Joop Eggen, Sep 27 '16 at 06:36

Ammad · Answer 1 · 2016-09-27T23:43:30.613

1

One of quickest solution is to increase cursor that each connection can handle by issuing following command on SQL prompt:

alter system set open_cursors = 1000

Also, add finally block in your code and close the connection to help closing cursors when ever exception occurs.

Also, run this query to see where actually cursor are opened.

select  sid ,sql_text, count(*) as "OPEN CURSORS", USER_NAME from v$open_cursor



finally {

     if (connection!=null) {

     connection.close();

        }

edited Sep 27 '16 at 23:43

answered Sep 27 '16 at 06:35

Ammad

4,031
12
39
62

Hi thanx for your reply , but I am getting conncection object through session id and using the same connection object in entire BO class. so if I close the connection and some other method doesnt call getconnection method it will throw nullpointer. And as this is legecy system I can not change code in every component. – Shrikant Dande Sep 27 '16 at 07:23
@ShrikantDande try increasing some cursors as well. Also close statement object in finally block as well. – Ammad Sep 27 '16 at 23:41

score 1 · Answer 2 · answered Sep 27 '16 at 07:09

Your code has a cursor leak. That's what is causing the error. It seems unlikely that your code can really go 2000 days (about 5.5 years) before encountering the error. If that was the case, I'd wager that you'd be more than happy to restart a server twice a decade.

In your try block, you create a Statement. If an exception is thrown between the time that the statement is created and the time that st.close() is called, your code will leave the statement open and you will have leaked a cursor. Once a session has leaked 4000 cursors, you'll get the error. Increasing max_open_cursors will merely delay when the error occurs, it won't fix the underlying problem.

The underlying problem is that your try/ catch block needs a finally that closes the Statement if it was left open by the try. For this to work, you'd need to declare st outside of the try

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

:) actually code executes insert/update query more than 2000 times in one go . and thanx, I think this solution will work. — Shrikant Dande, Sep 27 '16 at 09:37
I tried this now its showing following error java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1 ORA-01000: maximum open cursors exceeded — Shrikant Dande, Sep 27 '16 at 10:00

score 1 · Answer 3 · answered Sep 27 '16 at 07:40

As mentioned in another response you will leak cursors if an exception is thrown during the statement execution because st.close() won't be executed. You can use Java's try-with-resources syntax to be sure that your statement object is closed:

    try (Statement st = con.createStatement())
    {
        flag=st.execute(query);
        flag=true;
    }
    catch (Exception e) 
    {
        flag=false;
        e.printStackTrace();
        throw new SQLException(" UNABLE TO FETCH INSERT");          
    }
    return flag;

maximum open cursors exceeded exception in java code

3 Answers3