Error: ORA-01438: value larger than specified precision
Moderators: chulett, rschirm, roy
Error: ORA-01438: value larger than specified precision
I have decimal values going into a number(4,2) column in Oracle. The job has been running fine for years. In the last run, it suddenly started giving ora-01438. Is it possible that some setting got changed?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Any decimal number larger than 99.99 will cause this overflow condition.
If, for example, it's a percentage, it may be that 100.00 has come through as a value.
DataStage server jobs don't care (as they treat everything as string internally), but Oracle will reject a row containing such a value.
Add a reject link to a Transformer stage upstream of the Oracle stage. This reject link can be used to capture any rows rejected by Oracle.
If, for example, it's a percentage, it may be that 100.00 has come through as a value.
DataStage server jobs don't care (as they treat everything as string internally), but Oracle will reject a row containing such a value.
Add a reject link to a Transformer stage upstream of the Oracle stage. This reject link can be used to capture any rows rejected by Oracle.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Yes, it was a data issue but I have a question. The error is being caused by the target ORAOCI9 stage. Why does the message say "Inserted value too large for column, row rejected"? The error is not caused by Oracle so no row has been inserted yet. If an attempt has been made to insert into Oracle and it failed, shouldn't we see an ORA error?
Re: Decimal not being inserted
splayer wrote:In the last run, it suddenly started giving ora-01438.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Okay, that would have been good to mention right off the bat. So what was there in the Director log? You can see both what I used to call 'hard' and 'soft' rejects from Oracle - hard/physical where the database barfs it back up after you send it down the link and soft/logical when DataStage knows something is bad and doesn't even bother to send it. Both will be caught by the reject mechanism that Ray mentioned.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Both of these statements cannot be true.
splayer wrote:The ORA error is what I saw when I took the SQL to Oracle. It was not there in the Director log. Sorry about the confusion.
splayer wrote:The job has been running fine for years. In the last run, it suddenly started giving ora-01438.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Was thinking the same thing, Ray.
Other than what 'one error message'? The one from the SQL trace? So you are saying that your Director log for the job shows no error messages and completes without warnings or errors but is just throwing the data away? Color me... confused.splayer wrote:Other than that one error message, there is no other message related to this.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers