Error: ORA-01438: value larger than specified precision

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
splayer
Charter Member
Charter Member
Posts: 502
Joined: Mon Apr 12, 2004 5:01 pm

Error: ORA-01438: value larger than specified precision

Post by splayer »

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?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

ORA-01438: value larger than specified precision allowed for this column

Guessing it's your data that changed and now overflows the target.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
splayer
Charter Member
Charter Member
Posts: 502
Joined: Mon Apr 12, 2004 5:01 pm

Post by splayer »

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?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Re: Decimal not being inserted

Post by chulett »

:?:
splayer wrote:In the last run, it suddenly started giving ora-01438.
-craig

"You can never have too many knives" -- Logan Nine Fingers
splayer
Charter Member
Charter Member
Posts: 502
Joined: Mon Apr 12, 2004 5:01 pm

Post by splayer »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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
splayer
Charter Member
Charter Member
Posts: 502
Joined: Mon Apr 12, 2004 5:01 pm

Post by splayer »

Other than that one error message, there is no other message related to this.

I don't see an ORA error in the Director log. I know that my target ORAOCI9 stage is taking the value as a string and trying to insert into a number(4,2) but I am trying to understand why no ORA error is produced.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Was thinking the same thing, Ray.
splayer wrote:Other than that one error message, there is no other message related to this.
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. :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply