Trouble using sqlldr.exe with NCLOB values when unicode characters not in ASCII code range are used.

Question

When we use sqlldr to populate an NCLOB column with a text value from a lob file and the character is not in the regular ASCII code range sqlldr bombs.

---

Seemingly relevant sections from log file:

EXTENSIONDATA                     DERIVED *****           VARCHARC             
    Maximum field length is -2147483639
    Static LOBFILE.  Filename is C:\Temp\fb6b023e-7bac-4c93-814a-c7adecc11ad5.lob
    Character Set UTF8 specified for all input.

SQL*Loader-462: error inserting LOB into column EXTENSIONDATA, row 106, table ENTITYEXTENSIONDATA
secondary data file for LOB is C:\Temp\fb6b023e-7bac-4c93-814a-c7adecc11ad5.lob
file offset for beginning of lob is 18393
SQL*Loader-645: error converting character length field to a number

---

Control File has

LOAD DATA
CHARACTERSET UTF8
INFILE "C:\Temp\eb5e656c-94d1-4a0e-99be-3df8fa0d4461.bcp"
BADFILE "C:\Temp\eb5e656c-94d1-4a0e-99be-3df8fa0d4461.bad"
APPEND
INTO TABLE EntityExtensionData REENABLE
FIELDS TERMINATED BY '||' 
TRAILING NULLCOLS
(EntityExtensionDataId,EntityId,ExtensionData LOBFILE(CONSTANT 'C:\Temp\eb5e656c-94d1-4a0e-99be-3df8fa0d4461.lob') VARCHARC(10,2147483647))

Answer 1

When I was determining the length of the value in the lob value to place it in the lob file record I was using string.Length. Obviously when it was a multibyte character this would be the wrong size in bytes. The fix was just to change it to use Encoding.UTF8.GetByteCount() to get the correct size.