gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-01 18:18:37 26 4
gpt4 key购买 nike

我在 ORA-01000 SQL 异常之上遇到了这个问题。

在我的数据库中有一个包含 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)

这是我的代码:

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){}
}
}

我已经关闭了所有连接,但我仍然面临这个错误。

我已在 Oracle 数据库控制台中执行此命令以增加游标数量。

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

但是同样的错误再次出现。

最佳答案

更改 SQL 语句以使用绑定(bind)参数,并移动到循环之外

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

在循环内设置绑定(bind)参数

pst.setString(1, cLocationId);

您应该对 INSERT 语句执行相同的操作。

关于java.sql.SQLException : ORA-01000: maximum open cursors exceeded,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28235626/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com