DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
splayer


since July 2006

Group memberships:
Premium Members

Joined: 12 Apr 2004
Posts: 502

Points: 4490

Post Posted: Sun Apr 17, 2016 9:54 am Reply with quote    Back to top    

DataStage® Release: 9x
Job Type: Server
OS: Unix
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

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42613
Location: Denver, CO
Points: 219385

Post Posted: Sun Apr 17, 2016 2:20 pm Reply with quote    Back to top    

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

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

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54248
Location: Sydney, Australia
Points: 294235

Post Posted: Sun Apr 17, 2016 4:17 pm Reply with quote    Back to top    

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 ca ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
splayer


since July 2006

Group memberships:
Premium Members

Joined: 12 Apr 2004
Posts: 502

Points: 4490

Post Posted: Mon Apr 18, 2016 5:33 am Reply with quote    Back to top    

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?
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42613
Location: Denver, CO
Points: 219385

Post Posted: Mon Apr 18, 2016 6:11 am Reply with quote    Back to top    

Question

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

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
splayer


since July 2006

Group memberships:
Premium Members

Joined: 12 Apr 2004
Posts: 502

Points: 4490

Post Posted: Mon Apr 18, 2016 7:51 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42613
Location: Denver, CO
Points: 219385

Post Posted: Mon Apr 18, 2016 7:57 am Reply with quote    Back to top    

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 wher ...

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
splayer


since July 2006

Group memberships:
Premium Members

Joined: 12 Apr 2004
Posts: 502

Points: 4490

Post Posted: Mon Apr 18, 2016 2:51 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54248
Location: Sydney, Australia
Points: 294235

Post Posted: Mon Apr 18, 2016 5:18 pm Reply with quote    Back to top    

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. ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42613
Location: Denver, CO
Points: 219385

Post Posted: Mon Apr 18, 2016 6:59 pm Reply with quote    Back to top    

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 ...

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours