Oracle Connector conversion problem

ponzio · Post by **ponzio** » Wed Sep 03, 2014 7:25 am

Hi.
I'm having a problem using Oracle Connector.

This is my job: Seq.file ---> Trasformer ----> Oracle Connector

The input field UNIT_PRICE is defined in the following way in the input file:

SQL type = Double
Length = 38
Display = 40 (38 and 40 are as imported)

In the trasformer output link there is the same definition for the corresponding output field.

In the seq. file is present the value UNIT_PRICE = 0.333329.

Using Oracle Connector the table this value is loaded as 0.333328999999999 instead of 0.333329, why ?

Using the OCI Stage the value is loaded fine, as 0.333329.

The first thing I notice is that in Oracle Connector Stage tab 'Column' the 'Display' field property is not present.
I suppose that an implicit conversion changes the value.

Could someone help me, please?

Many thanks,
Andrea

chulett · Post by **chulett** » Wed Sep 03, 2014 7:28 am

What's the Oracle datatype?

ponzio · Post by **ponzio** » Wed Sep 03, 2014 7:45 am

NUMBER

chulett · Post by **chulett** » Wed Sep 03, 2014 7:57 am

An unbound NUMBER is stored as a FLOAT so not really surprised you see what you see, in spite of that 'working' with OCI. Define it as a string in your file and do an explicit conversion, see if that changes the behavior.

ponzio · Post by **ponzio** » Wed Sep 03, 2014 8:27 am

Sorry how can I do the explicit conversion?

I tried and doesn't work.

I tried to read the number in the input as float and write as float
that number becomes 0,333328992 and 0,0207 (previously correct) becomes0,0207000002

Thanks,
Andrea

chulett · Post by **chulett** » Wed Sep 03, 2014 1:28 pm

Sorry, too much PX on the brain. If you work with FLOAT you'll see all kinds of issues like this so... don't. Probably won't help but do the move from a string field to a decimal with the proper scale in the transformer and then map that decimal to the NUMBER in Oracle. If that still doesn't work but is not an issue with OCI then I'd think you'd need to contact support about the Oracle Connector issue and see what they suggest.

ponzio · Post by **ponzio** » Thu Sep 04, 2014 2:55 am

chulett wrote:Sorry, too much PX on the brain.

I was supposed last last post then same

chulett wrote: If you work with FLOAT you'll see all kinds of issues like this so... don't. Probably won't help but do the move from a string field to a decimal with the proper scale in the transformer and then map that decimal to the NUMBER in Oracle.

Read as Varchar and write as decimal works fine, but also read as double works fine.

So I suppose the important thing is write as decimal

Many thanks.
Andrea

ponzio · Post by **ponzio** » Thu Sep 04, 2014 3:27 am

ponzio wrote:but also read as double works fine.
So I suppose the important thing is write as decimal

Sorry, reading the field as double doesn't work

A