Could not open JDBC Connection for transaction java.sql.SQLException: ORA-00923:

**Rams** · Apr 26th, 2011, 07:43 AM

Hi Robin,

Thanks for your good response.

Actually i had created the table in Oracle10g database. but dont know about schema and synonyms.

The table structure is like this....

CREATE TABLE PLAYER (
PLAYER_ID INT unsigned NOT NULL ,
FIRST_NAME VARCHAR(20) NOT NULL ,
LAST_NAME VARCHAR(20) NOT NULL ,
POSITION VARCHAR(3) ,
DEBUT_YEAR INT,
FINAL_YEAR INT,
CAREER_LENGTH DOUBLE
);

can you pls help on this...

**robinkrom** · Apr 26th, 2011, 08:06 AM

Hello Rams,

As the "MEMORY-JOBREPOSITORY.xml" is commented out and the "DB-JOBREPOSITORY.xml" you actually need to create the DB tables for the spring-batch REPOSITORY itself. This repository is used to create the controlling/metadata for the spring-batchframework.

But if you are starting to learn about spring-batch you could work without! For production environments you should always use a database, the in memory solution has it's problems...

Anyway for now:
You should uncomment the MEMORY jobrepository import statement in FileToTableJob.xml and comment the DB import.

So go from this:

<import resource="DB-JOBREPOSITORY.xml"/>

to this:
<import resource="MEMORY-JOBREPOSITORY.xml"/>


hopefully nothing else was changed which might break the setup.

Your "Job-Repository" will now be in memory, the tables you use for the job itself are still in oracle.

Best wishes,
Robin

**Rams** · Apr 26th, 2011, 08:59 AM

Hi bwawok/robinkrom,

It is working fine. I am very greatfull thanks you both for make me to complete this task.

Thanks Thanks Thanks............

Thanks & Regards,
Rams

**Rams** · May 14th, 2011, 08:28 AM

Hi robin,

now i want run batch using DB-JOBREPOSITORY.xml and i had created the DB instances like the following way....

Code:

CREATE TABLE BATCH_STEP_EXECUTION_SEQ (ID NUMBER NOT NULL);
CREATE TABLE BATCH_JOB_EXECUTION_SEQ (ID NUMBER NOT NULL) 
CREATE TABLE BATCH_JOB_SEQ (ID NUMBER NOT NULL) 
CREATE TABLE BATCH_JOB_INSTANCE
  (
    JOB_INSTANCE_ID NUMBER PRIMARY KEY   ,
    JOB_NAME        VARCHAR(100) NOT NULL,
    JOB_KEY         VARCHAR(2500)
  )

Code:

CREATE TABLE BATCH_JOB_PARAMS
  (
    JOB_INSTANCE_ID NUMBER NOT NULL,
    TYPE_CD         VARCHAR(6) NOT NULL,
    KEY_NAME        VARCHAR(100) NOT NULL,
    STRING_VAL      VARCHAR(250),
    DATE_VAL        DATE,
    LONG_VAL        NUMBER,
    DOUBLE_VAL DOUBLE PRECISION,
    CONSTRAINT JOB_INSTANCE_PARAMS_FK FOREIGN KEY (JOB_INSTANCE_ID) REFERENCES BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
  )

CREATE TABLE BATCH_JOB_EXECUTION
  (
    JOB_EXECUTION_ID NUMBER PRIMARY KEY ,
    VERSION          NUMBER,
    JOB_INSTANCE_ID  NUMBER NOT NULL,
    CREATE_TIME      DATE NOT NULL,
    START_TIME       DATE NULL,
    END_TIME         DATE NULL,
    STATUS           VARCHAR(10),
    EXIT_CODE        VARCHAR(20),
    EXIT_MESSAGE     VARCHAR(2500),
    LAST_UPDATED     DATE,
    CONSTRAINT JOB_INSTANCE_EXECUTION_FK FOREIGN KEY (JOB_INSTANCE_ID) REFERENCES BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
  )


CREATE TABLE BATCH_STEP_EXECUTION
  (
    STEP_EXECUTION_ID NUMBER PRIMARY KEY ,
    VERSION           NUMBER NOT NULL,
    STEP_NAME         VARCHAR(100) NOT NULL,
    JOB_EXECUTION_ID  NUMBER NOT NULL,
    START_TIME TIMESTAMP NOT NULL,
    END_TIME TIMESTAMP NULL,
    STATUS             VARCHAR(10),
    COMMIT_COUNT       NUMBER,
    READ_COUNT         NUMBER,
    FILTER_COUNT       NUMBER,
    WRITE_COUNT        NUMBER,
    READ_SKIP_COUNT    NUMBER,
    WRITE_SKIP_COUNT   NUMBER,
    PROCESS_SKIP_COUNT NUMBER,
    ROLLBACK_COUNT     NUMBER,
    EXIT_CODE          VARCHAR(20),
    EXIT_MESSAGE       VARCHAR(2500),
    LAST_UPDATED TIMESTAMP,
    CONSTRAINT JOB_EXECUTION_STEP_FK FOREIGN KEY (JOB_EXECUTION_ID) REFERENCES BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
  ) 

CREATE TABLE BATCH_JOB_EXECUTION_CONTEXT
  (
    JOB_EXECUTION_ID NUMBER PRIMARY KEY,
    SHORT_CONTEXT    VARCHAR2(2500) NOT NULL,
    SERIALIZED_CONTEXT VARCHAR2(4000),
    CONSTRAINT JOB_EXEC_CTX_FK FOREIGN KEY (JOB_EXECUTION_ID) REFERENCES BATCH_JOB_EXECUTION (JOB_EXECUTION_ID)
  )

CREATE TABLE BATCH_STEP_EXECUTION_CONTEXT
  (
    STEP_EXECUTION_ID NUMBER PRIMARY KEY,
    SHORT_CONTEXT     VARCHAR2(2500) NOT NULL,
    SERIALIZED_CONTEXT VARCHAR2(4000),
    CONSTRAINT STEP_EXEC_CTX_FK FOREIGN KEY (STEP_EXECUTION_ID) REFERENCES BATCH_STEP_EXECUTION(STEP_EXECUTION_ID)
  )

and my DB-JOBREPOSITORY.xml is

Code:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:p="http://www.springframework.org/schema/p"
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-2.0.xsd"> 

<bean id="jobRepository-dataSource" 
class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> 
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" /> 
<property name="url" value="jdbc:oracle:thin:@1X.XXX.XX.XXX:XXXX:XXXX" /> 
<property name="username" value="XXXXX" /> <!-- your user id. e.g. root--> 
<property name="password" value="XXXXX" /> <!-- your password-->
<property name="maxIdle" value="10"/> 
<property name="maxActive" value="100"/> 
<property name="maxWait" value="10000"/> 
<property name="validationQuery" value="select 1 from dual"/> 
<property name="testOnBorrow" value="true"/> 
<property name="testWhileIdle" value="true"/> 
<property name="timeBetweenEvictionRunsMillis" value="1200000"/> 
<property name="minEvictableIdleTimeMillis" value="1800000"/> 
<property name="numTestsPerEvictionRun" value="5"/> 
<property name="defaultAutoCommit" value="false"/> 
</bean> 

<bean id="jobRepository-transactionManager" 
class="org.springframework.jdbc.datasource.DataSourceTransactionManager" lazy-init="true">
<property name="dataSource" ref="jobRepository-dataSource" /> 
</bean>

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

<bean id="asyncTaskExecutor" 
class="org.springframework.core.task.SimpleAsyncTaskExecutor"/>
<bean id="syncTaskExecutor" 
class="org.springframework.core.task.SyncTaskExecutor" />

<bean id="jobLauncher" 
class="org.springframework.batch.core.launch.support.SimpleJobLauncher"> 
<property name="jobRepository" ref="jobRepository"/> 
</bean>
<bean id="jobExplorer" 
class="org.springframework.batch.core.explore.support.JobExplorerFactoryBean" 
p:dataSource-ref="jobRepository-dataSource" p:tablePrefix="batch_" />
</beans>

and i am getting the following error...

Code:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jobRepository' defined in class path resource [DB-JOBREPOSITORY.xml]: Invocation of init method failed; nested exception is java.lang.NoClassDefFoundError: javax/xml/stream/XMLStreamException
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1338)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:473)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409)
	at java.security.AccessController.doPrivileged(Native Method)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380)
	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:423)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:728)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:380)
	at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:139)
	at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:83)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.start(CommandLineJobRunner.java:190)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.main(CommandLineJobRunner.java:254)
	at FileToTableMain.main(FileToTableMain.java:8)
Caused by: java.lang.NoClassDefFoundError: javax/xml/stream/XMLStreamException
	at org.springframework.batch.core.repository.dao.XStreamExecutionContextStringSerializer.init(XStreamExecutionContextStringSerializer.java:65)
	at org.springframework.batch.core.repository.dao.XStreamExecutionContextStringSerializer.afterPropertiesSet(XStreamExecutionContextStringSerializer.java:60)
	at org.springframework.batch.core.repository.dao.JdbcExecutionContextDao.afterPropertiesSet(JdbcExecutionContextDao.java:155)
	at org.springframework.batch.core.repository.support.JobRepositoryFactoryBean.createExecutionContextDao(JobRepositoryFactoryBean.java:169)
	at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean.getTarget(AbstractJobRepositoryFactoryBean.java:139)
	at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean.initializeProxy(AbstractJobRepositoryFactoryBean.java:129)
	at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean.afterPropertiesSet(AbstractJobRepositoryFactoryBean.java:135)
	at org.springframework.batch.core.repository.support.JobRepositoryFactoryBean.afterPropertiesSet(JobRepositoryFactoryBean.java:126)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1369)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1335)
	... 17 more

can you please correct me............
can any body correct me here..........

**Rams** · May 16th, 2011, 08:14 AM

any body can help here....

**arno** · May 17th, 2011, 06:58 AM

you're missing the StAX API, looks like you're running on Java 5. Upgrade to Java 6 or add the StAX API JAR to your project.

**Rams** · May 17th, 2011, 08:51 AM

Hi arno,

I have added Stax api.jar in my project libraries. Now i am getting the following error. It is complaining sequence does not exist.
Actaully i have mensioned some DB instance the above. Those instances are enough or do i neet do create separate instance for this..
pls let me know...

Code:

ERROR - Job Terminated in error:
org.springframework.dao.DataAccessResourceFailureException: Could not obtain sequence value; nested exception is java.sql.SQLException: ORA-02289: sequence does not exist

	at org.springframework.jdbc.support.incrementer.AbstractSequenceMaxValueIncrementer.getNextKey(AbstractSequenceMaxValueIncrementer.java:78)
	at org.springframework.jdbc.support.incrementer.AbstractDataFieldMaxValueIncrementer.nextLongValue(AbstractDataFieldMaxValueIncrementer.java:125)
	at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:110)
	at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:127)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy0.createJobExecution(Unknown Source)
	at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:102)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.start(CommandLineJobRunner.java:207)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.main(CommandLineJobRunner.java:254)
	at FileToTableMain.main(FileToTableMain.java:8)
Caused by: java.sql.SQLException: ORA-02289: sequence does not exist

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
	at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:801)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
	at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:841)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
	at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1274)
	at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
	at org.springframework.jdbc.support.incrementer.AbstractSequenceMaxValueIncrementer.getNextKey(AbstractSequenceMaxValueIncrementer.java:69)
	... 18 more

**arno** · May 18th, 2011, 03:39 AM

I think you didn't use the correct file to create the batch tables. There's a file for Oracle (schema-oracle10g.sql), it uses sequences and not tables to generate the IDs. Drop the tables and use the correct file.

**Rams** · May 18th, 2011, 04:37 AM

Hi arno,

i had droped all my previous tables and i had executed the "schema-oracle10g.sql" script and now i am getting the following error.

Code:

ERROR - Job Terminated in error:
org.springframework.dao.CannotSerializeTransactionException: PreparedStatementCallback; SQL [INSERT into batch_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; ORA-08177: can't serialize access for this transaction
; nested exception is java.sql.SQLException: ORA-08177: can't serialize access for this transaction

	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:252)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:850)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:854)
	at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:117)
	at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:127)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy0.createJobExecution(Unknown Source)
	at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:102)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.start(CommandLineJobRunner.java:207)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.main(CommandLineJobRunner.java:254)
	at FileToTableMain.main(FileToTableMain.java:8)
Caused by: java.sql.SQLException: ORA-08177: can't serialize access for this transaction

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3423)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:798)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
	... 20 more

**arno** · May 18th, 2011, 04:41 AM

try to set the isolationLevelForCreate to ISOLATION_REPEATABLE_READ in the job repository bean.

Thread: Could not open JDBC Connection for transaction java.sql.SQLException: ORA-00923:

Thread Tools

Display

Spring Table

Posting Permissions