Reading EMPTY values in NOT NUllable column

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
atulgoel
Participant
Posts: 84
Joined: Tue Feb 03, 2009 1:09 am
Location: Bangalore, India

Reading EMPTY values in NOT NUllable column

Post by atulgoel »

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

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
atulgoel
Participant
Posts: 84
Joined: Tue Feb 03, 2009 1:09 am
Location: Bangalore, India

Post by atulgoel »

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/132 ... -in-oracle

So there is no option to Load EMPTY value in Oracle from Datatsage in NOT NULLABLE column.
Atul
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
atulgoel
Participant
Posts: 84
Joined: Tue Feb 03, 2009 1:09 am
Location: Bangalore, India

Post by atulgoel »

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