OCI truncated Date

Anand K · Post by **Anand K** » Tue Sep 14, 2004 6:06 am

Hi All...

I am posting a query that has been already discussed in this forum. But none of the answers were helpful in my case. It is the warning message given within DataStage "OCI has truncated data".. I found and tried all the answers - trimming the fields, making the number 30 to 40 etc.. But still it is not helping in my case..

Please help me regarding the same ASAP

Thanks in advance

Anand K

chulett · Post by **chulett** » Tue Sep 14, 2004 7:08 am

Double-check everything, as those are the patented answers to this problem. What kind of problematic fields are included in your table? NUMBER? LONG? Perhaps if you listed the Oracle fields and how you have them defined in DataStage, people could help spot the problem child.

Anand K · Post by **Anand K** » Tue Sep 14, 2004 10:27 pm

chulett wrote:Double-check everything, as those are the patented answers to this problem. What kind of problematic fields are included in your table? NUMBER? LONG? Perhaps if you listed the Oracle fields and how you have them defined in DataStage, people could help spot the problem child.

I have NUMBER, VARCHAR and TIMESTAMP fields which I select from the Oracle tables. Some of them are "Computed Columns" added through DataStage. I have made the corresponding datatype and size changes. I have double checked each and every field but no luck so far :cry: Any help would be highly appreciated...

Thanks
Anand K

ray.wurlod · Post by **ray.wurlod** » Tue Sep 14, 2004 11:07 pm

What are reported in the following situations?

(a) importing the table definition through the OCI plug-in, what data types are reported?

(b) do you get the same message from Oracle when using something else such as sqlplus or TOAD?

chulett · Post by **chulett** » Tue Sep 14, 2004 11:30 pm

The question still remains - specifically, how are they defined in Oracle? In DataStage? Heck, what version of Oracle are you connecting to?

For example, if the column in Oracle is defined as a NUMBER, rather than say NUMBER(12), you can't define it with a size of 38 as you might expect. You need to define it as 40 in DataStage to avoid the truncation warning.

The TIMESTAMP field you mention - is it defined as a TIMESTAMP in Oracle, or as a DATE in Oracle and a Timestamp in DataStage? I don't believe the OCI stages support the new Timestamp field types yet, but I could be wrong.

I don't believe a varchar field will generate the 'truncated data' message, unless the source field is actually some form of a LONG in Oracle.

All in all, not enough details to provide any specific help.

Anand K · Post by **Anand K** » Wed Sep 15, 2004 12:46 am

The SQL did NOT give an error while executed through TOAD and SQLPLUS. I am using ORACLE 8i and in that the fields are defined as NUMBER only. There is no size specified for these in ORACLE. In DataStage, when I imported the fields, it was by default 38 for these fields. And after going through the forum I changed these values to 40.
Also some of the fields are defined as DATE in ORACLE and TIMESTAMP in DataStage

Kindly suggest how to proceed.

Thanks

Anand K

chulett · Post by **chulett** » Wed Sep 15, 2004 7:17 am

Anand K wrote:The SQL did NOT give an error while executed through TOAD and SQLPLUS.

It wouldn't... or I should say shouldn't. This is an issue with OCI. I'm guessing Ray just wanted to make sure your SQL was correct and that there wasn't another issue lurking here.

Anand also wrote:I am using ORACLE 8i and in that the fields are defined as NUMBER only. There is no size specified for these in ORACLE. In DataStage, when I imported the fields, it was by default 38 for these fields. And after going through the forum I changed these values to 40.

These should be your culprit. At this point, I'd triple-check check all of them... all it takes is one missed one to get this error. Make sure both the Size and the Display Sizes have been changed to 40, just to be safe.

It may help to bring over another pair of eyes. I recently struggled with a frustrating problem for way too long. I brought over someone else and they spotted the problem in two seconds.

Anand lastly wrote:Also some of the fields are defined as DATE in ORACLE and TIMESTAMP in DataStage.

These shouldn't be a problem. Or at least not related to this problem.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 15, 2004 4:17 pm

With no size, DataStage is trying to get at NUMBER(0) items. If they're integers, Oracle requires NUMBER(38) - not NUMBER(40). Try it with 38 instead of 40.

chulett · Post by **chulett** » Wed Sep 15, 2004 4:21 pm

Ray, while straight Oracle NUMBER fields are technically defined as 38, DataStage needs the metadata to be declared as 40 or it will give you the dreaded "OCI has fetched truncated data" error.

Mike · Post by **Mike** » Wed Sep 15, 2004 5:18 pm

I have never experienced the "OCI has fetched truncated data" error with the OCI stage. I have just used whatever was imported via Manager for the NUMBER data type (Decimal, Length=38, Display=40). Could it be platform-specific? I have only used the OCI stages on Solaris.

Mike

chulett · Post by **chulett** » Wed Sep 15, 2004 7:38 pm

It could be platform and/or version related. Sometimes they import as Length=38, Display=38 and that's when you can have problems.

I've also had the problem with LONG fields. You can select them into VarChars in DataStage all day, as long as the data itself doesn't exceed the size of the field in DataStage. Setup a 400 byte VarChar and the first time 401 bytes come in from the LONG - "OCI has fetched truncated data".

ray.wurlod · Post by **ray.wurlod** » Thu Sep 16, 2004 12:59 am

Here we're having no problem with NUMBER(38) in the Dynamic Relational Stage with access method set to "Oracle" (which uses OCI software). Datatage 7.1, in case anyone's interested.