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

Maarten Meeusen

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!

Maarten Meeusen

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:

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Transaction Management in Spring batch

From Dev

Transaction management with Spring Batch

From Dev

Transaction Issue with Spring Batch JobRepository in Unit Test

From Dev

JSR 352 Spring Batch: Transaction Management

From Dev

How does Spring Batch transaction management work?

From Dev

Transaction Issue with Spring Batch JobRepository in Unit Test

From Dev

JSR 352 Spring Batch: Transaction Management

From Dev

Transaction not set

From Dev

Transaction in Spring

From Dev

Spring JPA - No transaction set autocommit 'true'

From Dev

What are the similarities and differences between a BATCH statement in Cassandra and a Transaction in SQL?

From Dev

ActiveJDBC batch insert and transaction

From Dev

Spring Batch Java Config transaction-attributes equivalent

From Dev

Existing transaction detected in JobRepository - Spring Batch with Grails Plugin

From Dev

Retry not working when Spring Batch managed transaction rolls back

From Dev

Start a spring batch job when already within a transaction

From Dev

Existing transaction detected in JobRepository - Spring Batch with Grails Plugin

From Dev

How can stop JpaItemWriter if transaction failed in Spring Batch?

From Dev

first transaction in PostgreSQL

From Dev

How to call method after finishing first transaction in spring mvc

From Dev

How to call method after finishing first transaction in spring mvc

From Dev

Does "set transaction" start a transaction when autocommit is ON?

From Dev

Firebase Transaction [Error: Set]

From Dev

Transaction was overridden by a subsequent set

From Dev

CREATE/ALTER PROCEDURE' must be the first statement in a query batch

From Dev

Spring - commit JMS Transaction after JPA transaction

From Dev

Spring Global transaction vs Local transaction

From Dev

What is the difference between a "Spring transaction" and a "Hibernate transaction"

From Dev

Spring Transaction Hibernate @Transaction annotation not working with @Autowired

Related Related

  1. 1

    Transaction Management in Spring batch

  2. 2

    Transaction management with Spring Batch

  3. 3

    Transaction Issue with Spring Batch JobRepository in Unit Test

  4. 4

    JSR 352 Spring Batch: Transaction Management

  5. 5

    How does Spring Batch transaction management work?

  6. 6

    Transaction Issue with Spring Batch JobRepository in Unit Test

  7. 7

    JSR 352 Spring Batch: Transaction Management

  8. 8

    Transaction not set

  9. 9

    Transaction in Spring

  10. 10

    Spring JPA - No transaction set autocommit 'true'

  11. 11

    What are the similarities and differences between a BATCH statement in Cassandra and a Transaction in SQL?

  12. 12

    ActiveJDBC batch insert and transaction

  13. 13

    Spring Batch Java Config transaction-attributes equivalent

  14. 14

    Existing transaction detected in JobRepository - Spring Batch with Grails Plugin

  15. 15

    Retry not working when Spring Batch managed transaction rolls back

  16. 16

    Start a spring batch job when already within a transaction

  17. 17

    Existing transaction detected in JobRepository - Spring Batch with Grails Plugin

  18. 18

    How can stop JpaItemWriter if transaction failed in Spring Batch?

  19. 19

    first transaction in PostgreSQL

  20. 20

    How to call method after finishing first transaction in spring mvc

  21. 21

    How to call method after finishing first transaction in spring mvc

  22. 22

    Does "set transaction" start a transaction when autocommit is ON?

  23. 23

    Firebase Transaction [Error: Set]

  24. 24

    Transaction was overridden by a subsequent set

  25. 25

    CREATE/ALTER PROCEDURE' must be the first statement in a query batch

  26. 26

    Spring - commit JMS Transaction after JPA transaction

  27. 27

    Spring Global transaction vs Local transaction

  28. 28

    What is the difference between a "Spring transaction" and a "Hibernate transaction"

  29. 29

    Spring Transaction Hibernate @Transaction annotation not working with @Autowired

HotTag

Archive