CODEWITHSUNDEEP
springjdbctemplate
Thiru
Thiru

Reputation: 2699

Spring JDBC Template - Maximum open cursors exceeded

Our application connects to Oracle database and we use JDBCTemplate provided by Spring to interact with the database. As per the documentation of Spring JDBCTemplate, it ensures that it will close all the connections and cursors. But apparently we are get the following open cursors exception.

ORA-01000: maximum open cursors exceeded at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[spring-jdbc-4.2.6.RELEASE.jar!/:4.2.6.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.2.6.RELEASE.jar!/:4.2.6.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.2.6.RELEASE.jar!/:4.2.6.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645) ~[spring-jdbc-4.2.6.RELEASE.jar!/:4.2.6.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:680) ~[spring-jdbc-4.2.6.RELEASE.jar!/:4.2.6.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712) ~[spring-jdbc-4.2.6.RELEASE.jar!/:4.2.6.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722) ~[spring-jdbc-4.2.6.RELEASE.jar!/:4.2.6.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:790) ~[spring-jdbc-4.2.6.RELEASE.jar!/:4.2.6.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:809) ~[spring-jdbc-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]

The snippet:

 private Optional<PartyAccess> executeQuery(Object... args) {
    try {
        String query =  "SELECT EMP.ID as PARTY_ID, EMP.MPIN_HSB, EMP.INVALID_MPIN_HSB_COUNT, " +
                "EMP.MPIN_HSB_MODIFIED_ON,  EMP.MPIN_LSB, EMP.INVALID_MPIN_LSB_COUNT, EMP.MPIN_LSB_MODIFIED_ON, " +
                "EMP.EMP_PIN AS SMS_PIN, EMP.NUMBER_OF_PIN_CODE_FAILURE AS INVALID_SMS_PIN_COUNT, EMP.PIN_MODIFIED_ON, " +
                "EMP.PINFLAG AS USE_SHA2_HASH_FOR_PIN, CASE WHEN EMP_BLACKLIST_STATUS = 'Y'  THEN 1 ELSE 0 END AS BLOCKED " +
                "FROM MTX_EMPLOYEE EMP WHERE EMP.ID = ?";
        return Optional.of((PartyAccess) jdbcTemplate.queryForObject(query,
                args,
                new BeanPropertyRowMapper(PartyAccess.class)));
    } catch (DataAccessException ex) {
        LOGGER.error("Error occurred", ex);
        return Optional.empty();
    }
}

We use hikari for JDBC connection pool and the maximum size if 10. We are facing the issue when there are lot of requests coming to the application. I tried increasing the max_cursors of database (bad solution, I know). But even, that didn't help. We monitored the open cursors when there are lot of requests coming in, the value didn't exceed the max cursor value which is being set.

Upvotes: 3

Views: 3004

Answers (1)

Thiru
Thiru

Reputation: 2699

The issue got fixed, after upgrading the JPA version to latest. The earlier spring jpa version was not closing the cursors properly.

Upvotes: 1

Related Questions