NULL Handling in Runtime Column Propagation
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 50
- Joined: Fri Aug 29, 2014 1:32 pm
- Location: Mumbai
NULL Handling in Runtime Column Propagation
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 50
- Joined: Fri Aug 29, 2014 1:32 pm
- Location: Mumbai
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 50
- Joined: Fri Aug 29, 2014 1:32 pm
- Location: Mumbai
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
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.
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.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn