SQL Loader multibyte characters

[EL04]

Hi
I need to load multibyte characters from a position based datafile into the database using SQL Loader. The database & the control file are set to UTF-8 character set.
The characters are loaded fine using insert statements. But while loading from SQL Loader, the characters are loaded properly, but the position of the characters in the subsequent columns are pushed depending on the number of multibyte characters.

Control File:
LOAD DATA
CHARACTERSET UTF8
INFILE * INTO TABLE TEST
TRAILING NULLCOLS
(
ELEMENT1 POSITION(1:24),
ELEMENT2 POSITION(25:64),
ELEMENT3 POSITION(65:68),
ELEMENT4 POSITION(69:72),
ELEMENT5 POSITION(73:80),
ELEMENT6 POSITION(81:120)

)

DataFile:
SAMPLE1 Saméplee à Séampleee L7 ABC 60 00004454ABCD

The data that got loaded in the columns:
ELEMENT1: SAMPLE1
ELEMENT2: Saméplee à Séampleee L7
ELEMENT3: A
ELEMENT4: BC 6
ELEMENT5: 0 0004
ELEMENT6: 454ABCD

The data in the columns ELEMENT3 through ELEMENT6 are incorrect.
There are 3 multibyte characeters in the ELEMENT2 column. So the position of the data in the remaining columns have been misplaced.
The data in the column ELEMENT3 has been moved to the ELEMENT4 and so on. Any help would be appreciated!

Thanks...

[Tim...]

Hi.

Sounds like the positions are in bytes and you need to adjust the controlfile to take this into account, unless of course I am missing the point of your question.

Cheers

Tim...

[kaderno]

Same problem here, I think the position is specified in bytes so, unless you know exactly the number of special characters you can not use position based control files.

I found this around:

The control file specifies the specific starting and ending byte location of each field. This format is harder to create and less flexible but can yield performance benefits.

Any idea on how to deal with this?

[Tim...]

Hi.

I've not had to do this. I think I would log an SR with Oracle support to see how they expect it to be done.

Cheers

Tim...