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 OCTOBER 02, 2019

Applies to:

Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.


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.