oracle java.sql.SQLException: ORA-01000: 超出最大打开游标数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28235626/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
提问by kumar
I am getting this above ORA-01000 SQL exception.
我在 ORA-01000 SQL 异常上面得到了这个。
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.
在我的数据库中有 1500 条记录的表，我正在尝试插入这些值。在插入另一个表时，我收到了这个错误。
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)
java.sql.SQLException: ORA-01000: 超出最大打开游标数
在 oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) 在 oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) 在 oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) ) 在 oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743) 在 oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213) 在 oracle.jdbc.driver.T4CPreparedDescribement(StateCPreparedDescribement.StateTCPreparedStatement. 796) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1031) at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:836) at oracle.jdbc.driver.OracleStatement.doStateExecuteWithTimeout(OracleStatement.doStateExecuteWithTimeout) :1124) 在 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.mplration.CBInermigPart .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) 在 org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) 在 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.方法。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) 在 org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827) 在 javax.servlet.http.HttpServlet.service(HttpServlet.java) :617) 在 org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812) 在 javax.servlet.http.HttpServlet.service(HttpServlet.java:723) 在 org.apache.catalina.core.ApplicationFilterChain。 org.apache 中的 internalDoFilter(ApplicationFilterChain.java:290)。catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) 在 org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) 在 org.apache.catalina.core.StandardContextValve.invoke(Standard.javaContextValve) 191) 在 org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) 在 org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) 在 org.apache.catalina.core.StandardEngineVal .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) 在 org。apache.tomcat.util.net.JioEndpoint$Worker.run(JioEndpoint.java:489) 在 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.
我已经在我的 oracle db 控制台中执行了这个命令来增加游标的数量。
ALTER SYSTEM SET OPEN_CURSORS=1337 SID='*' SCOPE=BOTH;
But the same error occurs again.
但同样的错误再次发生。
回答by SomeJavaGuy
You are only closing the last SQL statement.
您只是关闭最后一条 SQL 语句。
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.
您正在遍历所有 CLocation，为每个 CLocation 打开一个 Preparedstatement 而不关闭单个。尝试在执行后关闭循环中的 Preparedstatement，因为它们中的每一个都在数据库端创建一个新的数据库游标。
回答by SubOptimal
Change your SQL statement to use a bind parameter and move is out of the loop
更改您的 SQL 语句以使用绑定参数并且移动超出循环
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.
你应该对 INSERT 语句做同样的事情。
回答by ibre5041
Rewrite your code to use try-with-resources construct.
重写您的代码以使用 try-with-resources 构造。
See: https://blogs.oracle.com/WebLogicServer/entry/using_try_with_resources_with
请参阅：https: //blogs.oracle.com/WebLogicServer/entry/using_try_with_resources_with
Also use bind variables. It will be shorter, safer and even faster.
也使用绑定变量。它会更短、更安全、甚至更快。
