getting all 38 significant digits from NUMBER

peternolan9 · Post by **peternolan9** » Sat Nov 20, 2004 11:27 am

Hi All,
yes, me again....on Oracle 'NUMBER' fields in Oracle Applications.

We have some fields in OA that have more than 15 significant digits. The client wants ALL significant digits to come across to the DW.

But try as we might, using ORA OCI or ODBC every time we try to read one of these fields we know has more than 15 significant digits in it, it comes out of the driver with just 15 digits.

We are on 7.1 AIX reading Oracle 9.2. Has anyone else ever seen more than 15 significant digits come out of an Oracle database using DataStage?

Thanks in Advance

ray.wurlod · Post by **ray.wurlod** » Sat Nov 20, 2004 4:58 pm

What is the value of your EXACTNUMERIC tuneable?

You could always SELECT CAST(THE_NUMBER AS CHAR(38)) ... FROM ...

You don't need user-defined SQL for this. On the columns grid, after you've done the import, change the Derivation from schema.table.THE_NUMBER to CAST(schema.table.THE_NUMBER AS CHAR(38)) and also change the data type and precision to CHAR and 38 (and, if desired, the display width as well).

Use the description field to document the technique.

Inspect the generated SQL.

To test quickly go to the Selection tab and add WHERE ROWNUM <= 10 then use View Data to see what you get. Don't forget to remove this constraint before releasing for testing!

peternolan9 · Post by **peternolan9** » Sun Nov 21, 2004 8:55 am

Hi Ray,
a very good question....I'll have to ask tomorrow...I've done the cast and used varchar2(50) and that works fine, brings back whatever is in the oracle table......we are unable to load it into our oracle instance because it is being truncated to 22 significant digits...and when I look at all my tables they all have number limited to 22.....I think this could well be our 'EXACTNUMBER' tunable or some other tunable to set maximum precision of a NUMBER......we shall see...

Thanks for the tip...much appreciated!!!

ray.wurlod wrote:What is the value of your EXACTNUMERIC tuneable?

You could always SELECT CAST(THE_NUMBER AS CHAR(38)) ... FROM ...

You don't need user-defined SQL for this. On the columns grid, after you've done the import, change the Derivation from schema.table.THE_NUMBER to CAST(schema.table.THE_NUMBER AS CHAR(38)) and also change the data type and precision to CHAR and 38 (and, if desired, the display width as well).

Use the description field to document the technique.

Inspect the generated SQL.

To test quickly go to the Selection tab and add WHERE ROWNUM <= 10 then use View Data to see what you get. Don't forget to remove this constraint before releasing for testing!

ray.wurlod · Post by **ray.wurlod** » Sun Nov 21, 2004 2:46 pm

If the relevant columns in your tables are set to NUMBER(22) in the target that's not a DataStage problem.

If you send your data to a text file, are the numbers truncated to 22 digits? If not, are you doing any transformation that might limit them to 22? Indeed, are you certain that the source has 38 digits in all cases? DataStage will remove leading zeroes from numeric values, but you should still be able to load 42 into a NUMBER(38) column.

peternolan9 · Post by **peternolan9** » Tue Nov 23, 2004 10:40 am

Hi Ray,
the 22 seems to be inside the Oracle database...DataStage is sending the ful 38 digits to Oracle through the ODBC interface and the message is an oracle message saying num1 has been truncated to num2 where num1 and num2 are the full 38 digits and the truncated 22 digits.....I am almost certain this is an Oracle problem and DS is passing the correct information. I'll let you know if I find out how to get around it..

ray.wurlod wrote:If the relevant columns in your tables are set to NUMBER(22) in the target that's not a DataStage problem.

If you send your data to a text file, are the numbers truncated to 22 digits? If not, are you doing any transformation that might limit them to 22? Indeed, are you certain that the source has 38 digits in all cases? DataStage will remove leading zeroes from numeric values, but you should still be able to load 42 into a NUMBER(38) column.

