DSXchange

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,

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.

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,

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.

vinaymanchinila wrote: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.

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

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.

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.

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.

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,

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

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

Thanks for all your responses.

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.