带有Oracle数组的Spring StoredProcedure:ORA-01000:超过了最大打开游标

问题描述

多次使用OracleTypes.ARRAY输入参数调用Oracle存储过程时，出现以下错误:-

When calling an Oracle stored procedure with OracleTypes.ARRAY input parameter multiple times, getting the following error :-

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模板配置为:-

The JDBC template configuration is :-

    <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:-

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

也尝试用OracleJdbc4NativeJdbcExtractor代替CommonsDbcpNativeJdbcExtractor.但是错误仍然存​​在.

Instead of CommonsDbcpNativeJdbcExtractor tried with OracleJdbc4NativeJdbcExtractor too. But still the error is there.

基本上，堆包含许多未关闭的Statement对象.知道为什么春天不关闭资源吗?

Basically the the heap contains lot of unclosed Statement objects. Any idea why spring is not closing the resources?

环境:-Java 1.8，Spring 4.1.6，Tomcat 7.

Environment :- Java 1.8, Spring 4.1.6, Tomcat 7.

推荐答案

检查您的open_cursor参数.此参数定义PER SESSION允许的最大光标.默认值为50.

check your open_cursor parameter. This parameter define the max cursor allowed PER SESSION. Default is 50.

检查是否有任何游标泄漏.通常，200到300的值对于普通用户来说应该绰绰有余了.

Check if you have any cursor leak. Normally a values of 200 ~ 300 should be more than enough for regular users.

这篇关于带有Oracle数组的Spring StoredProcedure:ORA-01000:超过了最大打开游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！