DSXchange

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

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?

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

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.

Any one have any other ideas???

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

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.

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.

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.

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.

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.