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

Question

I am getting this above ORA-01000 SQL exception.

In my DB a table having 1500 records and I am trying to insert those values. while inserting into another table I got this error.

java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:796) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1031) at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:836) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1124) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368) at com.exceloid.newdb.NewDBInsertion.insertCLocation(NewDBInsertion.java:1239) at com.exceloid.dbmigration.CBPartnerImpl.getCLocationData(CBPartnerImpl.java:1198) at com.exceloid.serviceimpl.CBPartnerServiceImpl.doProcess(CBPartnerServiceImpl.java:22) at com.exceloid.controller.HomeController.cBPartner(HomeController.java:272) at com.exceloid.controller.HomeController.welcomePage(HomeController.java:37) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827) at javax.servlet.http.HttpServlet.service(HttpServlet.java:617) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812) at javax.servlet.http.HttpServlet.service(HttpServlet.java:723) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489) at java.lang.Thread.run(Thread.java:745)

here is my code:

Connection connection = null;    
PreparedStatement pst = null;     
ResultSet result = null;    

try {      
            connection = newDbConnection(connection);     
            if(connection != null){      
                 for (CLocation cpayment : list) {    

                        cLocationId = cpayment.getcRegionID();    
                        adClient = cpayment.getAdClientID();    
                        adOrg = cpayment.getAdOrgID();    
                        updated = cpayment.getUpdated();     
                        updatedBy = cpayment.getUpdatedBy();    
                        created = cpayment.getCreated();    
                        createdBy = cpayment.getCreatedBy();    
                        isActive = cpayment.isActive();    
                        city = cpayment.getCity();   
                        postal = cpayment.getPostal();    
                        postalAdd = cpayment.getPostalAdd();    
                        cCountryId = cpayment.getcCountryID();    
                        cCityId = cpayment.getcCityID();    
                        address1 = cpayment.getAddress1();    
                        address2 = cpayment.getAddress2();    
                        cRegionId = cpayment.getcRegionID();   
                        regionName = cpayment.getRegionName();   

                        if(isActive == true) {    
                            InsertIsActive = "Y";    
                        } else {   
                            InsertIsActive = "N";    
                        } 

                        if(ad.equals(adClient)){    
                            final String sql = "SELECT * FROM c_location  where c_location_id = '"+cLocationId+"'";    
                            pst = connection.prepareStatement(sql);    
                            result = pst.executeQuery();    
                            boolean exist=result.next();    
                            if(exist){   
                                 System.out.println("already table data exists");    
                                 flag = true;    
                            } else {    
                                 String sql1="INSERT INTO c_location " + "    VALUES ('"+cLocationId+"', '"+client+"', '"+org+"', '"+InsertIsActive+"', '"+created+"', '"+createdBy+"', '"+updated+"', '"+updatedBy+"', '"+address1+"', '"+address2+"', '"+city+"', '"+postal+"', '"+postalAdd+"', '"+cCountryId+"', '"+cRegionId+"', '"+cCityId+"', '"+regionName+"')";   
                                 System.out.println(sql1);    
                                 pst=connection.prepareStatement(sql);    
                                 pst.executeUpdate();   
                                 flag = true;   
                            }    
                        } else {    
                            System.out.println("new db does not match adclient ID");    
                        }    
                 }     
            }     
        } catch (SQLException e) {    
            e.printStackTrace();     
        } finally {     
            if(result != null){     
                try{   
                    result.close();    
                }catch(Exception e){}    
            }    
            if(pst != null){    
                try{    
                    pst.close();    
                }catch(Exception e){}     
            }    
            if(connection != null){   
                try {    
                    connection.close();    
                } catch (SQLException e){}   
            }    
        }   

I have closed all the connections Still I face this error.

I have executed this command in my oracle db console for increasing the no of cursors.

ALTER SYSTEM SET OPEN_CURSORS=1337 SID='*' SCOPE=BOTH;

But the same error occurs again.

Answer 1

Rewrite your code to use try-with-resources construct.

See: https://blogs.oracle.com/WebLogicServer/entry/using_try_with_resources_with

Also use bind variables. It will be shorter, safer and even faster.

Answer 2

You are only closing the last SQL statement.

You are looping over all CLocation, open up a Preparedstatement for each of them without closing the single ones. Try closing the Preparedstatement within your loop after executing it, since each of them is creating a new databasecursor on the databaseside.

Answer 3

Change your SQL statement to use a bind parameter and move is out of the loop

final String sql = "SELECT * FROM c_location  where c_location_id = ?";    
pst = connection.prepareStatement(sql);

Inside the loop you set the bind parameter

pst.setString(1, cLocationId);

The same you should do for the INSERT statement.