RowMapper.mapRow returning java.sql.SQLException: Exhausted Resultset
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:
Code:
<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 :
Code:
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:
Code:
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.