DSXchange

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

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

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

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.

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

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.

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.

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

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

Thanks Craig and qt_ky. It worked:)

Appreciate your help!!!







Thanks,
Satheesh