spring batch - CLOB oracle database and c3p0 Error

[shriny]

We are getting the following error in app using spring batch,

Caused by: org.springframework.dao.InvalidDataAccessApiUsageE xception: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [com.mchange.v2.c3p0.impl.NewProxyConnection]: specify a corresponding NativeJdbcExtractor; nested exception is java.lang.ClassCastException: com.mchange.v2.c3p0.impl.NewProxyConnection cannot be cast to oracle.jdbc.OracleConnection

This happens when spring batch persisting CLOB objects to database.

Any one seen in this problem earlier?

We use c3p0 connection pool and spring batch 2.1

Here is the full stack trace

Caused by: org.springframework.dao.InvalidDataAccessApiUsageE xception: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [com.mchange.v2.c3p0.impl.NewProxyConnection]: specify a corresponding NativeJdbcExtractor; nested exception is java.lang.ClassCastException: com.mchange.v2.c3p0.impl.NewProxyConnection cannot be cast to oracle.jdbc.OracleConnection
at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.createLob(OracleLobHandler.ja va:357)
at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.setClobAsString(OracleLobHand ler.java:265)
at org.springframework.batch.core.repository.dao.Jdbc ExecutionContextDao$1.setValues(JdbcExecutionConte xtDao.java:197)
at org.springframework.jdbc.core.JdbcTemplate$2.doInP reparedStatement(JdbcTemplate.java:796)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:591)
at org.springframework.jdbc.core.JdbcTemplate.update( JdbcTemplate.java:792)
at org.springframework.jdbc.core.JdbcTemplate.update( JdbcTemplate.java:850)
at org.springframework.batch.core.repository.dao.Jdbc ExecutionContextDao.persistSerializedContext(JdbcE xecutionContextDao.java:193)
at org.springframework.batch.core.repository.dao.Jdbc ExecutionContextDao.updateExecutionContext(JdbcExe cutionContextDao.java:124)
at org.springframework.batch.core.repository.support. SimpleJobRepository.updateExecutionContext(SimpleJ obRepository.java:182)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoi npointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethod Invocation.invokeJoinpoint(ReflectiveMethodInvocat ion.java:182)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :149)
at org.springframework.transaction.interceptor.Transa ctionInterceptor.invoke(TransactionInterceptor.jav a:106)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :171)
at org.springframework.aop.framework.JdkDynamicAopPro xy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy40.updateExecutionContext(Unknown Source)
at org.springframework.batch.core.job.SimpleStepHandl er.handleStep(SimpleStepHandler.java:125)
at org.springframework.batch.core.job.flow.JobFlowExe cutor.executeStep(JobFlowExecutor.java:61)
at org.springframework.batch.core.job.flow.support.st ate.StepState.handle(StepState.java:60)
at org.springframework.batch.core.job.flow.support.Si mpleFlow.resume(SimpleFlow.java:144)
... 5 more
Caused by: java.lang.ClassCastException: com.mchange.v2.c3p0.impl.NewProxyConnection cannot be cast to oracle.jdbc.OracleConnection
at oracle.sql.CLOB.createTemporary(CLOB.java:754)
at oracle.sql.CLOB.createTemporary(CLOB.java:716)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.prepareLob(OracleLobHandler.j ava:393)
at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.createLob(OracleLobHandler.ja va:340)
... 29 mo

[Dave Syer]

There's a clue in the logging message: "specify a corresponding NativeJdbcExtractor". You inject it into the LobHandler and it knows about the connection pool.

[sisays]

Hi Dave,

I am getting the same kind of exception:

DEBUG [org.springframework.batch.core.step.item.SimpleRet ryExceptionHandler] - <Handled non-fatal exception>
org.springframework.dao.InvalidDataAccessApiUsageE xception: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [com.mchange.v2.c3p0.impl.NewProxyConnection]: specify a corresponding NativeJdbcExtractor; nested exception is java.lang.ClassCastException: com.mchange.v2.c3p0.impl.NewProxyConnection cannot be cast to oracle.jdbc.OracleConnection
at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.createLob(OracleLobHandler.ja va:511)
...

Caused by: java.lang.ClassCastException: com.mchange.v2.c3p0.impl.NewProxyConnection cannot be cast to oracle.jdbc.OracleConnection
at oracle.sql.CLOB.createTemporary(CLOB.java:754)

I don't see how to inject the NativeJdbcExtractor into the LobHandler. Could you please post some more details?

(c3p0 version 0.9.1.2 | Spring Batch 2.1.0)

Shriny, did you get it solved?

[shriny]

Yep, This one fixed it




<bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc .C3P0NativeJdbcExtractor"/>

<bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.Oracle LobHandler">
<property name="nativeJdbcExtractor">
<ref local="nativeJdbcExtractor"/>
</property>
</bean>

<bean id="jobRepository"
class="org.springframework.batch.core.repository.s upport.JobRepositoryFactoryBean">
<property name="lobHandler" ref="oracleLobHandler"/>
.......
</bean>

[Solarhersteller]

I am simply unable to do a complete update (in Eclipse, TortoiseCVS, or
command-line) from one of my computers, while from my laptop, on the
same network, but connected via wireless, I can update ok. Same software
being used everything (although base OS is XP SP1 on the wired computer,
and SP2 on the laptop). On the wired PC, I used to be able to cancel and
retry, an would manage to get the update to finish about one time in
maybe 5-6 attempts, but now even that is not working.

Has anybody seen anything similar?