Spring StoredProcedure with Oracle array : ORA-01000: maximum open cursors exceeded

Question

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]

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>

The stored Procedure class :-

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

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

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

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

Answer 1

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

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