Default: The default for conventional and direct path loads is LDRTRIM
. The default for external tables loads is LRTRIM
.
Purpose
The TRIM
parameter specifies the type of trimming to use during the load.
Syntax and Description
TRIM=[LRTRIM | NOTRIM | LTRIM | RTRIM |LDRTRIM]
The TRIM
parameter is used to specify that spaces should be trimmed from the beginning of a text field, or the end of a text field, or both. Spaces include blanks and other nonprinting characters such as tabs, line feeds, and carriage returns.
NOTRIM
indicates that no characters will be trimmed from the field. This setting generally yields the fastest performance.
LRTRIM
, LTRIM
, and RTRIM
are used to indicate that characters should be trimmed from the field. LRTRIM
means that both leading and trailing spaces are trimmed. LTRIM
means that leading spaces will be trimmed. RTRIM
means trailing spaces are trimmed.
LDRTRIM
is the same as NOTRIM
except in the following case:
If the field is a delimited field with OPTIONALLY
_ENCLOSED
_BY
specified, and the optional enclosures are missing for a particular instance, then spaces will be trimmed from the left.
If trimming is specified for a field that is all spaces, then the field will be set to NULL
.
Restrictions
Only LDRTRIM
is supported for forced conventional path and forced direct path loads. Any time you specify the TRIM
parameter, for any value, you receive a message reminding you of this.
If the load is a default external tables load and an error occurs that causes SQL*Loader express mode to use direct path load instead, then LDRTRM
is used as the trimming method, even if you specified a different method or had accepted the external tables default of LRTRIM
. A message is displayed alerting you to this change.
If you want to use NOTRIM
, then you can use a control file with the PRESERVE
BLANKS
clause.
Example
The following example reads the fields, trimming all spaces on the right (trailing spaces).
> sqlldr hr TABLE=employees TRIM=RTRIM