RowMapper.mapRow returning java.sql.SQLException: Exhausted Resultset

[jwhite1202]

Hello,

I am trying to get a handle on an error I am getting. I recently upgraded Spring from 1.2.6 to 3.0.5 and I also upgraded the Oracle drivers to 11g and am using the Universal Connection Pool.

Here is what the dataSource been looks like:

<bean id="myDatasource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
            <property name="URL" value="@JDBC_URL@"/>
            <property name="user" value="@JDBC_USERNAME@"/>
            <property name="password" value="@JDBC_PASSWORD@"/>
            <property name="ConnectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource"/>
            <property name="ConnectionPoolName" value="RTF_POOL"/>
            <property name="MinPoolSize" value="5"/>
            <property name="MaxPoolSize" value="75"/>
            <property name="maxConnectionReuseTime" value="300"/>
            <property name="validateConnectionOnBorrow" value="true"/>
            <property name="fastConnectionFailoverEnabled" value="true"/>
            <property name="maxConnectionReuseCount" value="100"/>
            <property name="timeToLiveConnectionTimeout" value="1800"/>
            <property name="connectionWaitTimeout" value="30"/>
            <property name="inactiveConnectionTimeout" value="30"/>
            <property name="connectionHarvestMaxCount" value="25"/>
            <property name="connectionHarvestTriggerCount" value="5"/>
            <property name="maxStatements" value="150"/>
        </bean>

Here is an example of the way we currently use RowMapper :

public List<FeeFiled> getFiledFeeList( long providerSid, long serviceOffSid,
      String productCd, String specialtyCode, String procedureCd ) {

    List<CallableParameter> ffInputList = new ArrayList<CallableParameter>();
    ffInputList.add( new CallableParameter( new Long( providerSid ),
        Types.BIGINT ) );
    ffInputList.add( new CallableParameter( new Long( serviceOffSid ),
        Types.BIGINT ) );
    ffInputList.add( new CallableParameter( productCd, Types.VARCHAR ) );
    ffInputList.add( new CallableParameter( specialtyCode, Types.VARCHAR ) );
    ffInputList.add( new CallableParameter( procedureCd, Types.VARCHAR ) );

    List<FeeFiled> returnList = executeFunction(
        "spt_pkg.get_my_filed_fee", ffInputList,
        new RowMapper() {

          public FeeFiled mapRow( ResultSet res, int rowNum )
              throws SQLException {
            FeeFiled filedFee = setFiledFeeObject( res );
            return filedFee; // OUT
          }
        } );

    return returnList;
  }

And I occasionally get this error:

