connector received Oracle error code ORA-1406

aschindler · Post by **aschindler** » Mon Sep 16, 2013 7:41 am

Hi Team,
Derivation for one of the column in my oracle connector is as follows:
AVG( NVL(C1, 0) ) * AVG(
CASE WHEN C2 <= 0 THEN ABS(C2) ELSE 0 END )

When i run job,getting below error message:

While reading data for column COLUMN_NAME, the connector received Oracle error code ORA-1406. (CC_OraStatement::logArrayReturnCodes, file CC_OraStatement.cpp, line 4155)

Tried all the possible ways ,Please help in resolving this error.

chulett · Post by **chulett** » Mon Sep 16, 2013 7:45 am

What "all possible ways" have you tried?

aschindler · Post by **aschindler** » Mon Sep 16, 2013 7:53 am

changed length as per target size and tried removing abs() function.
but still facing error.

chulett · Post by **chulett** » Mon Sep 16, 2013 7:58 am

What is the datatype / precision / scale of the column this will end up in? ABS() isn't part of the problem, btw.

aschindler · Post by **aschindler** » Mon Sep 16, 2013 8:11 am

Thanks for instant reply,
Target column datatype: Decimal [38] [10]
Its not one to one mapping,doing some calculation in between.

priyadarshikunal · Post by **priyadarshikunal** » Mon Sep 16, 2013 8:57 am

Did you try using cast()? I think avg() returns double and you will have to use cast to make it decimal.

chulett · Post by **chulett** » Mon Sep 16, 2013 9:25 am

Wasn't asking about the "target" column as in the end state, I mean the column it will land in first, the one in your source stage. That's the one it is complaining about.

ArndW · Post by **ArndW** » Mon Sep 16, 2013 9:55 am

You should have your Oracle definition something like

Code: Select all

AVG( NVL(C1, 0) ) * AVG( CASE WHEN C2 <= 0 THEN ABS(C2) ELSE 0 END ) AS MyColumn

and use the name "MyColumn" in your source stage.

DS_SUPPORT · Post by **DS_SUPPORT** » Mon Sep 16, 2013 10:13 pm

Hi Chulett,

In oracle database column size is [20][4] Number.

ArndW,
Currently i am using like that only.

chulett · Post by **chulett** » Mon Sep 16, 2013 10:22 pm

What it is in Oracle isn't the question, it might be if you were just selecting a column but you're not. Instead you are building a logical or derived column based on "C1" and "C2" with a mixture of functions so Oracle just assumes the worst and goes big.

Still trying to understand what data type and size you are using in the source stage for your "as MyColumn" field... not convinced that's been properly answered yet.

aschindler · Post by **aschindler** » Tue Sep 17, 2013 12:28 am

but i doubt if we give derivation alias name and column in source stage with different names, it may through error.
MyColumn - MyNewColumn

Please elaborate.

aschindler · Post by **aschindler** » Tue Sep 17, 2013 12:45 am

Also in source oracle connector stage datatype is Decimal [38][10].

ArndW · Post by **ArndW** » Tue Sep 17, 2013 3:55 am

I corrected my code typo above. But it remains that you are creating a new column through your case and you need to know what that derived data type is. I just read that Craig has already suggested this. You might perform an explicit CAST() on this result and make sure you use the same definition in DataStage.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 17, 2013 4:27 am

Just for grins, what does the error code mean? You can research this using the oerr -1406 command.

chulett · Post by **chulett** » Tue Sep 17, 2013 7:10 am

It's the dreaded "truncated during fetch" error, hence my questions.