nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist - How to avoid creating Spring Batch Metadata?

Question

I already went through many links like Spring Batch Framework - Auto create Batch Table and tried many options, but it didn't work for me yet.

I am using Spring Boot Project.

I dont want to create Spring Batch Metadata tables over my existing Oracle Database. I'm simply develoing utility using Spring Batch to migrate data from Oracle to MongoDB.

Here when I execute my code -

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
] with root cause

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501) ~[ojdbc7-12.1.0.jar:12.1.0.2.0]
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:678) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:768) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
    at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.getJobInstance(JdbcJobInstanceDao.java:148) ~[spring-batch-core-4.1.1.RELEASE.jar:4.1.1.RELEASE]
    at org.springframework.batch.core.repository.support.SimpleJobRepository.getLastJobExecution(SimpleJobRepository.java:297) ~[spring-batch-core-4.1.1.RELEASE.jar:4.1.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:343) ~[spring-aop-5.1.5.RELEASE.jar:5.1.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.1.5.RELEASE.jar:5.1.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.1.5.RELEASE.jar:5.1.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) ~[spring-tx-5.1.5.RELEASE.jar:5.1.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.1.5.RELEASE.jar:5.1.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.5.RELEASE.jar:5.1.5.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.1.5.RELEASE.jar:5.1.5.RELEASE]
    at com.sun.proxy.$Proxy64.getLastJobExecution(Unknown Source) ~[na:na]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:101) ~[spring-batch-core-4.1.1.RELEASE.jar:4.1.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]

application.properties

spring.batch.initialize-schema=never

UPDATE :: Now I am not able to create the Spring Batch tables on MYSQL.

score 0 · Answer 1 · answered Mar 27 '19 at 08:47

You can store Spring batch metadata in a different database e.g. a File or Memory based database like HSQL, See sample XML Configuration

    <bean id="dataSourceBatchJob" class="org.apache.commons.dbcp.BasicDataSource"
    lazy-init="true" destroy-method="close">
    <property name="driverClassName" value="org.hsqldb.jdbc.JDBCDriver" />
    <property name="url"
        value="jdbc:hsqldb:file:batchdbspring/batchcore.db;hsqldb.default_table_type=cached;shutdown=true;hsqldb.sqllog=0;hsqldb.log_data=false;hsqldb.log_size=50;hsqldb.reconfig_logging=false" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>

<jdbc:initialize-database data-source="dataSourceBatchJob">
    <jdbc:script location="org/springframework/batch/core/schema-drop-hsqldb.sql" />
    <jdbc:script location="org/springframework/batch/core/schema-hsqldb.sql" />
</jdbc:initialize-database>

<bean id="jobRepository"
    class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
    <property name="transactionManager" ref="transactionManager" />
    <property name="databaseType" value="HSQL"></property>
    <property name="dataSource" ref="dataSourceBatchJob"></property>
</bean>

<bean id="jobExplorer"
    class="org.springframework.batch.core.explore.support.JobExplorerFactoryBean">
    <property name="dataSource" ref="dataSourceBatchJob" />
</bean>


<bean id="jobLauncher"
    class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
    <property name="jobRepository" ref="jobRepository" />
</bean>

Thanks. Could you please share the Spring Boot config details ? — PAA, Mar 27 '19 at 09:00
I simply don't want to create `Spring Batch metadata tables`. I am using Spring Boot Tables — PAA, Mar 27 '19 at 09:11

nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist - How to avoid creating Spring Batch Metadata?

1 Answers1