peternolan9 · Post by **peternolan9** » Tue Nov 23, 2004 11:13 am

Hi Ray,
the 22 comes from the maximum size of a 38 digit precision numeric stored as decimal......38/2+3 apparently. I really don't get it myself but its on one of the oracle newsgroups...

The message I get is as follows:

oe_price_adjustments_oci2..tgt_oe_price_adjustments.IDENT1: At row 1420, link "tgt_oe_price_adjustments", while processing column "OPERAND"
Value "46.66666666666666666666666666666666666667" truncated to "46.66666666666666666666"

Strangely enough the second number there is 22 digits. EXACTNUM is currently set to the default of 15. We will give it a run tomorrow at 38....

Just wondering if anyone else out there has had this kind of problem and managed to get 38 signififcant digits loaded using DS?

Thanks all.

peternolan9 wrote:Hi Ray,
the 22 seems to be inside the Oracle database...DataStage is sending the ful 38 digits to Oracle through the ODBC interface and the message is an oracle message saying num1 has been truncated to num2 where num1 and num2 are the full 38 digits and the truncated 22 digits.....I am almost certain this is an Oracle problem and DS is passing the correct information. I'll let you know if I find out how to get around it..

ray.wurlod wrote:If the relevant columns in your tables are set to NUMBER(22) in the target that's not a DataStage problem.

If you send your data to a text file, are the numbers truncated to 22 digits? If not, are you doing any transformation that might limit them to 22? Indeed, are you certain that the source has 38 digits in all cases? DataStage will remove leading zeroes from numeric values, but you should still be able to load 42 into a NUMBER(38) column.

dzdiver · Post by **dzdiver** » Thu Nov 25, 2004 5:38 am

"Just wondering if anyone else out there has had this kind of problem and managed to get 38 significant digits loaded using DS? "

I can extract using DS type decimal length 38 numbers like
12345678901234567890123456789012345678
12.345678901234567890123456789012345678

using v7, regular oracle 8 OCI stage version 5.0

and then load them back into the database using same OCI stage set to update or insert.

cheers,
B.

peternolan9 · Post by **peternolan9** » Thu Nov 25, 2004 5:49 am

Hi B/All,
we have now got it working for OCI but we cannot get it working for ODBC and we have written all our ETL using ODBC....we've got the problem in with support, but if anyone has got this working on ODBC I'd be very interested. We have 22 significant digits working working with wire driver and 15 with standard ODBC on OCI. We are on AIX 5.1/Oracle 9.2...

dzdiver wrote:"Just wondering if anyone else out there has had this kind of problem and managed to get 38 significant digits loaded using DS? "

I can extract using DS type decimal length 38 numbers like
12345678901234567890123456789012345678
12.345678901234567890123456789012345678

using v7, regular oracle 8 OCI stage version 5.0

and then load them back into the database using same OCI stage set to update or insert.

cheers,
B.

dzdiver · Post by **dzdiver** » Thu Nov 25, 2004 9:32 am

Likely lame advice but which implementation of ODBC drivers are you using on AIX? Is there some of their documentation you could read?

Im sure you thought of this already also, but if you cant use OCI and need a temporary fix to get *something* working, you could use to_char() and convert back and forth...

good luck,
B.

peternolan9 · Post by **peternolan9** » Mon Dec 13, 2004 3:08 pm

Hi dzdiver,
we are using the data direct drivers and they do in fact document the fact that they truncate significant digits to 15 digits for a float.....The problem is with ASCL and they have agreed they have a problem and have even replicated the problem. We will probably just have to convert the affected jobs to OCI unless we can get a fix sooner rather than later..

Interestingly, if we just do view data in the ODBC icon we get 22 digits but if we run it through a transformer it gets truncated to 15 significant digits...

dzdiver wrote:Likely lame advice but which implementation of ODBC drivers are you using on AIX? Is there some of their documentation you could read?

Im sure you thought of this already also, but if you cant use OCI and need a temporary fix to get *something* working, you could use to_char() and convert back and forth...

good luck,
B.