ORA-01461: can bind a LONG value only for insert into a LONG column

[geira]

I am getting the message below from the batch-framework. I am running a configuration with several steps, - and gets this errormessage during processing. I am running on an Oracle 10g db.

Geir



2008-06-09 15:43:48,239 [ERROR ] [Thread-5 ] org.springframework.batch.core.step.AbstractStep - Encountered an error executing the step
2008-06-09 15:43:48,339 [INFO ] [Thread-5 ] org.springframework.beans.factory.xml.XmlBeanDefin itionReader - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2008-06-09 15:43:48,434 [INFO ] [Thread-5 ] org.springframework.jdbc.support.SQLErrorCodesFact ory - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
2008-06-09 15:43:55,818 [ERROR ] [Thread-5 ] org.springframework.batch.core.step.AbstractStep - Encountered an error saving batch meta data.This job is now in an unknown state and should not be restarted.
org.springframework.jdbc.UncategorizedSQLException : PreparedStatementCallback; uncategorized SQLException for SQL [INSERT into BATCH_STEP_EXECUTION(STEP_EXECUTION_ID, VERSION, STEP_NAME, JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS, COMMIT_COUNT, ITEM_COUNT, CONTINUABLE, EXIT_CODE, EXIT_MESSAGE) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; SQL state [72000]; error code [1461]; ORA-01461: can bind a LONG value only for insert into a LONG column
; nested exception is java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

at org.springframework.jdbc.support.SQLStateSQLExcept ionTranslator.translate(SQLStateSQLExceptionTransl ator.java:124)
at org.springframework.jdbc.support.SQLErrorCodeSQLEx ceptionTranslator.translate(SQLErrorCodeSQLExcepti onTranslator.java:322)
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.Jdbc StepExecutionDao.saveStepExecution(JdbcStepExecuti onDao.java:189)
at org.springframework.batch.core.repository.support. SimpleJobRepository.saveOrUpdate(SimpleJobReposito ry.java:235)
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:585)
at org.springframework.aop.support.AopUtils.invokeJoi npointUsingReflection(AopUtils.java:310)
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.interceptor.ExposeInvocati onInterceptor.invoke(ExposeInvocationInterceptor.j ava:89)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :171)
at org.springframework.aop.framework.JdkDynamicAopPro xy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy7.saveOrUpdate(Unknown Source)

[lucasward]

I believe this was an error in the schemas in 1.0.1:

http://jira.springframework.org/browse/BATCH-609

You should be able to change the column type to fix the issue.

[geira]

I am already using the schema scripts embedded in the 1.0.1 version of the batch, and the LONG_VAL is already declared as NUMBER.

Could the problem be the line: ps.setLong(6, ((Long) value).longValue()); in JdbcStepExecutionDao setting a long to a Number datatype?




CREATE TABLE BATCH_STEP_EXECUTION_CONTEXT (
STEP_EXECUTION_ID NUMBER(38) NOT NULL ,
TYPE_CD VARCHAR2(6) NOT NULL ,
KEY_NAME VARCHAR2(1000) NOT NULL ,
STRING_VAL VARCHAR2(1000) ,
DATE_VAL TIMESTAMP DEFAULT NULL ,
LONG_VAL NUMBER(38) ,
DOUBLE_VAL NUMBER ,
OBJECT_VAL BLOB,
constraint STEP_EXECUTION_CONTEXT_FK foreign key (STEP_EXECUTION_ID)
references BATCH_STEP_EXECUTION(STEP_EXECUTION_ID)
) ;

[Dave Syer]

What Oracle data type works for you?

[geira]

I have found the the reason for this exception.

An error had occurred during processing in one of the steps:



org.springframework.batch.core.UnexpectedJobExecut ionException: Failed to initialize the step
at org.springframework.batch.core.step.AbstractStep.e xecute(AbstractStep.java:166)
at org.springframework.batch.core.job.SimpleJob.execu te(SimpleJob.java:125)
at no.aftenposten.accountbalancer.spring.ClassPathXml ApplicationContextJobFactory$ContextClosingJob.exe cute(ClassPathXmlApplicationContextJobFactory.java :92)
at org.springframework.batch.core.launch.support.Simp leJobLauncher$1.run(SimpleJobLauncher.java:86)
at org.springframework.core.task.SyncTaskExecutor.exe cute(SyncTaskExecutor.java:49)
at org.springframework.batch.core.launch.support.Simp leJobLauncher.run(SimpleJobLauncher.java:81)
at no.aftenposten.accountbalancer.spring.JobStarterIm pl.startBatch(JobStarterImpl.java:149)
at no.aftenposten.accountbalancer.spring.JobStarterIm pl.batchStarterRun(JobStarterImpl.java:272)
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:585)
at org.springframework.aop.support.AopUtils.invokeJoi npointUsingReflection(AopUtils.java:310)
at org.springframework.aop.framework.JdkDynamicAopPro xy.invoke(JdkDynamicAopProxy.java:198)
at $Proxy44.batchStarterRun(Unknown Source)
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:585)
at sun.reflect.misc.Trampoline.invoke(MethodUtil.java :36)
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:585)
at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java :243)
at javax.management.modelmbean.RequiredModelMBean.inv okeMethod(RequiredModelMBean.java:1071)
at javax.management.modelmbean.RequiredModelMBean.inv oke(RequiredModelMBean.java:953)
at org.springframework.jmx.export.SpringModelMBean.in voke(SpringModelMBean.java:88)
at com.sun.jmx.mbeanserver.DynamicMetaDataImpl.invoke (DynamicMetaDataImpl.java:213)
at com.sun.jmx.mbeanserver.MetaDataImp



The stacktrace was tried to be stored in the method saveStepExecution in the JdbcStepExecutionDao. The field EXIT_MESSAGE in BATCH_STEP_EXECUTION has a max size on 2500. The stacktrace had a size on above 4000. This was causing this confusing errormessage.


I made a quick tempory fix:



stepExecution.getExitStatus().getExitDescription() .length() > EXIT_MESSAGE_MAX_LENGTH ?
stepExecution.getExitStatus().getExitDescription() .substring(0, EXIT_MESSAGE_MAX_LENGTH -1):
stepExecution.getExitStatus().getExitDescription()


Is this a something which should be handled somewhere else?

[Dave Syer]

I think the message overflow only happens if there is an exception before any business processing (e.g. in ItemStream.open()). It should be fixed in the trunk (I can't find the JIRA issue quickly, but it's there somewhere, and I remember Robert fixed it).

[hailspring]

Now I am getting this error:

Caused by: org.springframework.jdbc.UncategorizedSQLException : PreparedStatementCallback; uncategorized SQLException for SQL [INSERT into GP_BATCHSTEP_EXECUTION(STEP_EXECUTION_ID, VERSION, STEP_NAME, JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS, COMMIT_COUNT, ITEM_COUNT, CONTINUABLE, EXIT_CODE, EXIT_MESSAGE) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; SQL state [72000]; error code [12899]; ORA-12899: value too large for column "DEVGPS2"."GP_BATCHSTEP_EXECUTION"."EXIT_MESSAGE" (actual: 3082, maximum: 2500)
; nested exception is java.sql.SQLException: ORA-12899: value too large for column "DEVGPS2"."GP_BATCHSTEP_EXECUTION"."EXIT_MESSAGE" (actual: 3082, maximum: 2500)


I am using Spring Batch 1.0.1, is this a known issue??

[lucasward]

It was a known issue, but I'm not sure which version fixed it, I would have to check in jira. I believe it was fixed in 1.1 though.