Trailing spaces when using Oracle Enterprise stage

[ag_ram]

Hi,

I am using an Oracle Enterprise stage in Upsert-Insert mode to insert records into an oracle table. The fields in the input record to the oracle stage can have leading or trailing spaces in them.

When I used the Load-Append option, the operator variable APT_ORACLE_PRESERVE_BLANKS (false) is able to trim the spaces before loading the data into the table.

However when i use explicit insert statement (i.e. User defined Upsert mode in the stage), the stage is not able to trim the spaces.

One of the workaround is to use the TRIM function in the INSERT DDL or use a transformer stage to do this.

However i feel this is not efficient and wondering if there is any setting within datastage that can do this for me.

Please let me know your thoughts on this.

Thanks.

[ArndW]

If the VarChar field has trailing spaces in it they will be written to the database, so you need to TRIM() those in your DataStage job or via the SQL (less efficient).