APT_ORACLE_PRESERVE_BLANKS
Moderators: chulett, rschirm, roy
APT_ORACLE_PRESERVE_BLANKS
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
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
Sachin C
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
Sachin C
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Re: APT_ORACLE_PRESERVE_BLANKS
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
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