data truncation issue with the Number field in oracle

satheesh_color · Post by **satheesh_color** » Wed Mar 18, 2015 9:12 am

Hi All,

I am running the below query
SELECT
to_char(STUDY_DURATION) - Number as defined source table
FROM TableName

Result: 893.640636574074074074074074074074074074

TargetTable: STUDY_DURATION defined as number

When i try to load the above data by using decimal in DS9.1 PX job the job is aborted.
Error:The OCI function OCIStmtFetch2 returned status -1. Error code: 1,457, Error message: Error while trying to retrieve text for error ORA-01457. (CC_OraStatement::fetch, file CC_OraStatement.cpp, line 1,599)

Kindly let me know your thoughts on the same to overcome this issue.

Thanks,
Satheesh.R

chulett · Post by **chulett** » Wed Mar 18, 2015 10:04 am

Hmmm... if your source is a number and your target is a number, why are you doing a TO_CHAR() in the source sql?

qt_ky · Post by **qt_ky** » Wed Mar 18, 2015 11:01 am

I'm curious as to the "truncation issue" in the subject line. Is the data loaded but truncated as well?

chulett · Post by **chulett** » Wed Mar 18, 2015 11:58 am

I was assuming it was the error where Oracle will 'truncate' a unbound number [meaning NUMBER rather than NUMBER(20) let's say] during a fetch operation but that's another ORA error. The posted one is:

ORA-01457: converting column overflows decimal datatype

So also curious what the 'truncation' in the subject is referring to. I'd also be curious as to the exact datatype (precision/scale) of the source and target fields.

satheesh_color · Post by **satheesh_color** » Thu Mar 19, 2015 12:36 am

Hi All,

Apologize for the confusion.
My source Oracle 9i table column definition defined as Number and Target Oracle 12C is also same.

When i try to extract data from DS9.1 and load into target i could see the error message.

My ultimate aim is to load the whole data into my target.Kindly guide me.

Thanks,
Satheesh

qt_ky · Post by **qt_ky** » Thu Mar 19, 2015 7:59 am

In your job on all the columns tabs, try setting the SQL Type to Double, leaving the Length and Scale empty. Also in your SELECT, remove the TO_CHAR function as Craig hinted.

chulett · Post by **chulett** » Thu Mar 19, 2015 8:15 am

satheesh_color wrote:My source Oracle 9i table column definition defined as Number and Target Oracle 12C is also same.

Not so much confusion as a lack of pertinent information.

And just to verify again, your targets are both unbound NUMBER fields, correct? No precision, no scale. Those are (basically) stored as a FLOAT in Oracle which would explain why your example data looks the way it does. And why you may have some fun getting the results to match.

satheesh_color · Post by **satheesh_color** » Thu Mar 19, 2015 8:41 am

Hi Chullet,

Yes..You are right. My source and destination tables dont have any precesion & scale. is there any workaround for this chullet..

Regards,
Satheesh

chulett · Post by **chulett** » Thu Mar 19, 2015 9:35 am

Workaround? No. You just need to learn how to deal with it. Did you try what qt_ky posted?

satheesh_color · Post by **satheesh_color** » Fri Mar 20, 2015 5:34 am

Thanks Craig and qt_ky. It worked:)

Appreciate your help!!!

Thanks,
Satheesh