How To Trim The Trailing White Space While Loading Data Using SQL*Loader (Doc ID 331366.1)

Last updated on NOVEMBER 24, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.0 and later   [Release: 8.1.7 and later ]
Information in this document applies to any platform.

Goal

When loading data into a table using a control file like the following, how to trim the trailing white space that can go into the character columns from the data file:

LOAD DATA
INTO table test_import
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
   t_name,
   min_value,
   max_value,
   diff TERMINATED BY whitespace
)

Note: In this code T_NAME column is of length 24 in the table, whereas the data which is flowing from the data file is only 15 characters long and rest is trailing white space.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms