SQL loader with utf8

Question

I get the following error while loading Japanese data using SQL * Loader. My database is UTF8 (NLS options), and my OS supports UTF8.

Record 5: Rejected - Error on table ACTIVITY_FACT, column METADATA. ORA-12899: value too large for column METADATA (actual: 2624, maximum: 3500) 

My management file:

 load data characterset UTF8 infile '../tab_files/activity_fact.csv' "STR ';'" APPEND into tableactivity_fact fields terminated by ',' optionally enclosed by '~' TRAILING NULLCOLS (metadata CHAR(3500)) 

My table

 create table actvuty_facr{ metadata varchar2(3500 char) } 

Why SQL Loader throws the wrong exception, (actual: 2624, maximum: 3500) . 2624 less than 3500.

Answer 1

The default length semantics for all data files (except UFT-16) are bytes. Thus, in your case, you have a CHAR of 3500 bytes, not characters. You have several multibyte characters in your file, so 2624 characters use more than 3500 bytes, so a message (misleading).

You can sort this using character length semantics instead

change this line in the control file

 characterset UTF8 

to that

 characterset UTF8 length semantics char 

and it will work with characters for CHAR fields (and some others) - in the same way as you set up your table, so 3500 characters up to four bytes long.

For more information, see the Utility Guide on Semantics for Character Lengths .