ORA-12899 et character set

J'ai obtenu aujourd'hui l'erreur suivante en tentant de mettre à jour une table à partir d'une autre table située sur une base distante (via un dblink):

SQL> insert /*+ append */ into LNS$OWNER.MUT_ERROR_LOG_FILE
 2 select * from MUT_ERROR_LOG_FILE_SAV@LNS_LNSD12;
select * from MUT_ERROR_LOG_FILE_SAV@LNS_LNSD12
 *
ERROR at line 2:
ORA-12899: value too large for column
"LNS$OWNER"."MUT_ERROR_LOG_FILE"."ERR_COMMENT" (actual: 242, maximum: 240)

L'erreur précise que je souhaite insérer une chaine de caractères d'une taille de 242 (on verra après à quoi correspond ce nombre) alors que mon champ est défini par un VARCHAR2(240). Et pourtant la table source et la table cible ont étés crées à partir du même code SQL:

create table MUT_ERROR_LOG_FILE
(
  ERR_ID              NUMBER(10) not null,
  ERR_CLASSNAME       VARCHAR2(150) not null,
  ERR_USUALNAME       VARCHAR2(200) not null,
  ERR_MUTEX_ID        NUMBER(10),
  ERR_USER_ID         NUMBER(10),
  ERR_POST_DATE       DATE,
  ERR_DATE            DATE,
  ERR_OWNER_ERROR_ID  NUMBER(10) not null,
  ERR_OWNER_ENTITY_ID NUMBER(10),
  ERR_ENTITY_ID       NUMBER(10),
  ERR_LEVEL           NUMBER(10),
  ERR_COMMENT         VARCHAR2(240),
  ERR_AUTO_SOLUTION   NUMBER(1),
  ERR_LINKEDEVENT_ID  NUMBER(10),
  ERR_WARNING_DATE    DATE
)

Comment se fait-il alors que mon INSERT plante si les 2 champs sont en VARCHAR2(240)?
La cause est à chercher du côté du jeu de caractères défini pour chacune des 2 bases.
Ma base source utilise un character set single-byte(WE8DEC) alors que ma base cible utilise un character set multi-bytes, en l'occurence AL32UTF8.
Ce qu'il faut savoir c'est qu'en single-byte, 1 caractère correspond toujours à un seul octect. Par contre en mult-byte un caractère peut être défini sur plusieurs octects (1 à 4 bytes pour UTF8).
Illustrons cela avec un exemple.
Dans ma base en single-byte je crée une table avec une colonne de type VARCHAR2(10):

SQL> create table t1 (c1 varchar2(10));
 
Table created
 
SQL> insert into t1 values ('à');
 
1 row inserted
 
SQL> select dump(c1) from t1;
 
DUMP(C1)
--------------------------------------------------------------------------------
Typ=1 Len=1: 224

J'ai inséré dans le champ C1 la valeur 'à' et le résultat de la fonction dump indique bien que la taille stockée pour ce caractère est de 1 octect (Len=1).
Faisons la même chose sur la base définie avec un jeu de caractères multi-bytes:

 SQL> create table t1 (c1 varchar2(10));
 
Table created
 
SQL> insert into t1 values ('à');
 
1 row inserted
 
SQL> select dump(c1) from t1;
 
DUMP(C1)
--------------------------------------------------------------------------------
Typ=1 Len=2: 195,160 

 Cette fois ci le même caractère est stocké sur 2 octects (Len=2).

Revenons maintenant au nombre 240 déterminant la taille de ma colonne ERR_COMMENT.
Le type VARCHAR2 peut être défini selon 2 types de taille: BYTES ou CHAR. Avec un jeu de caractères single_byte un caractère vaut forcément un BYTE. Par contre avec un characterset multibytes un caractère peut être stocké sur plusieurs bytes.
Par défaut si on ne précise pas le type de taille c'est celle définie par le paramètre NLS_LENGTH_SEMANTICS qui est utilisée.
Sur mes 2 bases ce paramètre était défini à BYTE.
Donc dans ma table source j'avais des lignes dont la colonne ERR_COMMENT stockait jusqu'à 240 BYTES soit donc 240 caractères puisqu'elle utilise un jeu de caractères single-byte.
Par contre ces 240 caractères vont consommer plus que 240 BYTES sur ma base cible puisqu'elle utilise un jeu de caractères multi-bytes.
Comme ma colonne est définie avec une taille max de 240 BYTES (puisque NLS_LENGTH_SEMANTICS=BYTE) elle ne peut stocker tous les caractères que ma table source contenait d'où mon erreur ORA-12899: value too large for column.
La solution consiste à créer la table en précisant le type de taille CHAR pour indiquer qu'on souhaite stocker 240 caractères maximum et non 240 Bytes:

create table MUT_ERROR_LOG_FILE
(
  ERR_ID              NUMBER(10) not null,
  ERR_CLASSNAME       VARCHAR2(150 CHAR) not null,
  ERR_USUALNAME       VARCHAR2(200 CHAR) not null,
  ERR_MUTEX_ID        NUMBER(10),
  ERR_USER_ID         NUMBER(10),
  ERR_POST_DATE       DATE,
  ERR_DATE            DATE,
  ERR_OWNER_ERROR_ID  NUMBER(10) not null,
  ERR_OWNER_ENTITY_ID NUMBER(10),
  ERR_ENTITY_ID       NUMBER(10),
  ERR_LEVEL           NUMBER(10),
  ERR_COMMENT         VARCHAR2(240 CHAR),
  ERR_AUTO_SOLUTION   NUMBER(1),
  ERR_LINKEDEVENT_ID  NUMBER(10),
  ERR_WARNING_DATE    DATE
)

CONCLUSION:

La morale de cette histoire c'est que lorsque vous utilisez des bases avec un jeu de caractères multi-bytes il vaut mieux préciser (pour les colonnes de type VARCHAR2) une taille de type CHAR plutôt que de type BYTE.
Pour ce faire vous pouvez soit définir le paramètre NLS_LENGTH_SEMANTICS à CHAR ou bien ajouter la clause CHAR lorsque vous définissez vos colonnes de type VARCHAR2.