Oracle Upsert Rejects

[bakul]

Hi,

We have a job of the following type

Code: Select all

OEE => Lookup => Transformer => Upsert (update only) => Reject
        ||
        V
  Rejects Inserted

The final upsert stage was rejecting records during update. For some the SQLCODE was -1480. For some, it was blank
We tried using APT_STRING_PADCHAR values of 0x0, 0x00 (no improvement), 0x20 (all rejected)
There were no metadata or nullability mismatches between source and target and the data had been stored succesfully in source.
The varchar fields were unbounded.

After analysis, it was observed that the rejects were for 2 fields both of type varchar. The fields had long string values and contained intermittent spaces and some characters like # etc.

Just for trial, we added the length to the metadata of these 2 fields i.e. defined them as bounded varchars.

On running the job, this solved the problem.

We have other jobs (LOAD option of OEE) using unbounded varchars and this issues was not observed there.

What could have been the issue?
Why does Oracle reject unbounded varchars?
Is it to do with the intermittent spaces or with characters like #?