DSXchange

Hi,
We are trying to load flat file on unix server to oracle table through oracle enterprise stage. I have a input column customer_name which contains some trailing spaces which we need to preserve while loading. e.g "sachin " should be loaded as it is and not as "sachin". For this purpose we have set environment variable APT_ORACLE_PRESERVE_BLANKS to true. But after doing this data loaded in traget table is like "sachin " as my customer_name field is of varchar(20). It is adding extra spaces treating like char field.
Oracle version used is 10g
Any pointers to this issue. Saw one post to similar to ine but there were no answers provided

You asked it to preserve blanks and it did so. What's the problem?

Hi Ray,
but it shud preserve only existing spaces right. And should not pad string with additional spaces based on length of the string.

small correction

it is adding extra spaces in loaded string. If my column has length as varchar(20) then loaded string would be "sachin " + 13 spaces

In earlier post this was not reflecting properly hence confusion

Are you sure it's a VARCHAR not a CHAR in the database? If it were CHAR then the database itself might pad with space characters.

Are you perhaps reading it from your source as a CHAR? You are correct that it should only preserve any existing trailing spaces and not add anything to the data when it is sent to Oracle.

Pretty sure that database has datatype as varchar2(20) and also while reading from seq file I am reading as varchar. Only thing is not specified any length while reading from file

Are you bulk loading or inserting?

Push your data to a sequential file instead of your oracle stage so you can see if any modification has happened prior to oracle

Yes. Already done this. Have added peek parallel to oracle stage and data is coming properly till peek stage. Only existing spaces in string are getting preserved. But in ORACLE it is padded with additional spaces based on length of the string

I think Kryt0n might have the correct idea - what method are you using to load into Oracle - if using BULK it might be using a fixed-width file which in turn might be causing the inserting of extra spaces.

I am using write method as Load. I tried reading generated ctl file for the same and following are contents of the same. It is using some "FIX 20".

OPTIONS(DIRECT=TRUE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=YES)

LOAD DATA INFILE '/data03/Scratch/Scratch12/ora.1183988.635332.fifo.0.out' "FIX 20"
APPEND PRESERVE BLANKS INTO TABLE Name_test
(
NAME POSITION(1:20) NULLIF (1:20) = BLANKS

)
so I think Kryt0n has given hint in correct direction. but how to override this while using load method

I think that using this method you don't have a choice, the file is created using fixed widths (which can be parallelized easily, unlike variable width files) so preserving the original number of trailing spaces won't be possible. Is using the normal insert method an option?

Recently I have had the same issue ie after adding APT_ORACLE_PRESERVE_BLANKS all the character columns have been padded with space. I have found a workaround for this, just updating the old thread which may help others.

Workaround

Removed length values in the metadata for all the columns in the load oracle stage .The out put has come as expected, even source column value with space also loaded correctly.

-Paul J