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

nlsoracleutf-8varchar

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.

Best Answer

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
Related Question