Oracle Stage Inserted once,Rejected once.

[ssreeni3]

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

[chulett]

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.

[bicap]

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

[ray.wurlod]

We can probably do you a good deal on DB2

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

[chulett]

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.

[ssreeni3]

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

[chulett]

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