data truncation issue with the Number field in oracle
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
data truncation issue with the Number field in oracle
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
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
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 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
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
Not so much confusion as a lack of pertinent information.satheesh_color wrote:My source Oracle 9i table column definition defined as Number and Target Oracle 12C is also same.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am