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:
In the above text file
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"
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