ORA-12899: value too large for column COMESFROMTOWN (actual: 50, maximum: 50)

Question

I am importing a csv file into an Oracle database using an external table.

The file encoding is UTF-8. The separator is actually ;.

The target table definition is identical to the external table definition. The NLS_LENGTH_SEMANTICS is defined as CHAR.

Target character fields are defined as COMESFROMTOWN VARCHAR2(50), (example).

Reading the external table and loading into the target table works fine for the majority of them. But records with COMESFROMTOWN length of 49 to 50 characters including a ',' (comma) are rejected because of length: ORA-12899: value too large for column COMESFROMTOWN (actual: 50, maximum: 50)

I tried resizing the external table fields, but the issue also happens when loading the target from the external table. Resizing the target is not an option.

Can you explain why:

• The comma being the 2c UTF-8 character, coded on 1 byte, it raises an error?
• NLS_LENGTH_SEMANTICS being defined as CHAR, it does not uses the requested number of bytes for the string?

How can I workaround this ?

Thanks.

Answer 1

Thanks to Wernfried Domscheit suggestion, this solved the issue :

1. ALTER SESSION SET nls_length_semantics = CHAR which was decisive
2. CHARACTERSET WE8ISO8859P1 which exactly fits the source file characterset

Note that our Oracle 11g instance is configured with following defaults:

• NLS_LENGTH_SEMANTICS = CHAR
• NLS_CHARACTERSET = UTF8