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

1
votes

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)";
3
springspring-bootspring-batch
I really encourage you to read: stackoverflow.com/help/behavior. If someone takes time to help you in details (pasteboard.co/J2Q4AAR.png), it is not conform to SO code of conduct to remove the post (pasteboard.co/J2QWpff.png , 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: stackoverflow.com/questions/61085861. - Mahmoud Ben Hassine
pasteboard.co/J2R3t01.png. And feel free to downvote my answers 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

3 Answers

0
votes

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

0
votes

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

-1
votes

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.