8

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.

Manu
  • 3,467
  • 3
  • 29
  • 28
  • 2
    @LalitKumarB While the linked question does answer what the underlying cause for the `ORA-01000` error is, it does not answer the question the OP asked which was "Why is spring not closing the resources?" – MT0 Jan 11 '16 at 08:34
  • 1
    @LalitKumarB Please don't mark questions as duplicate, without reading it – Manu Jan 11 '16 at 08:45
  • Post reopened. On a side note, it would be nice if you could edit the question and explain why it is not a duplicate. – Lalit Kumar B Jan 11 '16 at 08:55
  • 1
    @kmmanu How and why would someone mark a post as duplicate without even reading it? The underlying problem is exactly what is answered in the post marked as duplicate. It would be nice if you could edit the question and explain why it is not a duplicate. – Lalit Kumar B Jan 11 '16 at 08:57
  • @LalitKumarB Thanks for reopening the post. The underlying problem may be same. But question is why Spring is not able to close the JDBC resources, when using the framework provided API. – Manu Jan 11 '16 at 09:12

2 Answers2

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.

Jin
  • 121
  • 1
  • 4
0

In the above case, the issue was due to the wrong ownership of oracle array type. Actually the user defined array type TBL_EMP_ID was owned by a schema, other than the schema in which the stored procedure was declared.

The issue was solved by moving the array-type declaration to the same schema where the stored procedure GET_EMPLOYEE_LIST was declared.

Manu
  • 3,467
  • 3
  • 29
  • 28
  • How did you determine that it was the ownership of the oracle array type that was causing the problem? According to https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access.html#jdbc Spring should be closing the statements - is there a spring bug that describes why it wasn't? – Ryan Nov 20 '17 at 19:45
  • Frankly, this was based on a trial and error. Might be a shortcoming in the spring-jdbc code to close the connection in the above described scenario. – Manu Nov 22 '17 at 06:27