SQL Loader with utf8

Question

I am getting following error while loading Japanese data using SQL*Loader. My Database is UTF8 (NLS parameters) 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 Control 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 is throwing the wrong exception, (actual: 2624, maximum: 3500). 2624 is less than 3500.

Veradi

UTF8 is a multi-byte character set. 2624 characters could theoretically be 10,496 bytes. My guess would be that in SQL*Loader the char(3500) is byte semantic. Try upping it to 14,000. – Excruciation

hi ben,i tried char(14000) too. But didn't work. – Veradi

I think you have the same case that I had: https://mcmap.net/q/1776955/-ora-12899-while-actual-value-length-is-smaller-than-maximum/706012 – Libel

Answer 1

The default length semantics for all datafiles (except UFT-16) is byte. So in your case you have a CHAR of 3500 bytes rather than characters. You have some multi-byte characters in your file and the 2624 characters is therefore using more than 3500 bytes, hence the (misleading) message.

You can sort this out by using character length semantics instead

alter this line in your control file

characterset UTF8

to this

characterset UTF8 length semantics char

and it will work on characters for CHAR fields (and some others) - in the same way that you have set up your table, so 3500 characters of up to four bytes each.

See the Utilities Guide on Character Length Semantics for more information

Hallvard