JdbcCursorItemReader. Frequent Start/Stop job throws UncategorizedSQLException.

[johny_z]

I have a spring batch application running on an app server.
I have a given a provision to the user to stop the job in between.
This is being done using jobExecution.stop().
But if the user Start and Stop the same job frequently, i am getting the following error (sometimes):

" Error while reading.
org.springframework.jdbc.UncategorizedSQLException : Attempt to process next row failed; uncategorized SQLException for SQL [(SELECT some query)]; SQL state [72000]; error code [1002]; ORA-01002: fetch out of sequence; nested exception is java.sql.SQLException: ORA-01002: fetch out of sequence "

I am using ojdbc14.jar as jdbc driver. I used JdbcCursorItemReader to read the data in some steps and HibernateCursorItemReader in some other steps.
I have set dataSource and mapper for JdbcCursorItemReader and queryString and sessionFactory for HibernateCursorItemReader. I searched a lot and tried by setting setVerifyCursorPosition to true also. But no luck.

Should I set anything else to my reader?
Would you please help me on this ?

[lucasward]

Can you post the full stack trace please? (with code tags)

[johny_z]

29 Oct 2008 17:49:59,697 [SimpleAsyncTaskExecutor-1] com.listeners.ItemLevelListener  - Error while reading.
org.springframework.jdbc.UncategorizedSQLException: Attempt to process next row failed; uncategorized SQLException for SQL [(SELECT query)
]; SQL state [72000]; error code [1002]; ORA-01002: fetch out of sequence
; nested exception is java.sql.SQLException: ORA-01002: fetch out of sequence
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124)
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
	at org.springframework.batch.item.database.JdbcCursorItemReader.doRead(JdbcCursorItemReader.java:427)
	at org.springframework.batch.item.support.AbstractBufferedItemReaderItemStream.read(AbstractBufferedItemReaderItemStream.java:92)
	at org.springframework.batch.item.support.DelegatingItemReader.read(DelegatingItemReader.java:61)
	at com.reader.MyReader.read(MyReader.java:87)
	at org.springframework.batch.item.support.DelegatingItemReader.read(DelegatingItemReader.java:61)
	at org.springframework.batch.core.step.item.BatchListenerFactoryHelper$1.read(BatchListenerFactoryHelper.java:67)
	at org.springframework.batch.core.step.item.SimpleItemHandler.doRead(SimpleItemHandler.java:88)
	at org.springframework.batch.core.step.item.SkipLimitStepFactoryBean$StatefulRetryItemHandler.read(SkipLimitStepFactoryBean.java:378)
	at org.springframework.batch.core.step.item.SimpleItemHandler.handle(SimpleItemHandler.java:66)
	at org.springframework.batch.core.step.item.ItemOrientedStep$2.doInIteration(ItemOrientedStep.java:390)
	at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:346)
	at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:212)
	at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:143)
	at org.springframework.batch.core.step.item.ItemOrientedStep.processChunk(ItemOrientedStep.java:383)
	at org.springframework.batch.core.step.item.ItemOrientedStep$1.doInIteration(ItemOrientedStep.java:251)
	at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:346)
	at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:212)
	at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:143)
	at org.springframework.batch.core.step.item.ItemOrientedStep.doExecute(ItemOrientedStep.java:231)
	at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:172)
	at org.springframework.batch.core.job.SimpleJob.execute(SimpleJob.java:100)
	at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:86)
	at java.lang.Thread.run(Thread.java:810)
Caused by: 
java.sql.SQLException: ORA-01002: fetch out of sequence

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:676)
	at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:602)
	at oracle.jdbc.driver.T2CPreparedStatement.fetch(T2CPreparedStatement.java:984)
	at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:291)
	at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:213)
	at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.next(WSJdbcResultSet.java:2468)
	at org.springframework.batch.item.database.JdbcCursorItemReader.doRead(JdbcCursorItemReader.java:418)
	... 22 more