使用OracleTypes.ARRAY输入参数多次调用Oracle存储过程时,收到以下错误: –
org.springframework.jdbc.UncategorizedsqlException: CallableStatementCallback; uncategorized sqlException for sql [{call EMP_SCHEMA.GET_EMPLOYEE_LIST(?,?)}]; sql state [72000]; error code [1000]; ORA-01000: maximum open cursors exceeded; nested exception is java.sql.sqlException: ORA-01000: maximum open cursors exceeded at org.springframework.jdbc.support.AbstractFallbacksqlExceptionTranslator.translate(AbstractFallbacksqlExceptionTranslator.java:84) ~[spring-jdbc-4.1.6.RELEASE.jar:4.1.6.RELEASE] at org.springframework.jdbc.support.AbstractFallbacksqlExceptionTranslator.translate(AbstractFallbacksqlExceptionTranslator.java:81) ~[spring-jdbc-4.1.6.RELEASE.jar:4.1.6.RELEASE]
JDBC模板配置是: –
<bean id="commonsDbcpNativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor" /> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg> <ref bean="dataSource" /> </constructor-arg> <property name="nativeJdbcExtractor" ref="commonsDbcpNativeJdbcExtractor" /> </bean>
存储过程类: –
public class GetEmployees extends StoredProcedure { public GetEmployees(JdbcTemplate jdbcTemplate) { super(jdbcTemplate,"EMP_SCHEMA.GET_EMPLOYEE_LIST"); declareParameter(new sqlParameter("p_emp_id_list",OracleTypes.ARRAY,"TBL_EMP_ID")); declareParameter(new sqlOutParameter(CURSOR,OracleTypes.CURSOR,new EmployeeDataRowMapper())); compile(); } public List<Employee> ofIds(Set<EmployeeId> employeeIds) { Map<String,OracleArraysqlTypeValue> params = new HashMap<>(); params.put("p_emp_id_list",new OracleArraysqlTypeValue(employeeIds)); final Map<String,Object> result = execute(params); return (List<Employee>) result.get(CURSOR); } }
Oracle sqlTypeValue: –
public class OracleArraysqlTypeValue extends AbstractsqlTypeValue { private final String[][] employeeIds; public OracleArraysqlTypeValue(String[][] employeeIds) { this.employeeIds = employeeIds; } @Override protected Object createTypeValue(Connection connection,int sqlType,String typeName) throws sqlException { ArrayDescriptor arrayDescriptor = new ArrayDescriptor(typeName,connection); return new ARRAY(arrayDescriptor,connection,employeeIds); } }
而不是CommonsDbcpNativeJdbcExtractor也尝试使用OracleJdbc4NativeJdbcExtractor.但仍然存在错误.
基本上堆包含许多未闭合的Statement对象.知道为什么春天没有关闭资源?
环境: – Java 1.8,Spring 4.1.6,Tomcat 7.
检查你的open_cursor参数.此参数定义允许的最大游标PER SESSION.默认值为50.
检查是否有任何光标泄漏.通常情况下,200到300的值应该足以满足普通用户的需求.