OCI has fetched truncated data

sreeni · Post by **sreeni** » Mon Dec 10, 2007 12:54 am

Hi,

I know it has been posted in the past. These posts helped me to solve my problem.

we are using Oracle database as a source, one column data type in Oracle table is "date". when we are trying to retrieve data from this date column by using OCI stage, it is giving error "OCI has fetched truncated data". But we declared datatype for this date column is "char(10)" in OCI stage. It is working in pre-prod environment but it is giving error in prod environment. After changed the datatype from char(10) to Timestamp, it is working in prod environment also.

why it is still working in pre-prod environment?

we verified database level date formate settings in prod and pre-prod, both are same.

we verified datastage level NLS property tab is disabled in prod and pre-prod.

please help me regarding the same.

Thanks

ArndW · Post by **ArndW** » Mon Dec 10, 2007 3:39 am

Can you explain how a TimeStamp value can fit into CHAR(10) with an example where there is no truncation of data occurring during the conversion? Your search should be more into why the development environment is not producing this error.

sreeni · Post by **sreeni** » Mon Dec 10, 2007 4:14 am

Sorry, I changed column datatype from Char(10) to Date(10).
do I need to verify any datastage specific properties?

ray.wurlod · Post by **ray.wurlod** » Mon Dec 10, 2007 1:04 pm

The point is that a timestamp has at least 19 characters, not 10.

sreeni · Post by **sreeni** » Tue Dec 11, 2007 12:04 am

Thanks for the clarification.

I can see the NLS tab is disabled in datastage administrator.
where can I see the default date format settings in datastage server?

ray.wurlod · Post by **ray.wurlod** » Tue Dec 11, 2007 1:43 am

Ultimately in a file called msg.text in the DataStage engine directory. However, different databases have different default formats.

sreeni · Post by **sreeni** » Tue Dec 11, 2007 6:35 am

Thanks for spending your valueble time, I tried to get the more information regarding date default settings in msg.txt file. I couldn't get any info. I am able to see some sample below entries in msg.txt file.

DLM0040 Process sig (%d) has date of %ld\n
GNS0002 genser: cannot decode date\n
UVR0018 to expiry date of %s for %d CPU's\n
UVR0012 %-15s package is installed. \n\tExpiry date = %s\n
UVR0003 Only one date option may be specified\n

can you give me more details on date default settings?

ray.wurlod · Post by **ray.wurlod** » Wed Dec 12, 2007 1:28 am

From memory, LOC0020

sreeni · Post by **sreeni** » Wed Dec 12, 2007 9:55 am

I got the below entry.

LOC0020 D

Can I know the meaning of the above entry?

ray.wurlod · Post by **ray.wurlod** » Wed Dec 12, 2007 7:20 pm

It means that the default date format is the one you would get by executing the following query.

Code: Select all

SELECT CURRENT_DATE CONV 'D' FMT '20L' FROM VOC FIRST 1;

sreeni · Post by **sreeni** » Thu Dec 13, 2007 6:02 am

I executed the query in prod and pre-prod datastage. It retruns same date format. So I need to dig into the prod and pre-prod databases.