Unexpected ORA-12899 with the use of multi-byte character sets like UTF8 and default BYTE setting for NLS_LENGTH_SEMANTICS

Problem Definition

When using utf8 (multibyte character set), changing the default setting of NLS_LENGTH_SEMANTICS = BYTE to NLS_LENGTH_SEMANTICS = CHAR can help to avoid unexpected ORA-12899: value too large for column errors on AO columns. The problem is that with the default setting VARCHAR(N) translates to a length of n Bytes and not necessarily n characters in UTF8, so you would get a ORA-12899 error when you try to store n multibyte characters in that column.

This can be reproduced with a simple ActiveObjects test class as follows:

— class —
import net.java.ao.*;
import net.java.ao.schema.StringLength;
import net.java.ao.schema.Table;

@Table("T_TEST_CLASS")
public interface TestClassAO extends Entity {
String TEST_FIELD_ID = "C_TEST_FIELD";

@Accessor(TEST_FIELD_ID)
@StringLength(6)
String getTestField();
}

— run test —
myActiveObjects.create(TestClassAO.class, new DBParam(TestClassAO.TEST_FIELD_ID, "привет"));

Suggested Solution

It's Oracle DB feature: NLS_LENGTH_SEMANTICS. This setting manages how Oracle DB will calculate length of VARCHAR2 (in out case). Default value is BYTE (it means "calculate by bytes"). We should use CHAR (calculate by chars) for expected results of UTF-8 usage... In this way we must define this parameter explicitly during table creating. For example: VARCHAR2(18) must be replaced by VARCHAR2(18 char).
See
https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams127.htm#REFRN10124
http://gerardnico.com/wiki/database/oracle/byte_or_character