Spring Batch Oracle JdbcCursorItemReader

[sathya_sn]

I'm running into an interesting issue trying to use a JdbcCursorItemReader against an Oracle database. When the commit-interval is reached and the first chunk commits, I get a "Caused by: java.sql.SQLException: ORA-01002: fetch out of sequence" from the ItemReader.

It almost looks like the cursor was opened FOR UPDATE. This is in a XA transaction in JBoss 5.1.0. Has anyone run into this issue before? Any configuration I could be missing ?

Would really appreciate any help ...

    <bean id="customer.addressLineUpdateReader" 
        class="org.springframework.batch.item.database.JdbcCursorItemReader"
        p:dataSource-ref="springbatch.repositoryDataSource">
        
        <property name="rowMapper">
            <bean class="org.springframework.jdbc.core.BeanPropertyRowMapper">
                <property name="mappedClass" value="com.company.CustomerAddressDTO"/>
            </bean>
        </property>
        <property name="sql" value="
             select 
                 cust_addr_id recordId,
                 cust_id customerId  
             from  
                 customer_address
             where  
                 void_ind = 'n' 
                 and cust_addr_typ_cd = 'abc'" />
    </bean>

    <bean id="customer.addressLineUpdateWriter" scope="step"
        class="com.company.PerformanceAddressLineUpdateWriter"/>

    <batch:job id="customer.job.addressLineUpdateJob" 
        job-repository="springbatch.jobRepository" parent="springbatch.job.baseJob">
        
        <batch:step id="partitionStep" parent="springbatch.step.baseStep">
            <batch:tasklet transaction-manager="xaTransactionManager">
                <batch:chunk commit-interval="5"                     
                    reader="customer.addressLineUpdateReader"
                    writer="customer.addressLineUpdateWriter"/>
            </batch:tasklet>
        </batch:step>

    </batch:job>

[sathya_sn]

Has anyone tried using Spring Batch with Oracle in an XA environment? It would be hard to believe no one else has run into this.

[sathya_sn]

I ended up registering an additional datasource in JBoss and used it just for the reader. With this seperation in the datasource and underlying connection between the reader and other spring batch artifacts (processor/writer etc), everything works fine.

Was debating over whether this was the right thing to do and doing further googling led me to a blog series by Tobias Flohre at http://blog.codecentric.de/en/2012/0...and-listeners/. In the section "Cursor based reading", he is proposing exactly this.

That does make me feel a bit more comfortable with the solution. Has anyone else run into this issue? Any other resolution than the one I've mentioned? Would be really interested to know. Thanks!

[mwhipple]

I just ran in to the same problem. The solution of adding another resource to JBoss did the trick, thanks. I would think that the "setUseSharedExtendedConnection" may offer a simpler solution, even if it ends up being a workaround. I may look at it after I get caught up from having lost the time wrestling with this cursor issue.

[sathya_sn]

I did try setting the "useSharedExtendedConnection" to true and wrapped the datasource in ExtendedConnectionDataSourceProxy (like the javadoc recommends). Didn't seem to make any difference in my setup. Would be interested to know if that works for you.