Page 1 of 1
connector received Oracle error code ORA-1406
Posted: Mon Sep 16, 2013 7:41 am
by aschindler
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.
Posted: Mon Sep 16, 2013 7:45 am
by chulett
What "all possible ways" have you tried?
Posted: Mon Sep 16, 2013 7:53 am
by aschindler
changed length as per target size and tried removing abs() function.
but still facing error.
Posted: Mon Sep 16, 2013 7:58 am
by chulett
What is the datatype / precision / scale of the column this will end up in? ABS() isn't part of the problem, btw.
Posted: Mon Sep 16, 2013 8:11 am
by aschindler
Thanks for instant reply,
Target column datatype: Decimal [38] [10]
Its not one to one mapping,doing some calculation in between.
Posted: Mon Sep 16, 2013 8:57 am
by priyadarshikunal
Did you try using cast()? I think avg() returns double and you will have to use cast to make it decimal.
Posted: Mon Sep 16, 2013 9:25 am
by chulett
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.
Posted: Mon Sep 16, 2013 9:55 am
by ArndW
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.
Posted: Mon Sep 16, 2013 10:13 pm
by DS_SUPPORT
Hi Chulett,
In oracle database column size is [20][4] Number.
ArndW,
Currently i am using like that only.
Posted: Mon Sep 16, 2013 10:22 pm
by chulett
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.
Posted: Tue Sep 17, 2013 12:28 am
by aschindler
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.
Posted: Tue Sep 17, 2013 12:45 am
by aschindler
Also in source oracle connector stage datatype is Decimal [38][10].
Posted: Tue Sep 17, 2013 3:55 am
by ArndW
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.
Posted: Tue Sep 17, 2013 4:27 am
by ray.wurlod
Just for grins, what does the error code mean? You can research this using the oerr -1406 command.
Posted: Tue Sep 17, 2013 7:10 am
by chulett
It's the dreaded "truncated during fetch" error, hence my questions.