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)

Solution 1

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

Solution 2

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

        }

Solution 3

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;

Comments

[Shrikant Dande]

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]

:) actually code executes insert/update query more than 2000 times in one go . and thanx, I think this solution will work.

[Shrikant Dande]

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

[Justin Cave]

@ShrikantDande - Then you have some other cursor leak.

[Ammad]

@ShrikantDande try increasing some cursors as well. Also close statement object in finally block as well.