Insert Null values into Oracle date column

[vinaymanchinila]

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,

[DSguru2B]

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.

[vinaymanchinila]

The error I get int he first case is SQL CODE 1403, I am investigating into it, I an loading a blank oralce table with Insert then Update.

In the second case when you mean default value what would it be, 9999-99-99 or can we insert null vlaue ' ' ?

thanks,

[DSguru2B]

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.

[chulett]

In the second case when you mean default value what would it be, 9999-99-99 or can we insert null vlaue ' ' ?

It would have to be a valid date with special meaning to your application. For example, I've seen things like:

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.

[kumar_s]

Dont you get any ORA error code. As noted you cannot send Null to not nullable field.

[vinaymanchinila]

For testing purpose, Iam inserting CurrentDate() and all the rows are bieng rejected with 1403 SQL code, I am using Insert then Update, also did try Update then insert. Its wierd, that all the rows re being rejected without being inserted or updated.

[chulett]

What is CurrentDate() and what does it's output look like? I'll wager it is not the proper format, gets 'converted' to null by Oracle and then fails the constraint.

[ray.wurlod]

Are you using CurrentDate() in an expression in the Transformer, or in the SQL itself? The latter does not work, because there's no such Oracle function.

[vinaymanchinila]

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,

[vinaymanchinila]

FYI , if it helps, I have also set the APT_ORACLE_PRESERVE_BLANKS to true for this job.

[vinaymanchinila]

There was a trigger constraint that had to be dropped, so the sql code error was not help full!

Thanks for all your responses.

[kumar_s]

It might be a user defined function used in your site.
You need to convert it back to oracle format while loading it. You need to use TO_DATE() function.