DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic is not resolved, but there is a WORKAROUND.
Author Message
atulgoel
Participant



Joined: 03 Feb 2009
Posts: 76
Location: Bangalore, India
Points: 842

Post Posted: Tue Nov 28, 2017 2:27 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
I am reading the data from SQL Server and dumping into Oracle. I am using RCP while reading and writing. For reading and Writing both I am using ODBC connector stage.

Problem is, there are some NOT NULLABLE columns having EMPTY values in source and the job is getting aborted while loading these EMPTY values. Its gives below error:

ORA-01400: cannot insert NULL into <Not Nullable column name>

The metadata is same in Source and Target (Not null-able)

FYI: I am creating the table in Oracle using ODBC stage (Table Action = Create, Write Mode = Insert)

_________________
Atul
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42386
Location: Denver, CO
Points: 217652

Post Posted: Tue Nov 28, 2017 6:35 am Reply with quote    Back to top    

What empty value, specifically? Oracle loves to trim spaces from fields, which can turn an "empty value" like a space into a null during a load. There's an option to "preserve spaces" but I don't know off the top of my head if ODBC supports that...

_________________
-craig

Pinky's as perfect as the Fourth of July, quilted and timeless, seldom denied
Rate this response:  
Not yet rated
atulgoel
Participant



Joined: 03 Feb 2009
Posts: 76
Location: Bangalore, India
Points: 842

Post Posted: Tue Nov 28, 2017 7:53 am Reply with quote    Back to top    

Its a empty value in a column. I found that Oracle treats NULL and EMPTY values as same (NULL)

below link explains that:

https://stackoverflow.com/questions/13278773/null-vs-empty-string-in-oracle

So there is no option to Load EMPTY value in Oracle from Datatsage in NOT NULLABLE column.

_________________
Atul
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: 42386
Location: Denver, CO
Points: 217652

Post Posted: Tue Nov 28, 2017 8:16 am Reply with quote    Back to top    

Ah... so you specifically mean an empty string. Yes, that's a well-known fact and yet another Oracle quirk. And saying there is no option "from DataStage" isn't really correct, this is all about Oracle and your desire to use RCP. Why not define the target column as NULLABLE since you want to leave it "empty" and you know Oracle doesn't allow that? Seems to me the simplest and most straight-forward solution.

_________________
-craig

Pinky's as perfect as the Fourth of July, quilted and timeless, seldom denied
Rate this response:  
Not yet rated
atulgoel
Participant



Joined: 03 Feb 2009
Posts: 76
Location: Bangalore, India
Points: 842

Post Posted: Thu Nov 30, 2017 6:30 am Reply with quote    Back to top    

Yes I agree your point Craig. I have made the columns as NULLABLE on Oracle and loading the data. Thanks.

_________________
Atul
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