DSXchange

Hi Experts,

In my job we are capturing changes in the Current file against an Empty file. Here all the records are new records and i am populating these records to Oracle Table.All the records are inserted into Oracle table.

Now,We are using a new current file against the Empty file to captures the changes.Logically,all the records are new records here.But Oracle Stage rejects all the records here with Ora-1400 code.
I handled nullable columns in a Transformer stage to populate to non nullable Oracle Columns.
I used below Column derivation for map a nullable column(ie;Column_A) to a Non_Nullable column:
If IsNotNull(Column_A) Then Trim(Column_A) Else NullToEmpty(Column_A).
But my issue is:
Why all the records are inserted for one current file and all the records are rejected for a new current file?

Please Clarify the above issue.

Thanks in Advance,
Sreeni

While DataStage understands the difference between 'null' and 'empty', Oracle treats them as identical so you're not actually handling them like you think you are.

Hi,

Thanks for the reply.So,How can I populate the records to Oracle Table?

Please help me regarding to solve the above issue.

Thanks in Advance,
Sreeni

We can probably do you a good deal on DB2

Seriously, though, you have to make the data acceptable to Oracle. Whatever that takes.

So, rather than NullToEmpty you'll need to convert null values for required fields to whatever value is appropriate. Spaces? 12/31/9999? Depends on the data type and the rules in your target.

Hi,

Thanks for the reply.I tried with the below derivation.

If Trim(NullToValue(Column_A,'')) <> '' Then Trim(Column_A) Else
NullToValue(Column_A,'')

But all records got rejected by Oracle Stage.I checked metadata also.

I am not getting any idea to solve the above issue.

Thanks in Advance,
Sreeni

You didn't change the end result. NullToEmpty and NullToValue with a value of '' are equivalent.