FATAL 26 Sep 2011 11:58:03 executeFunction(137): org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call npf_fee_request_insert_pkg.record_claim_batch_fee_request(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [null]; error code [31]; The connection is closed: The connection is closed; nested exception is java.sql.SQLException: The connection is closed: The connection is closed
ERROR 26 Sep 2011 11:58:03 insertBatchDetails(82): insertBatchDetails- loadClaimBatchFeeRequest()org.ddpa.npf.storedproc.FatalDatabaseException: Stored Procedure npf_fee_request_insert_pkg.record_claim_batch_fee_request call failed.
FATAL 26 Sep 2011 12:36:01 executeFunction(100): org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call npf_fee_request_pkg.get_srvc_off_prvdr_filed_fee(?, ?, ?, ?, ?)}]; SQL state [99999]; error code [17011]; Exhausted Resultset; nested exception is java.sql.SQLException: Exhausted Resultset
ERROR 26 Sep 2011 12:36:01 processClaim(147): Database Error Encountered During Fee Retrieval!
org.ddpa.npf.storedproc.FatalDatabaseException: Stored Procedure npf_fee_request_pkg.get_srvc_off_prvdr_filed_fee call failed.
        at org.ddpa.npf.storedproc.OracleSpringStoredProcedureDAO.executeFunction(OracleSpringStoredProcedureDAO.java:101)
        at org.ddpa.npf.dao.fees.impl.FiledFeeListDAOImpl.getFiledFeeList(FiledFeeListDAOImpl.java:76)
        at org.ddpa.npf.claimprocessor.service.fees.impl.FiledFeeServiceImpl.fetchFiledFeeList(FiledFeeServiceImpl.java:57)
        at org.ddpa.npf.claimprocessor.service.fees.impl.FiledFeeServiceImpl.deriveFiledFeeList(FiledFeeServiceImpl.java:95)
        at org.ddpa.npf.claimprocessor.product.BaseProductClaimDetailProcessor.doRetrieveFiledFees(BaseProductClaimDetailProcessor.java:624)
        at org.ddpa.npf.claimprocessor.product.BaseProductClaimDetailProcessor.getFiledFees(BaseProductClaimDetailProcessor.java:298)
        at org.ddpa.npf.claimprocessor.product.BaseProductClaimDetailProcessor.setFiledFees(BaseProductClaimDetailProcessor.java:409)
        at org.ddpa.npf.claimprocessor.product.preferred.PreferredClaimDetailProcessor.setFiledFees(PreferredClaimDetailProcessor.java:557)
        at org.ddpa.npf.claimprocessor.product.preferred.PreferredClaimDetailProcessor.doSetClaimDetailFeeValues(PreferredClaimDetailProcessor.java:101)
        at org.ddpa.npf.claimprocessor.product.BaseProductClaimDetailProcessor.setClaimDetailFeeValues(BaseProductClaimDetailProcessor.java:195)
        at org.ddpa.npf.claimprocessor.product.BaseProductClaimDetailProcessor.doSetClaimDetailFees(BaseProductClaimDetailProcessor.java:139)
        at org.ddpa.npf.claimprocessor.product.BaseProductClaimDetailProcessor.setClaimDetailFees(BaseProductClaimDetailProcessor.java:78)
        at org.ddpa.npf.claimprocessor.ClaimProcessorImpl.processFees(ClaimProcessorImpl.java:605)
        at org.ddpa.npf.claimprocessor.ClaimProcessorImpl.processClaim(ClaimProcessorImpl.java:184)
        at org.ddpa.npf.feeengine.FeeLookupTask.processClaim(FeeLookupTask.java:145)
        at org.ddpa.npf.feeengine.FeeLookupTask.run(FeeLookupTask.java:99)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:619)
Caused by: org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call npf_fee_request_pkg.get_srvc_off_prvdr_filed_fee(?, ?, ?, ?, ?)}]; SQL state [99999]; error code [17011]; Exhausted Resultset; nested exception is java.sql.SQLException: Exhausted Resultset
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:969)
        at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1003)
        at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:144)
        at org.ddpa.npf.storedproc.OracleSpringStoredProcedureDAO.executeFunction(OracleSpringStoredProcedureDAO.java:96)
        ... 18 more
Caused by: java.sql.SQLException: Exhausted Resultset
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
        at oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.java:813)
        at org.ddpa.npf.dao.fees.impl.FiledFeeListDAOImpl.setFiledFeeObject(FiledFeeListDAOImpl.java:126)
        at org.ddpa.npf.dao.fees.impl.FiledFeeListDAOImpl.access$000(FiledFeeListDAOImpl.java:39)
        at org.ddpa.npf.dao.fees.impl.FiledFeeListDAOImpl$1.mapRow(FiledFeeListDAOImpl.java:82)
        at org.ddpa.npf.dao.fees.impl.FiledFeeListDAOImpl$1.mapRow(FiledFeeListDAOImpl.java:78)
        at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:92)
        at org.springframework.jdbc.core.JdbcTemplate.processResultSet(JdbcTemplate.java:1144)
        at org.springframework.jdbc.core.JdbcTemplate.extractOutputParameters(JdbcTemplate.java:1104)
        at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:1015)
        at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:1)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:953)
        ... 21 more

I have read various posts around the internet stating that this Exhausted Resultset can come about using in a while loop, I believe it looks for a ResultSet.next() and there's nothing there. However since I am using RowMapper, no while statements here. The code that you see is an example, I get this error on different stored procedure calls. So what I am trying to find out is how did things change between Spring 1.2.6 and 3.0.5 with the way a mapRow handles ResultSets? This SQL code works in Spring 1.2.6/Oracle 10g. If this is more of an Oracle issue than a Spring issue that's fine too, but an guidance on this issue would be appreciated.

[bill chen]

i have also met this problem ,I don't know why it can throw exception. Is there an other method to solve this problem.