ORA-01406: fetched column value was truncated - error reading a LONG RAW in Oracle

Question

I have a Spring Batch job configured that is setup to read data from an Oracle database. I am running into ORA-01406 error message stating that the fetched column value was truncated. Following are some additional details and some trouble shooting I have done so far

The data is being retrieved from a database that is configured as a DBLINK (external database)
The column being retrieved is defined as LONG RAW dataype
The query being used to retrieve the data is used in SqlPagingQueryProviderFactoryBean
I tried having the Mapper try to map it as String, BinaryStream and always see this error message

The interesting part is that when I use a query() on JdbcTemplate and use a simple query like below, I have no problem retrieving the value SELECT MY_COLUMN FROM SOME_TABLE WHERE SOME CONDITION;

Does someone know if Spring Batch has any issues retrieving the LONG RAW columns? OR how can this be fixed?

Following is the stack trace

DEBUG JdbcStepExecutionDao - Truncating long message before update of StepExecution, original message is: org.springframework.batch.core.step.skip.NonSkippableReadException: Non-skippable exception during read
at org.springframework.batch.core.step.item.FaultTolerantChunkProvider.read(FaultTolerantChunkProvider.java:104)
at org.springframework.batch.core.step.item.SimpleChunkProvider$1.doInIteration(SimpleChunkProvider.java:114)
at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368)
at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)
at org.springframework.batch.core.step.item.SimpleChunkProvider.provide(SimpleChunkProvider.java:108)
at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:69)
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:395)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:267)
at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:77)
at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368)
at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)
at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:253)
at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:195)
at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:141)
at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64)
at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:60)
at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:151)
at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:130)
at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:135)
at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:301)
at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:134)
at java.lang.Thread.run(Unknown Source)
Caused by: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [SELECT * FROM (SELECT MY_COLUMN FROM SOME_TABLE WHERE SOME CONDITION) ORDER BY SOME_COLUMN ASC) WHERE ROWNUM <= 100]; ORA-01406: fetched column value was truncated

; nested exception is java.sql.SQLDataException: ORA-01406: fetched column value was truncated

at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:413)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:468)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:478)
at org.springframework.batch.item.database.JdbcPagingItemReader.doReadPage(JdbcPagingItemReader.java:210)
at org.springframework.batch.item.database.AbstractPagingItemReader.doRead(AbstractPagingItemReader.java:108)
at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.read(AbstractItemCountingItemStreamItemReader.java:83)
at org.springframework.batch.core.step.item.SimpleChunkProvider.doRead(SimpleChunkProvider.java:91)
at org.springframework.batch.core.step.item.FaultTolerantChunkProvider.read(FaultTolerantChunkProvider.java:87)
... 24 more
Caused by: java.sql.SQLDataException: ORA-01406: fetched column value was truncated

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:999)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1185)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1477)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:392)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:452)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:402)
... 31 more

Code snipet please............. spring batch configuration would be helpfull — Karthik Prasad, Jun 11 '14 at 10:32

score 1 · Accepted Answer · answered Jul 18 '14 at 05:00

1

After much research and trials, I ended up retrieving the LONG RAW data type as a byte[]. Following is the relevant code that I wrote to retrieve the data

    public byte[] getALongRawText(final SomeObject someObject) {

    byte[] result = myJdbcTemplate.query(getALongRawTextSql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setLong(1, someObject.getProperty1());
                    preparedStatement.setLong(2, someObject.getProperty2());
                }
            }, new ResultSetExtractor<byte[]>() {
                @Override
                public byte[] extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                    if(resultSet.next()) {
                        return resultSet.getBytes(1);
                    }
                    return null;
                }
            });      

    return result;
}

answered Jul 18 '14 at 05:00

mekatoka

263
2
5
11

Did you ever determine what the exact cause was? We have a very similar exception, although from DB2, on a SELECT. It only fails when we try to compare a 20 character column with a parameter passed in that is > 20 characters. Seems weird that DB2 would through a truncation error on a passed-in parameter. – CodeChimp Jul 28 '14 at 14:00
No, I did not. To me it appears to be an issue of Spring Batch. I am not sure if internally, it tries to convert a LONG RAW type to a String. LONG RAW is archaic data type and it should not be used anyway but we had to use it due to a legacy system. – mekatoka Aug 09 '14 at 16:43
1

In our case, what seems to be happening is that the DB2 driver sets up defined parameters when doing a Prepared Statement through the JDBC drivers. For varchar, it seems that it is using the Meta Data on the column to determine the length of said defined parameter. In our case, we are trying to compare a 21 character length string to a 20 character length defined parameter. This causes the truncation error. When we ran it manually, we were not doing it as a prepared statement, which is why it worked in the console but not in code. – CodeChimp Aug 11 '14 at 11:27

ORA-01406: fetched column value was truncated - error reading a LONG RAW in Oracle

1 Answers1