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

Last updated on JULY 03, 2015

Applies to:

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

Symptoms

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 define 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 by using below text file:

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 the above data is uploaded using sql*loader, data in the table will be shown as

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

  

Cause

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