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.XmlBeanDefinitionReader - 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.SQLErrorCodesFactory - 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.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124)
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.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.JdbcStepExecutionDao.saveStepExecution(JdbcStepExecutionDao.java:189)
	at org.springframework.batch.core.repository.support.SimpleJobRepository.saveOrUpdate(SimpleJobRepository.java:235)
	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:310)
	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.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.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.UnexpectedJobExecutionException: Failed to initialize the step
	at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:166)
	at org.springframework.batch.core.job.SimpleJob.execute(SimpleJob.java:125)
	at no.aftenposten.accountbalancer.spring.ClassPathXmlApplicationContextJobFactory$ContextClosingJob.execute(ClassPathXmlApplicationContextJobFactory.java:92)
	at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:86)
	at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:49)
	at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:81)
	at no.aftenposten.accountbalancer.spring.JobStarterImpl.startBatch(JobStarterImpl.java:149)
	at no.aftenposten.accountbalancer.spring.JobStarterImpl.batchStarterRun(JobStarterImpl.java:272)
	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:310)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
	at $Proxy44.batchStarterRun(Unknown Source)
	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 sun.reflect.misc.Trampoline.invoke(MethodUtil.java:36)
	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 sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:243)
	at javax.management.modelmbean.RequiredModelMBean.invokeMethod(RequiredModelMBean.java:1071)
	at javax.management.modelmbean.RequiredModelMBean.invoke(RequiredModelMBean.java:953)
	at org.springframework.jmx.export.SpringModelMBean.invoke(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_MESSA GE" (actual: 3082, maximum: 2500)
; nested exception is java.sql.SQLException: ORA-12899: value too large for column "DEVGPS2"."GP_BATCHSTEP_EXECUTION"."EXIT_MESSA GE" (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.