Loading UTF8 fixed length file using SQL Loader 8i

Hi all,

I'm trying to load an UTF8 file with fixed length record using SQL Loader for Oracle 8i.

Unfortunately, SQL Loader does not make the difference between bytes and characters. i.e. that if two bytes are used to code an accentuated character (like é) SQL loader will consider that two caracters has been read meaning that beginning of next field will be shifted.

Example:

LOAD DATA
CHARACTERSET UTF8
INFILE 'XXX.txt'
REPLACE INTO TABLE tmp.MY_TABLE
(

    LASTN    POSITION (1:20),  -- Last name
    FIRSN    POSITION (21:40), -- First name
    STATU    POSITION (41:41), -- Status
    BIRDT    POSITION (42:49)  -- Date of birth

With this data :
Thévenat Jacques 119460508

I'll get in my DB :

Field 1 : Thévenat
Field 2 :  Jacques   (note the leading space)
Field 3 :    (blank)
Field 4 : 11946050 (wrong date)

This because the "é" is coded like C3 A9 (= 2 bytes) in UTF8.

I know that there is a LENGTH SEMANTICS CHAR option in Oracle 9i but I haven't found anything similar in 8i.

Does anybody know a work around ?

Thanks in advance. Received on Tue Jan 04 2005 - 07:18:23 CST