na] Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist - Spring Batch

Question

I am working on Spring Boot v2.2.6.RELEASE and Spring Batch. In this example, I am reading data from Oracle system and putting it into the Postgres system after applying some data filter.

Note - Spring Batch is able to read the data from Oracle DB but unable to write it into PostgresDB.

spring.datasource.url=jdbc:oracle:thin:@//localhost:1527/DB
spring.datasource.username=user
spring.datasource.password=password
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

postgres.datasource.url=jdbc:postgresql://localhost:5432/admin?currentSchema=BATCH
postgres.datasource.username=postgres
postgres.datasource.password=postgres
postgres.datasource.driver-class-name=org.postgresql.Driver


#By default, Spring runs all the job as soon as it has started its context.
spring.batch.job.enabled=false

# Chunk Size to save data
spring.chunk.size=200

spring.batch.initialize-schema=always

DatabaseConfig.java

@Configuration
public class DatabaseConfig {

    @Autowired
    private Environment env;

    @Bean(name="oracleDS")
    @Primary
    public DataSource batchDataSource(){          
        return DataSourceBuilder.create()
                .url(env.getProperty("spring.datasource.url"))
                .driverClassName(env.getProperty("spring.datasource.driver-class-name"))
                .username(env.getProperty("spring.datasource.username"))
                .password(env.getProperty("spring.datasource.password"))
                .build();          
    } 



    // All metadata tables are present here
    @Bean(name="postgresDS")
    public DataSource mysqlBatchDataSource(){          
        return DataSourceBuilder.create()
                .url(env.getProperty("postgres.datasource.url"))
                .driverClassName(env.getProperty("postgres.datasource.driver-class-name"))
                .username(env.getProperty("postgres.datasource.username"))
                .password(env.getProperty("postgres.datasource.password"))
                .build();          
    }
}

EmployeeBatchConfig.java

@Configuration
public class EmployeeBatchConfig {
    private static final String SQL = "Get the Data";

    @Autowired
    @Qualifier(value="oracleDS")
    private DataSource dataSource;

    @Autowired
    @Qualifier(value="postgresDS")
    private DataSource dataSourcePostgresDB;

    @Bean(destroyMethod = "")
    @StepScope
    public JdbcCursorItemReader<Employee> EmployeeReader() throws Exception {
        JdbcCursorItemReader<Employee> reader = new JdbcCursorItemReader<>();
        reader.setDataSource(this.dataSource);
        reader.setSql(SQL);

        reader.setRowMapper(new EmployeeRowMapper());
        reader.afterPropertiesSet();
        return reader;
    }

    @Bean
    public JdbcBatchItemWriter<Employee> EmployeeWriter(NamedParameterJdbcTemplate jdbcTemplate){
        JdbcBatchItemWriter<Employee> writer = new JdbcBatchItemWriter<>();
        writer.setDataSource(this.dataSourcePostgresDB);
        writer.setJdbcTemplate(jdbcTemplate);

        ItemSqlParameterSourceProvider<Employee> paramProvider = new BeanPropertyItemSqlParameterSourceProvider<>();
        writer.setItemSqlParameterSourceProvider (paramProvider);

        writer.setSql("INSERT INTO BATCH.EMPLOYEE(EMP_ID, FIRSTNAME) VALUES (:empId, :firstName)");
        writer.afterPropertiesSet();
        return writer;
    }
}

Error:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO BATCH.EMPLOYEE(EMP_ID, FIRSTNAME) VALUES (?, ?)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:647) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:936) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:366) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.batch.item.database.JdbcBatchItemWriter.write(JdbcBatchItemWriter.java:182) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:193) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:159) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:294) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:217) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:77) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:407) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:331) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:273) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:82) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:375) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:145) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:258) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:208) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148) [spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:410) [spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:136) [spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:319) [spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:147) [spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) [spring-core-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:140) [spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) [spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) [spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) [spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) [spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) [spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) [spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at com.sun.proxy.$Proxy58.run(Unknown Source) [na:na]
    at com.mastercard.customer.data.management.CustomerProfileStagingBatchApplication.run(CustomerProfileStagingBatchApplication.java:50) [classes/:na]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:784) [spring-boot-2.2.6.RELEASE.jar:2.2.6.RELEASE]
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:768) [spring-boot-2.2.6.RELEASE.jar:2.2.6.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:322) [spring-boot-2.2.6.RELEASE.jar:2.2.6.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) [spring-boot-2.2.6.RELEASE.jar:2.2.6.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) [spring-boot-2.2.6.RELEASE.jar:2.2.6.RELEASE]
    at com.mastercard.customer.data.management.CustomerProfileStagingBatchApplication.main(CustomerProfileStagingBatchApplication.java:38) [classes/:na]
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) ~[ojdbc7-12.1.0.1.0.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) ~[ojdbc7-12.1.0.1.0.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017) ~[ojdbc7-12.1.0.1.0.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655) ~[ojdbc7-12.1.0.1.0.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249) ~[ojdbc7-12.1.0.1.0.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566) ~[ojdbc7-12.1.0.1.0.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215) ~[ojdbc7-12.1.0.1.0.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58) ~[ojdbc7-12.1.0.1.0.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943) ~[ojdbc7-12.1.0.1.0.jar:12.1.0.1.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeForRowsWithTimeout(OraclePreparedStatement.java:10932) ~[ojdbc7-12.1.0.1.0.jar:12.1.0.1.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:11043) ~[ojdbc7-12.1.0.1.0.jar:12.1.0.1.0]
    at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:244) ~[ojdbc7-12.1.0.1.0.jar:12.1.0.1.0]
    at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128) ~[HikariCP-3.4.2.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.2.jar:na]
    at org.springframework.jdbc.core.JdbcTemplate.lambda$batchUpdate$2(JdbcTemplate.java:950) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]

