Oracle Data Type of Number causing problems

[peternolan9]

Hi All,

I have a table where the column is just defined as 'number'. When the OCI reads in the metadata it sets it to number 38. But I believe that Oracle number can be 126 long. Is this correct?

The job fails with a message of oci retrieved truncated data when there are more than 50 such warnings. When I go in and set this one column to length 126 the job extracts the data ok......so, what length should I set in DataStage for any column that is just defined to be 'number' in the source Oracle 9.2 system?

Also, we are testing ODBC. We are using the DD Oracle Wire drivers and on this same number field I get messages warning of data truncation. Only, since they are information messages in ODBC I get 35,000 of them!!!! I remove the 'number' column from the input query and all is ok. But if I set it to double 126 it still issues the warning message, strangely enough....

Anyone have any experience of source Oracle columns being set to just number? Another message DS issues is 'expected number received double' so it thinks it is getting a double rather than a number. I went and queried the table itself and it thinks it has a lot of values of length 40.

The field in question is an exchange rate that has a lot of decimal places in it.....


Thanks

Peter Nolan

[chulett]

There was a discussion recently on ADN on this very subject. Oracle numbers are 38 unless specified as something smaller. If you have access to that site, then here is a link to the thread.

Long story short, in spite of the numbers actually being 38 you need to declare them as '40' to stop the truncation messages.