OCI has fetched truncated data

pradkumar · Post by **pradkumar** » Thu Jan 04, 2007 12:11 pm

I am trying to extract a large volume of data.
When I run the job for 1000 records, it is running fine.
But when I run it for more than 1000 it is running but with a warning " OCI has fetched truncated data"

I encountered this error before when I was loading the data year wise. I thought, "since I was loading only one year data from a large pool of data it is giving me such a warning and I ignored it"

Any Comments on this plz?

chulett · Post by **chulett** » Thu Jan 04, 2007 12:14 pm

Search the forum, causes for this have been covered a number of times.

kcbland · Post by **kcbland** » Thu Jan 04, 2007 12:39 pm

Your metadata is wrong. Re-import the metadata and use that. If the messages continue, then you have to figure out which columns don't match the data. Sometimes metadata shows no decimal scale, but the actual data contains decimals. You'll have to set the decimal scale manually in the job to give you the correct answers.

pradkumar · Post by **pradkumar** » Thu Jan 04, 2007 7:56 pm

I figured out that there are four amount columns in my job which are cuasing this error.
Acc_DR -->1000, 2987.76
Acc_CR---->291.13, Null(just empty space)
Entered_DR-->234.09,23
Entered_CR-->459.09,-900.20

and all the above four are numeric data type with length of 38 in the SOURCE and TARGET.
I manually changed scale to 2 in the target, but it was no use..

Any ideas over this issue ?

kcbland · Post by **kcbland** » Thu Jan 04, 2007 8:02 pm

Your error messaage is on the SQL selecting the data out of the source. The metadata says NUMERIC 38 so when DS binds to the Oracle client, any decimal values have to be truncated.

Change the metadata in the OCI stage selecting from Oracle. Twiddling the scale will fix your warning message, plus give you data with decimals. This is a problem where data modelers create tables using loose datatyping.

pradkumar · Post by **pradkumar** » Thu Jan 04, 2007 8:08 pm

I changed the data type to Decimal in source. But still warnings are comingI am using a scale of 1.
Shall I use scale in my source and all other transformer stages following it or is it just enough if I change in source (OCI stage).

Is there any range of scale values I have to use or its simply trial and error.

pradkumar · Post by **pradkumar** » Thu Jan 04, 2007 9:06 pm

I solved the above problem by using Trunc(Columnname,2) in the source itself.

But now I am facing a DS Phantom error when I am trying to do the following:

1) I am trying to calculate the balance amount as:

Code: Select all

ICONV(ACCOUNT_DR,"MD2")-->STAGE VARAIBLE 1
ICONV(ACCOUT_CR,"MD2")-->STAGE VARIABLE 2

OCONV(STAGE VARIABLE1- STAGE VARIABLE2, "MD2")

I am getting the following phantom error:
Phantom 9673
Program "JOB.811108259.DT.1424949098.TRANS2": Line 300, Improper data type.

kcbland · Post by **kcbland** » Thu Jan 04, 2007 9:25 pm

You don't need to ICONV numerics to subtract. Just subtract them. To format and round, just use OCONV(value, "MDXY") where X is the decimal scale to round and Y is the shift for implied decimals.

Improper datatype means a NULL was used in a math expression.

pradkumar · Post by **pradkumar** » Thu Jan 04, 2007 9:29 pm

Thanks a lot..

There were few null values,
I used in source itself NVL now and now its working fine without Iconv and Oconv

DSguru2B · Post by **DSguru2B** » Thu Jan 04, 2007 9:38 pm

Great. Thanks for sharing that with us. Now you can mark the post resolved. Others should make it a habbit to mark their posts resolved for ease of search.