Spring batch generates ORA-01453: SET TRANSACTION must be first statement of transaction

Question

I'm trying to set up a Spring Batch job which reads from an Oracle DB and then writes to xml. But it won't get further than the reading process, where I get this exception:

java.lang.IllegalStateException: Failed to execute CommandLineRunner
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:779) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:760) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.SpringApplication.afterRefresh(SpringApplication.java:747) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1162) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1151) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at hello.Application.main(Application.java:10) [classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_20]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_20]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_20]
at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0_20]
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) [idea_rt.jar:na]
Caused by: org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:289) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373) ~[spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:427) ~[spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276) ~[spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at com.sun.proxy.$Proxy41.getLastJobExecution(Unknown Source) ~[na:na]
at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:98) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_20]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_20]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_20]
at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0_20]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at com.sun.proxy.$Proxy46.run(Unknown Source) ~[na:na]
at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.execute(JobLauncherCommandLineRunner.java:214) ~[spring-boot-autoconfigure-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.executeLocalJobs(JobLauncherCommandLineRunner.java:231) ~[spring-boot-autoconfigure-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.launchJobFromProperties(JobLauncherCommandLineRunner.java:123) ~[spring-boot-autoconfigure-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.run(JobLauncherCommandLineRunner.java:117) ~[spring-boot-autoconfigure-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:776) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
... 11 common frames omitted

Caused by: java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction

I've implemented the reader bean like this:

@Configuration
@EnableBatchProcessing
@Import(DataSourceConfiguration.class)
public class BatchConfiguration {
    @Autowired
    public DataSource dataSource;

    @Bean
    public JdbcCursorItemReader<LeaveBalanceDTO> reader(){
        JdbcCursorItemReader<LeaveBalanceDTO> reader = new JdbcCursorItemReader<>();
        String sql = "SELECT * FROM sapleave";
        reader.setDataSource(dataSource);
        reader.setSql(sql);
        reader.setRowMapper(new BeanPropertyRowMapper<>(LeaveBalanceDTO.class));
        return reader;
    }

And the datasource configuration looks like this

@PropertySource(value=("classpath:application.properties"))
public class DataSourceConfiguration {
    @Autowired
    Environment environment;

    @Bean
    public DataSource dataSource() {
        return DataSourceBuilder
            .create()
            .driverClassName(environment.getProperty("CDL.driverClassName"))
            .url(environment.getProperty("CDL.url"))
            .username(environment.getProperty("CDL.username"))
            .password(environment.getProperty("CDL.password"))
            .build();
    }

I thought TransactionManagement was done automatically by Spring Batch with the @EnableBatchProcessing. And also I didn't think for select queries a transaction is even needed. Somebody have an idea of what is going wrong? Thanks in advance!

Edit: Instead of DataSourceBuilder I have implemented OracleDataSource like this:

@Bean
public DataSource dataSource() throws SQLException {
    OracleDataSource dataSource = new OracleDataSource();
    dataSource.setUser(environment.getProperty("CDL.username"));
    dataSource.setPassword(environment.getProperty("CDL.password"));
    dataSource.setURL(environment.getProperty("CDL.url"));
    return dataSource;
}

But now I get this error:

Caused by: org.springframework.dao.DataAccessResourceFailureException: Could not obtain sequence value; nested exception is java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist

At the startup of the application I see that spring is running a sql script, I guess to create a temporary database:

2017-03-29 15:43:57.200  INFO 19332 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [org/springframework/batch/core/schema-oracle10g.sql]
2017-03-29 15:43:57.285  INFO 19332 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from class path resource [org/springframework/batch/core/schema-oracle10g.sql] in 85 ms.

And it seems like some sequences are getting created in there. I don't know if it has something to do with this problem but I am using an Oracle 9i for retrieving my data. Any ideas on possible solutions? Thanks!

score 0 · Accepted Answer · edited May 23 '17 at 11:54

0

I fixed it myself by preventing spring batch to store the job repository in the Oracle DB. What I did is let Spring create an embedded hsqldb to store its job repo. I did this based on these 2 posts:

Spring Batch - How to prevent batch from storing transactions in DB
How to java-configure separate datasources for spring batch data and business data? Should I even do it?

edited May 23 '17 at 11:54

Community

1
1

answered Mar 31 '17 at 16:04

Maarten Meeusen

482
1
9
23

Spring batch generates ORA-01453: SET TRANSACTION must be first statement of transaction

1 Answers1