DSXchange

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?

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

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

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.

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?

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

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.

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.

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.

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.

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.