Migration from SQLSERVER to ORACLE fails....

Hi,

I have a working solution for Spring batch which consists of couple of jobs that read data from files, web service, database and write to files, and/or databases.

Now, I wanted to switch from SQL Server to Oracle... So, I created required framework and application tables and changes the database type of factory bean as follows:

Code:

---

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

---

Question: What are the valid values for databaseType above????

However, I'm encountering two issues...
#1 I get following error when file writer steps is executed.

Code:

---

ERROR org.springframework.batch.core.job.AbstractJob - Encountered fatal error executing job
org.springframework.batch.core.JobExecutionException: Flow execution ended unexpectedly
        at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:141)
        at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:281)
        at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:120)
        at java.lang.Thread.run(Thread.java:811)
Caused by: org.springframework.batch.core.job.flow.FlowExecutionException: Ended flow=loadPeopleData at state=loadPeopleData.createFlatFile with exception
        at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:152)
        at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:124)
        at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:135)
        ... 3 more
Caused by: org.springframework.dao.DataAccessResourceFailureException: Could not create Oracle LOB; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Couldn't initialize OracleLobHandler because Oracle driver classes are not available. Note that OracleLobHandler requires Oracle JDBC driver 9i or higher!; nested exception is java.lang.ClassNotFoundException: oracle.sql.BLOB
        at org.springframework.jdbc.support.lob.OracleLobHandler$OracleLobCreator.createLob(OracleLobHandler.java:516)
        at org.springframework.jdbc.support.lob.OracleLobHandler$OracleLobCreator.setClobAsString(OracleLobHandler.java:413)
        at org.springframework.batch.core.repository.dao.JdbcExecutionContextDao$1.setValues(JdbcExecutionContextDao.java:197)
        at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:815)

---

#2 Even though file writing has failed with above exception, step status is COMPLETED and JOB status is failed.

The above error is coming because of CNF exception as follows:

Code:

---

Caused by: java.lang.ClassNotFoundException: oracle.sql.BLOB
        at org.eclipse.osgi.framework.internal.core.BundleLoader.findClass(BundleLoader.java:402)
        at org.eclipse.osgi.framework.internal.core.BundleLoader.findClass(BundleLoader.java:347)
        at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loadClass(DefaultClassLoader.java:83)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:606)
        at org.springframework.jdbc.support.lob.OracleLobHandler.initOracleDriverClasses(OracleLobHandler.java:181)
        ... 31 more

---

I'm using Oracle 10 driver, and the said file is there in the jar file that I've provided to my server.

JDBC driver name : Oracle JDBC driver
JDBC driver version : 10.2.0.1.0

I have 10.2.0.2 and that class is found and works fine. Maybe your classpath is screwed up some other way? If there a duplicate JAR in a parent classloader or something?

Thank you for replying, Dave.

You are right, it does look like a classpath issue. However, I don't think that I need to bundle it with my application. Also, to answer your question there is only one copy of ojdbc14.jar which is referenced using WebSphere Environemnt Variable by Oracle datasource provider.

Strange thing is that, the job has two steps as follows:

Step 1. Load external Data
a. read from webservice
b. write to Oracle database
Step 2: Generate Flat file
a. read from database
b. write to flat file

Everything until, Step 2a works fine and then it says Step 2b also COMPLETED. But the job status is failed with the following exception. I've included additional debug log which shows that the job was able to read data and started writing to the flat file and then fails after that?

So, it is not clear why at this point it should fail with Oracle class not found when the step has completed even though it is not because file is not generated?

Why it requires Oracle BLOB class to write a flat file? Is it loading everything into memory using BLOB?

Code:

---

DEBUG org.springframework.batch.repeat.support.RepeatTemplate - Repeat operation about to start at count=324
DEBUG org.springframework.batch.repeat.support.RepeatTemplate - Repeat is complete according to policy and result value.
DEBUG org.springframework.batch.item.file.FlatFileItemWriter - Writing to flat file with 323 items.
DEBUG org.springframework.batch.core.step.item.ChunkOrientedTasklet - Inputs not busy, ended: true
DEBUG org.springframework.batch.core.step.tasklet.TaskletStep - Applying contribution: [StepContribution: read=323, written=323, filtered=0, readSkips=0, writeSkips=0, processSkips=0, exitStatus=EXECUTING]
DEBUG org.springframework.batch.core.step.tasklet.TaskletStep - Saving step execution before commit: StepExecution: id=33, version=4, name=createFlatFile, status=STARTED, exitStatus=EXECUTING, readCount=3323, filterCount=0, writeCount=3323 readSkipCount=0, writeSkipCount=0, processSkipCount=0, commitCount=4, rollbackCount=0, exitDescription=
DEBUG org.springframework.batch.repeat.support.RepeatTemplate - Repeat is complete according to policy and result value.
DEBUG org.springframework.batch.core.step.AbstractStep - Step execution success: id=33
DEBUG org.springframework.batch.core.step.AbstractStep - Step execution complete: StepExecution: id=33, version=6, name=createFlatFile, status=COMPLETED, exitStatus=COMPLETED, readCount=3323, filterCount=0, writeCount=3323 readSkipCount=0, writeSkipCount=0, processSkipCount=0, commitCount=4, rollbackCount=0
ERROR org.springframework.batch.core.job.AbstractJob - Encountered fatal error executing job
org.springframework.batch.core.JobExecutionException: Flow execution ended unexpectedly
        at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:141)
        at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:281)
        at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:120)
        at java.lang.Thread.run(Thread.java:811)
Caused by: org.springframework.batch.core.job.flow.FlowExecutionException: Ended flow=loadPeopleData at state=loadPeopleData.createFlatFile with exception
        at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:152)
        at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:124)
        at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:135)
        ... 3 more
Caused by: org.springframework.dao.DataAccessResourceFailureException: Could not create Oracle LOB; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Couldn't initialize OracleLobHandler because Oracle driver classes are not available. Note that OracleLobHandler requires Oracle JDBC driver 9i or higher!; nested exception is java.lang.ClassNotFoundException: oracle.sql.BLOB
        at org.springframework.jdbc.support.lob.OracleLobHandler$OracleLobCreator.createLob(OracleLobHandler.java:516)
        at org.springframework.jdbc.support.lob.OracleLobHandler$OracleLobCreator.setClobAsString(OracleLobHandler.java:413)
        at org.springframework.batch.core.repository.dao.JdbcExecutionContextDao$1.setValues(JdbcExecutionContextDao.java:197)
        at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:815)
        at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:811)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:867)

---

This issue is documented and is still unresolved in JIRA https://jira.springsource.org/browse/SPR-5538

If anyone knows the solution let me know?