SQL Loader : Multibyte character error

Question

I am trying to load a file with few records containing multibyte(special) character using SQL Loader into Oracle table via a control file. But it is giving me Multibyte character error if I use the below control file.

LOAD DATA
CHARACTERSET UTF8
INTO TABLE MY_DB.T1
(
MESSAGE BOUNDFILLER POSITION(437:515) CHAR,
FILLER ":MESSAGE",
MY_DTE DATE "DDMMYYYYHH24MISS" "CASE WHEN 1=1 THEN NULL ELSE NULL END",
XTRA_TXT POSITION(516:755) CHAR
)

The special character is at the position 530.

In other case when I place the FILLER field at the end of control file after MY_DTE field without any other changes the records are loaded successfully.

Also, if I keep the control file as is and add any character before the special character position (530), then also records are successfully loaded.

I am not sure what really is causing the issue. PS- The type of field FILLER in the table is VARCHAR2(79 BYTE).

What is the actual error message you get? Also what is the DB character set, and how many chars and how many bytes are actually in positions 516:755? — Alex Poole
In the logs I can find the error as : RECORD 616 : Rejected - Error on table My_DB.T1, column MY_DTE. — rattlesnake
Also ,the NLS_CHARACTERSET in the table NLS_DATABASE_PARAMETERS is UTF8 and NLS_LANGUAGE is AMERICAN — rattlesnake
@AlexPoole The characters starting from 516 looks like : REBATE JAN 17 â DEC 17 .....rest spaces till 755th character — rattlesnake
Are you working on Windows or Linux? I assume the file is actually saved as UTF-8? — Wernfried Domscheit

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2018-07-09T10:41:47

In order to make it working make following before you use sqlloader:

Set your LOCALE to UTF-8 (in Windows it would be chcp 65001)
Set your NLS_LANG environment variable to UTF-8, e.g. set NLS_LANG=.AL32UTF8
Declare CHARACTERSET UTF8 in your sqlloader file.

Then it should work.

SQL Loader : Multibyte character error

1 Answers