Note: When inserting the data into Postgres, but batch some how trying to load into Oracle only. Is there any @Bean qualifying issue ?

@Solution:

As suggested here: Spring Batch - Unable to save the records into Postgres database.

String sql = "INSERT INTO \"BATCH\".employee(rowid_object, status) VALUES (:rowid_object, :status)";

I really encourage you to read: https://stackoverflow.com/help/behavior. If someone takes time to help you in details (https://pasteboard.co/J2Q4AAR.png), it is not conform to SO code of conduct to remove the post (https://pasteboard.co/J2QWpff.png , https://pasteboard.co/J2QWxePK.png) without even saying thank you or accepting answers and then move on (since your issue is fixed) and post other questions under another identity: https://stackoverflow.com/questions/61085861. — Mahmoud Ben Hassine, Apr 08 '20 at 12:18
https://pasteboard.co/J2R3t01.png. And feel free to downvote my answers https://stackoverflow.com/a/61081771/5019386, I will still answer any question about Spring Batch in a professional way and according to SO code of conduct. — Mahmoud Ben Hassine, Apr 08 '20 at 12:20

score 0 · Answer 1 · answered Apr 07 '20 at 14:01

0

Yes, first you need to have the specific entity if you're initializing the statement as a bean for a specific table

answered Apr 07 '20 at 14:01

Amit Mishra

498
5
16

Could you please elaborate ? I am looking to create target DB tables automatically, All efforts I am taking at making the object and then persist it . – PAA Apr 07 '20 at 14:03
Nope, I dont have JPA Entity for Employee, however I've created Employee model class because I am using SpringJDBC to save the data. – PAA Apr 07 '20 at 14:44

Eklavya · Accepted Answer · 2020-04-07T16:53:35.130

BATCH.EMPLOYEE does not exist, that's why this error occurs.

You can use migration tools to run sql for create table and other modifications. Some popular migration tools are flyway and liquibase.

Migration tools helps you to create and update your database. It automatically run your defined sql files in a order your given. When you need to update any table add another script file then it automatically run it.

Follow this step to configure : https://flywaydb.org/getstarted/firststeps/maven

Database Migrations with Flyway Tutorial

score -1 · Answer 3 · answered Apr 07 '20 at 13:59

-1

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO BATCH.EMPLOYEE(EMP_ID, FIRSTNAME) VALUES (?, ?)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

According to this error, the table BATCH.EMPLOYEE does not exist. Make sure to create this table before running your job.

answered Apr 07 '20 at 13:59

Mahmoud Ben Hassine

28,519
3
32
50

Right, my question is how can we create it automatically ? – PAA Apr 07 '20 at 14:01
Where is this table defined? Please refer to https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto-database-initialization if you want Spring Boot to create it automatically. – Mahmoud Ben Hassine Apr 07 '20 at 14:37
Can we use the combination of Spring Jdbc (ItemReader) to read the data and JpaWriter to write the data to target system ? – PAA Apr 07 '20 at 14:51
I did not mention JPA, the link I shared is for all available options. If you want to use Flyway, here is the relevant section: https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto-execute-flyway-database-migrations-on-startup. – Mahmoud Ben Hassine Apr 07 '20 at 15:53
I am sorry, please confirm using Spring JDBC Can we create tables automatically ? – PAA Apr 07 '20 at 16:00
@PAA you can't auto generated Table using jdbc, it's better to use migration tools which can help to auto execute your sql – Eklavya Apr 07 '20 at 16:14
@AbinashGhosh - Thanks. Could you please share more info on migration tool ? – PAA Apr 07 '20 at 16:37

na] Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist - Spring Batch

3 Answers3

Linked