NULL Handling in Runtime Column Propagation

sarathchandrakt · Post by **sarathchandrakt** » Mon Sep 29, 2014 11:49 pm

I am doing a simple DB2 to Oracle ETL. Table name is passed runtime so there is no point in adding columns. So I used Runtime Column Propagation. But the blanks in DB2 when passed to oracle are treated as NULL and the job fails. Is there a way to do NULL Handiling in ORACLE Connector or DB2 Connector during RUNtime column Propagation

ray.wurlod · Post by **ray.wurlod** » Tue Sep 30, 2014 12:32 am

Blank (" ") or empty string ("") ?

There is a difference.

I believe there is an environment variable that controls this behaviour into Oracle, but can't bring it to mind at the moment.

Perhaps you could research it?

sarathchandrakt · Post by **sarathchandrakt** » Tue Sep 30, 2014 6:07 am

Its Empty (""). Please try to remember. I searched but I didn't find it.

chulett · Post by **chulett** » Tue Sep 30, 2014 7:01 am

APT_ORACLE_PRESERVE_BLANKS is probably what you're thinking of. However, "blanks" here equal spaces as in trailing spaces during a bulk load so I'm not sure it's going to help. An empty string will be turned into a NULL in Oracle regardless.

sarathchandrakt · Post by **sarathchandrakt** » Tue Oct 07, 2014 12:49 am

Any one have any other ideas???

qt_ky · Post by **qt_ky** » Tue Oct 07, 2014 6:21 am

It sounds like Oracle is causing the problem. Try calling Oracle Support.

chulett · Post by **chulett** » Tue Oct 07, 2014 7:07 am

Specify for us what kind of "null handling" you want to do and then perhaps people can let you know if there is a way to handle that under RCP.

kwwilliams · Post by **kwwilliams** » Tue Oct 07, 2014 7:17 am

Oracle treats empty strings as null.

http://docs.oracle.com/cd/B19306_01/ser ... nts005.htm

http://docs.oracle.com/cd/B28359_01/ser ... nts005.htm

You can work around this by providing a default value in the Oracle DDL. Understand that other loads where the column is nullable and the source value is an empty string, the target contains a null value.

chulett · Post by **chulett** » Tue Oct 07, 2014 9:14 am

Yup, already noted that. However, a default value will only help if you leave the column out of the DML. If you send a NULL, that's what it will attempt to insert.

vmcburney · Post by **vmcburney** » Tue Oct 07, 2014 6:19 pm

I think the question isn't how you get Oracle to accept an empty field into a NOT NULL field but why the target column is NOT NULL in the first place. With additional development effort you can hack the data to make it fit Oracle's rules - you can convert empty spaces to a blank space for VARCHAR fields or a 0 for integer or 1901 for dates but you have to ask why the column is NOT NULL in the first place when clearly there are null values that are supposed to go in it.

vmcburney · Post by **vmcburney** » Tue Oct 07, 2014 6:21 pm

Has anyone tested this out using the version 11 Data Click? This seems to be an ideal Data Click scenario - where in just a couple clicks you move a DB2 table into Oracle. They must have found a way to resolve this null field problem.