A V Vamsi krishna opened SPR-17001 and commented
+Error stack trace+
** | | | Caused by: java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:220) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:48) at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:938) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4901) at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:5631) at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385) at weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:105) at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1134) at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1131) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1079) | |
Reference URL: https://stackoverflow.com/questions/47044033/spring-5-0-1-namedjdbctemplate-batchupdate-ora-01000-maximum-open-cursors-excee
Issue Links: - #20687 Doc: ParameterMetaData.getParameterType performance on Oracle 12c
Comment From: spring-projects-issues
A V Vamsi krishna commented
Team,
Please update us if you are looking for additional details.
Comment From: spring-projects-issues
A V Vamsi krishna commented
Hi Team,
Could you please give an update on the above issue.
We are using the
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production in our environment
Comment From: spring-projects-issues
A V Vamsi krishna commented
Hi Team,
Could you treat this as priority. We are facing the issue in our production environment.
Comment From: spring-projects-issues
Juergen Hoeller commented
Do the steps in #20687 help: 1. Upgraded Oracle JDBC driver to ojdbc8.jar version 12.2.0.1 2. Create spring.properties with spring.jdbc.getParameterType.ignore=true
There is a corresponding note in our reference documentation since 5.0.2. Beyond that, we are not aware of any remaining issues on our side. It's effectively an Oracle JDBC driver bug with the use of the standard java.sql.ParameterMetaData.getParameterType() API which can be resolved through the steps above.
Comment From: spring-projects-issues
A V Vamsi krishna commented
Hi Juergen,
Thanks for the update. As we mentioned earlier we are using the below Oracle DB version
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit.
Currently we are using ojdbc14. jar version 10.2.0.2.0
Can we use the ojdbc8.jar version 12.2.0.1 in this environment ?
Comment From: spring-projects-issues
Juergen Hoeller commented
As far as I know, you can use the Oracle 12 JDBC driver against an Oracle 11 DBMS. However, I'm no expert there; please check with your database administrator.
Even on its own, spring.jdbc.getParameterType.ignore=true should get you pretty far in avoiding that driver bug. You may set that flag in a spring.properties file in the root of your classpath or as a JVM system property.
Comment From: spring-projects-issues
A V Vamsi krishna commented
Hi Juergen,
Currently in our application we are using JDBCTemplate for all DB interactions. For each method call, we are creating new JDBCTemplate, using it and not closing anything (JDBC resources) from application side. Is there any possibility that it may keep Cursors open?.Can we create any number of JDBCTemplate and it will have no impact on number of open cursors count?
Regards,
Ram Lodhi.
Comment From: sabinsumghershanan
@jhoeller I am also facing the issue with open cursors in my application. I am using spring version 5.0.16-RELEASE and ojdbc6 jar and java1.7. The open cursor value has been set to 400 and connction pool value is 40. But still the open cursors get exceeded. Could you please provide some recommendation for the same?
Comment From: iKoru
@jhoeller Me, too. I'm on Spring boot 2.2.5.RELEASE(maybe based on Spring 5.2.6.RELEASE or something) and ojdbc8.jar and java 1.8.
Comment From: pkumaravel
me too facing same issue using Spring JDBC 5.1.3.RELEASE with ojdbc7 driver on a Oracle 12c database, any suggestion or solution.
Comment From: sabinsumghershanan
@pkumaravel I resolved the issue by adding spring.jdbc.getParameterType.ignore=true, as a custom property in was. You can also add it as a spring.properties file in the root of the classpath of your project.