Loading UTF8-encoded fixed-width text files with SQL Loader

Question

I'm trying to import fixed-width text files but I'm running into problems with non-ascii charater data. Below is the smallest example I can create that demonstrates the problem.

load data
infile *
truncate into table test
(
   txt position(1:3)
)
BEGINDATA
A
Ö
ABC
ÅÄÖ

Here is the table:

create table test(
   txt varchar2(30 char) not null
);

All records but the last one is imported just fine. I'm guessing it has to do with byte vs char semantics. Is this fixable at my end? Is it even possible to talk about fixed-width records in a file with multibyte character data?

Here is the relevant portion of the log file:

SQL*Loader: Release 11.2.0.1.0 - Production on Mon May 29 10:30:04 2017

Control File: test.dat
Data File:    test.dat
Bad File:     test.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TEST, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TXT                                   1:3     3           CHARACTER            

Record 4: Rejected - Error on table TEST, column TXT.
Multibyte character error.

Table TEST:
  3 Rows successfully loaded.
  1 Row not loaded due to data errors.

Space allocated for bind array:                    384 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             4
Total logical records rejected:         1
Total logical records discarded:        0

sandman sandman · Accepted Answer · 2017-05-29T10:36:36

SQL-Loading problems can arise between you expecting a certain width and applications expecting certain field sizes.

NLS_LENGTH_SEMANTICS allows you to specify the length of a column datatype in terms of CHARacters rather than in terms of BYTEs. Typically this is when using multi-byte AL32UTF8 or in your case UTF8.

It can be set at session level if not defined at database level.

ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE

Oracle advises to use CHAR explicitly when creating the table.

Create table scott.test (Col1 CHAR(20 CHAR),Col2 VARCHAR2(100 CHAR));

When accessing columns in PL/SQL, define the variables explicitly e.g.

Col2 VARCHAR2 (10 CHAR);

Loading UTF8-encoded fixed-width text files with SQL Loader

1 Answers