Oracle Load Issuse, Data loaded with trailing spaces

gsym · Post by **gsym** » Tue Mar 10, 2009 9:50 am

Hey,

I am using LOAD option in oracle enterprise stage. When i load data into the VARCHAR Field, the data is getting loaded with spaces.

Example

Assume the job as follows

Oracle -----> Transformer------->Oracle

In transformer i am defaulting a value 'SM' for a column of datatype Varchar (5). when i load the data, its getting loaded as 'SM ' (with trailing spaces). I tried trimming the data via datastage but it dosent work.

is there any env variable to solve this issuse or any workaround? or is it a default characteristic of LOAD?

girija · Post by **girija** » Tue Mar 10, 2009 9:52 am

What is the data type defined in oracle for this field?

gsym · Post by **gsym** » Tue Mar 10, 2009 10:28 am

Its Varchar2(5)

ArndW · Post by **ArndW** » Tue Mar 10, 2009 10:33 am

You probably have a VarChar2 data type, which does get loaded with trailing spaces. Look up the use of the environment variable APT_ORACLE_PRESERVE_BLANKS and set it so that the spaces are preserved

gsym · Post by **gsym** » Tue Mar 10, 2009 11:05 am

The reason I set tht to true was, when we use SQL LOAD option via datastage, it treats " " as Null and it dosent get loaded into the non nullable column. But when I set this ENV varibale, it works fine.

Also When I use Insert, it dosent get loaded with trailing spaces

DSXchange

Oracle Load Issuse, Data loaded with trailing spaces

Oracle Load Issuse, Data loaded with trailing spaces

Varchar2(5)

I already tried that and still it dosent work.