ORA-12899: value too large for column due to 12c char to data length conversion setting

ORA- Error:

ORA-12899: value too large for column "NSM_USER"."NSM_DOC_TYPE_TAB"."DIVISION" (actual: 103, maximum: 100)




Cause :

Table was created with explicit CHAR mentioned in VARCHAR2 data type.

1 CHAR is 4 BYTES.  

create table NSM_DOC_TYPE_TAB (

STATUS varchar2(32 char),   

CMS_CODE       varchar2(10 char),     

DIVISION    varchar2(100 char),   

DEPT   varchar2(255 char)                    

);        

If CHAR_LENGTH is showing 10 , so total  10 x 4 i.e. 40 Bytes of storage space available. 

The parameter NLS_LENGTH_SEMANTICS=(CHAR|BYTE)overrides the behavior in 12c on-wards. 

select table_name,column_name, data_length, char_length
from user_tab_columns
where table_name = 'NSM_DOC_TYPE_TAB'
order by column_id;

11g: NLS_LENGTH_SEMANTICS=CHAR

TABLE_NAME	COLUMN_NAME	DATA_LENGTH	CHAR_LENGTH
NSM_DOC_TYPE_TAB	STATUS	128	32
NSM_DOC_TYPE_TAB	CMS_CODE	40	10
NSM_DOC_TYPE_TAB	DIVISION	400	100
NSM_DOC_TYPE_TAB	DEPT	1020	255

12c: NLS_LENGTH_SEMANTICS=BYTE

TABLE_NAME	COLUMN_NAME	DATA_LENGTH	CHAR_LENGTH
NSM_DOC_TYPE_TAB	STATUS	32	32
NSM_DOC_TYPE_TAB	CMS_CODE	10	10
NSM_DOC_TYPE_TAB	DIVISION	100	100
NSM_DOC_TYPE_TAB	DEPT	255	255

Solution :

Set the parameter NLS_LENGTH_SEMANTICS=CHAR at session level through sqlplus or using logon trigger below for specific db users.

create or replace trigger change_nls_sem
after logon
on database
begin
if (user like 'NSM%') then
execute immediate 'ALTER SESSION SET NLS_LENGTH_SEMANTICS="CHAR"';
end if;
exception
when others then
null;
end;

/