Insert Null values into Oracle date column
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
Insert Null values into Oracle date column
Hi,
One of the jobs reads from SQL server and writes to Oracle table, the date column in the target oracle is Nullable and in the transformer I set it to SetNull() , it rejects all the rows ( there are no rows to view in the reject file though!) .
So how do we insert null vlaues into a oralce date column which is nullabe and also
What would be the solution if the oracle date column is not nullabe and the incomming source has null value?
Thanks,
One of the jobs reads from SQL server and writes to Oracle table, the date column in the target oracle is Nullable and in the transformer I set it to SetNull() , it rejects all the rows ( there are no rows to view in the reject file though!) .
So how do we insert null vlaues into a oralce date column which is nullabe and also
What would be the solution if the oracle date column is not nullabe and the incomming source has null value?
Thanks,
What is the error/warning message you get in the log? Copy paste it here.
For your second question, you will go into the exentended properties and specify an null_value for the nullable column. Or you can do an explicit null check inside the transformer and set a default value.
For your second question, you will go into the exentended properties and specify an null_value for the nullable column. Or you can do an explicit null check inside the transformer and set a default value.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
You can insert a null value. Do an explicit check with IsNull() and then SetNull() if its a nullable column. If its not then your requirements will state what to specify if null is encountered, specify that as null_value in the extended properties. It can also be specified using NullToValue() in transformer.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
It would have to be a valid date with special meaning to your application. For example, I've seen things like:vinaymanchinila wrote:In the second case when you mean default value what would it be, 9999-99-99 or can we insert null vlaue ' ' ?
01-01-1900
12-31-9999
As magic min/max values used. You can't use something like a space. Also note that a typical table designer will include a default value for the column like that when it is required. In order to leverage the default value in the column, you have to not send the column - sending null will generate an error.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
Hi,
The CurrentDate() is in column in the transformer and the format as seen in the peek (reject) is 2007-03-08, the date column table has been defined as date but when I import the metadata using Orchestrate or ODBC it comes in as timestamp, I did try to use CurrentTimestamp() in the transformer but its the same issue, it rejects all the records with 1403.
Thanks,
The CurrentDate() is in column in the transformer and the format as seen in the peek (reject) is 2007-03-08, the date column table has been defined as date but when I import the metadata using Orchestrate or ODBC it comes in as timestamp, I did try to use CurrentTimestamp() in the transformer but its the same issue, it rejects all the records with 1403.
Thanks,
Thanks,
Vinay
Vinay
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am