Unable to insert ' ' for a NULLABLE field in Oracle table

[clmhwyfe]

I have a datastage job that reads data from a Sybase table (CHAR Columns) and loads to a Oracle table (CHAR) Fields.

There are a few rows which had only spaces as the column values and this data needs to be loaded to the oracle tables as spaces.

My job has APT_STRING_PADCHAR=0x20 set and also I tried using NULLtoEmpty / NULLtoValue with ' ' as the value but nothing seems to work.

The job aborts with the error
The statement failed with status 1400: ORA-01400: cannot insert NULL

I tried the If (Trim(column)) = '' then ' ' - Even then the job aborts with the same error. But if I use this in the constraint of a transformer and write records to a file with the above derivation, It identifies these records correctly - Please let me know if am missing something during the conversion

[rkashyap]

Many of the load stages (Oracle Connector/OCI etc) have an option to "Preserve trailing blanks". Check if this option is set to 'No'.

[clmhwyfe]

Thanks Kashyap,

That resolved the issue.

[chulett]

Which (from what I recall) only applies to bulk loads. Are these bulk loads or conventional inserts?

[chulett]

Interesting... still curious as to the answer to my question.

[clmhwyfe]

Hi Craig,

It was a bulk load

[clmhwyfe]

Hi Criag -
Just to update , I just ran the job via conventional insert and it worked fine.

So it works for both bulk and conventional insert.

[chulett]

You'd have to disable that option when you did the conventional inserts for a true test... and fairly certain you'll find conventionals work fine without that setting.

[clmhwyfe]

Hi Craig,

I tried using conventional load with the option set to 'No' and it either aborted (NULL Constraint) or sent these records to the reject file when I rejected the SQL constraint violation records .

But when I changed it to 'Yes' it loaded the data.

[chulett]

OK, good to know.