title: Spring Batch connection to Oracle DataBase
body: <p>I am Using Spring Batch for Writing Data from flat file to Oracle DB .But i am getting error for Automatic Table Creation .Can Anyone Please Help On this.</p>
  
  <p>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.SQLException: ORA-00942: table or view does not exist</p>
  
  <pre><code>&lt;beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:batch="http://www.springframework.org/schema/batch" 
  xmlns:task="http://www.springframework.org/schema/task"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.springframework.org/schema/batch
      http://www.springframework.org/schema/batch/spring-batch-2.2.xsd
      http://www.springframework.org/schema/beans 
      http://www.springframework.org/schema/beans/spring-beans-3.2.xsd"&gt;
  
  &lt;bean id="report" class="com.mkyong.model.Report" scope="prototype" /&gt;
  
  &lt;batch:job id="reportJob"&gt;
      &lt;batch:step id="step1"&gt;
          &lt;batch:tasklet&gt;
              &lt;batch:chunk reader="cvsFileItemReader" writer="oracleItemWriter"
                  commit-interval="2"&gt;
              &lt;/batch:chunk&gt;
          &lt;/batch:tasklet&gt;
      &lt;/batch:step&gt;
  &lt;/batch:job&gt;
  
  &lt;bean id="cvsFileItemReader" class="org.springframework.batch.item.file.FlatFileItemReader"&gt;
  
      &lt;!-- Read a csv file --&gt;
      &lt;property name="resource" value="classpath:cvs/report.csv" /&gt;
  
      &lt;property name="lineMapper"&gt;
          &lt;bean class="org.springframework.batch.item.file.mapping.DefaultLineMapper"&gt;
  
              &lt;!-- split it --&gt;
              &lt;property name="lineTokenizer"&gt;
                  &lt;bean
                      class="org.springframework.batch.item.file.transform.DelimitedLineTokenizer"&gt;
                      &lt;property name="names" value="date,impressions,clicks,earning" /&gt;
                  &lt;/bean&gt;
              &lt;/property&gt;
  
              &lt;property name="fieldSetMapper"&gt;
  
                  &lt;!-- return back to reader, rather than a mapped object. --&gt;
                  &lt;!--
                      &lt;bean class="org.springframework.batch.item.file.mapping.PassThroughFieldSetMapper" /&gt;
                  --&gt;
  
                  &lt;!-- map to an object --&gt;
                  &lt;bean
                      class="org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper"&gt;
                      &lt;property name="prototypeBeanName" value="report" /&gt;
                  &lt;/bean&gt;
  
              &lt;/property&gt;
  
          &lt;/bean&gt;
      &lt;/property&gt;
  
  &lt;/bean&gt;
  
  &lt;bean id="oracleItemWriter"
      class="org.springframework.batch.item.database.JdbcBatchItemWriter"&gt;
      &lt;property name="dataSource" ref="dataSource" /&gt;
      &lt;property name="sql"&gt;
          &lt;value&gt;
          &lt;![CDATA[        
              insert into RAW_REPORT(DATE1,IMPRESSIONS,CLICKS,EARNING) values (:date, :impressions, :clicks, :earning)
          ]]&gt;
          &lt;/value&gt;
      &lt;/property&gt;
      &lt;!-- It will take care matching between object property and sql name parameter --&gt;
      &lt;property name="itemSqlParameterSourceProvider"&gt;
          &lt;bean
              class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" /&gt;
      &lt;/property&gt;
  &lt;/bean&gt;
  </code></pre>
  
  <p></p>
  
  <p>DataBase.xml</p>
  
  <pre><code>&lt;beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:jdbc="http://www.springframework.org/schema/jdbc" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.springframework.org/schema/beans 
      http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
      http://www.springframework.org/schema/jdbc 
      http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd"&gt;
  
  &lt;!-- connect to database --&gt;
  &lt;!-- &lt;bean id="dataSource"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource"&gt;
      &lt;property name="driverClassName" value="com.mysql.jdbc.Driver" /&gt;
      &lt;property name="url" value="jdbc:mysql://localhost:3306/Sakila" /&gt;
      &lt;property name="username" value="root" /&gt;
      &lt;property name="password" value="" /&gt;
  &lt;/bean&gt; --&gt;
  
  &lt;!-- &lt;bean id="jobLauncher" class="org.springframework.batch.core.launch.support.SimpleJobLauncher"&gt;
      &lt;property name="jobRepository" ref="jobRepository"/&gt;
  &lt;/bean&gt;
  </code></pre>
  
  <p>-->
     
          <br>
      &lt;/bean> --></p>
  
  <pre><code>&lt;!-- &lt;bean id="jobRepository" class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean"&gt;
       &lt;property name="dataSource" ref="dataSource" /&gt;
       &lt;property name="transactionManager" ref="transactionManager"/&gt;
       &lt;property name="databaseType" value="oracle" /&gt;
       &lt;property name="tablePrefix" value="BATCH_"/&gt;
       &lt;property name="isolationLevelForCreate" value="ISOLATION_DEFAULT"/&gt;
  &lt;/bean&gt;   --&gt;
  
  &lt;bean id="jobRepository"
  class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean"&gt;
  &lt;property name="transactionManager" ref="transactionManager" /&gt;
  &lt;/bean&gt;
  
  &lt;bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"&gt;
      &lt;property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/&gt;
      &lt;property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/&gt;
      &lt;property name="username" value="system"/&gt;
      &lt;property name="password" value=""/&gt;
  &lt;/bean&gt;
  
   &lt;bean id="transactionManager"
      class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" /&gt;  
  
  &lt;!-- create job-meta tables automatically --&gt;
  &lt;!-- &lt;jdbc:initialize-database data-source="dataSource"&gt;
      &lt;jdbc:script location="org/springframework/batch/core/schema-drop-mysql.sql" /&gt;
      &lt;jdbc:script location="org/springframework/batch/core/schema-mysql.sql" /&gt;
  &lt;/jdbc:initialize-database&gt; --&gt;
  
  
  &lt;jdbc:initialize-database data-source="dataSource"  &gt;
      &lt;jdbc:script location="classpath:/org/springframework/batch/core/schema-oracle10g.sql" /&gt;
      &lt;jdbc:script location="classpath:/org/springframework/batch/core/schema-drop-oracle10g.sql" /&gt;
       &lt;!-- &lt;jdbc:script location="classpath:oracle10g/initial-query.sql" /&gt; --&gt;
  
  &lt;/jdbc:initialize-database&gt;
  </code></pre>
  
  <p></p>
  
  <p>Context.xml</p>
  
  <pre><code>&lt;beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="
      http://www.springframework.org/schema/beans 
      http://www.springframework.org/schema/beans/spring-beans-3.2.xsd"&gt;
  
  &lt;!-- stored job-meta in database --&gt;
  &lt;bean id="jobRepository"
      class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean"&gt;
      &lt;property name="dataSource" ref="dataSource" /&gt;
      &lt;property name="transactionManager" ref="transactionManager" /&gt;
      &lt;property name="databaseType" value="oracle" /&gt;
  &lt;/bean&gt;
  
  &lt;!-- stored job-meta in memory --&gt;
  &lt;!-- 
  &lt;bean id="jobRepository"
      class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean"&gt;
      &lt;property name="transactionManager" ref="transactionManager" /&gt;
  &lt;/bean&gt;
  --&gt;
  
  &lt;bean id="jobLauncher"
      class="org.springframework.batch.core.launch.support.SimpleJobLauncher"&gt;
      &lt;property name="jobRepository" ref="jobRepository" /&gt;
  &lt;/bean&gt;
  </code></pre>
  
  <p></p>
  
  <p>Thanks
  Sh anti Bhushan</p>