My Oracle Support Banner

Loading Multibyte Characters Using Fixed Width SQL*Loader Control File, Table Column Values May Shift Position (Doc ID 2010180.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.


When trying to load data with German Umlaut characters (which are represented by 2 bytes) into an Oracle table using SQL*Loader with a fixed width control file, data will be loaded successfully but 2 bytes characters shift the data by 1 Byte.
The fixed width text fields are in the control file defined with the POSITION parameter.
Due to this, columns can get loaded with incorrect data as 2 bytes character data may be shifted to a next column.

For Example, if there are only 2 columns to be loaded using SQL*Loader and the data file has the entry:

Gärtnerweg 51|GERMANY

In the above text file

Column 1:   Gärtnerweg 51     ---->>  In this word, the second character 'ä' is a German 2-byte character.
Column 2:   GERMANY

After loading this data file using sql*loader, the table data looks like:

Column1        Column2
-------------  --------
Gärtnerweg 51   GERMANY      ---->> Data Shifted in the second column(GERMANY), having one blank space at the beginning, i.e " GERMANY"

Instead of

Column1        Column2
-------------  --------
Gärtnerweg 51  GERMANY





To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.