Fabien Celaia

Problématique

Sous Oracle, lorsque l'on crée une table avec une colonne de type chaîne de caractères, on spécifie généralement VARCHAR2(n)... mais n quoi ? en fait, par défaut, Oracle crée un champ de n BYTES... ce qui ne cause pas vraiment de soucis avec des jeux de caractères codés sur 1 byte, mais qui devient problématique avec de l'unicode (1 à 4 bytes/caractère) et une langue, comme le français, faisant la part belle aux accentués.

Au niveau de la création du champ, on peut en fait spécifier en quoi l'on souhaite déterminer la taille : VARCHAR2(n BYTE) pour du byte, VARCHAR2(n CHAR) pour du CHAR... mais sans cette spécification, Oracle prend la valeur du paramètre NLS_LENGTH_SEMANTICS qui est, par défaut, le BYTE.

La tentation est forte d'exécuter

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER system SET NLS_LENGTH_SEMANTICS='CHAR' scope=both sid='*'

... mais là, patatras... si on lit attentivement la documentation Oracle du paramètre, on tombe sur un triste

Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in run-time errors, including buffer overflows.

Source : https://docs.oracle.com/database/121...v.htm#NLSPG235

Donc nous sommes coincés entre la fonctionnalité (en Unicode, avec une langue accentuée, nous devrions passer le paramètre CHAR) et la recommandation Oracle (BYTE)

Procédure

Si vous devez passer de CHAR à BYTE pour revenir à la recommandation Oracle, il vous faut traiter les tables existantes.

Dans un premier temps, nous détectons et modifions les colonnes BYTES en CHAR. Oracle traite la chose de la manière suivante : rappelons-nous que la taille maximale d'un varchar2 est de 4000 bytes.

• Pour les varchar2 dont la taille est inférieure ou égale à 1000, Oracle les stocke en varchar2(4*bytes) : il agit de la sorte avec une politique du pire.
• Pour les varchar2 dont la taille est supérieure à 1000, Oracle les stocke en varchar2(4000)

Ces différences sont détectables dans la vue ALL_TAB_COLUMNS. Voici une requête permettant d'une part de les détecter, et d'autre part de générer le DDL permettant de corriger

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select  t.owner, t.table_name, c.column_name, data_type, data_length,    char_length, char_used, nullable, 'ALTER TABLE  '||t.owner||'."'||t.table_name||'" MODIFY "'||c.column_name||'"  '||data_type||'('||char_length||' CHAR);'
from all_tab_columns c
inner join all_users u on c.owner=  substr(u.username,1, instr(u.username,'_U')-1)
inner join all_tables t on t.owner=c.owner and t.table_name=c.table_name
where u.username like '%\_U' escape '\'
and char_used='B';

Une fois les tables corrigées, il convient de faire en sorte que notre générateur de code fonctionne correctement et spécifie bien des varchar2(n CHAR).

Certains générateurs de code (je citerai ici Liquibase) corrigent par eux-mêmes : détectant un code source multi-bytes (comme une string de Java), ils forcent l’utilisation du varchar2(n CHAR). Donc si c'est votre pain quotidien, pas de souci.

Mais si l'on ne peut influer sur la génération de code ?

On peut alors créer un déclencheur (trigger) qui passera le paramètre NLS_LENGTH_SEMANTICS à CHAR au niveau de la SESSION et pas au niveau du serveur.

Voici un exemple de code pour ce type de déclencheur:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE OR REPLACE TRIGGER DVP.DVP_logon_trigger 
  AFTER LOGON 
  ON DVP.SCHEMA 
BEGIN 
  execute immediate ''ALTER SESSION SET nls_length_semantics=''''CHAR'''''' ; 
END;

Et voici l'ordre permettant de générer ce type de déclencheur sur tous les schémas non système... Attention : il convient de n'appliquer ce déclencheur qu'aux connecteurs permettant de générer les ordres DDL... et pas forcément aux connecteurs applicatifs. Veillez cependant à penser aux connecteurs applicatifs qui créent des tables à la volée.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
select   'CREATE OR REPLACE TRIGGER '||username||'.'||username||'_logon_trigger 
  AFTER LOGON 
  ON '||username||'.SCHEMA 
BEGIN 
  execute immediate ''ALTER SESSION SET nls_length_semantics=''''CHAR'''''' ; 
END; 
/' 
from dba_users  
where ORACLE_MAINTAINED ='N' /*uniquement dès v.12*/

Dangers

Est-ce bien tout ? Non, sans doute pas. Le passage en UTF remontera un certain nombres de soucis, principalement dus à l'utilisation de fonctions sur chaînes de caractères de type LENGTH. Veillez à en informer vos développeurs... et apporter les corrections nécessaires le cas échéant.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SQL> create table toto (c char(5), cc char(5 char), v varchar2(5), vc varchar2(5 char), b clob ) ;

Table created.


SQL> insert into toto values ('été','été','été','été','été') ;


1 row created.

SQL> select length(c), length(cc), length(v), length(vc), length(b) from toto ;


 LENGTH(C) LENGTH(CC)  LENGTH(V) LENGTH(VC)  LENGTH(B)
---------- ---------- ---------- ---------- ----------
         3           5          3           3

SQL> select lengthb(c), lengthb(cc), lengthb(v), lengthb(vc) from toto ;


LENGTHB(C) LENGTHB(CC) LENGTHB(V) LENGTHB(VC)
---------- ----------- ---------- -----------
         5           7          5           5

QL> select lengthb(c), lengthb(cc), lengthb(v), lengthb(vc) from toto ;


LENGTHC(C) LENGTHC(CC) LENGTHC(V) LENGTHC(VC)
---------- ----------- ---------- -----------
         3           5          3           3

Conclusion

Avec un peu de gymnastique, on arrive à retrouver une situation stable, satisfaisant aux restrictions de l'éditeur. Je suis cependant un peu déçu d'avoir à disposition un paramètre qui permet de faire ce que je souhaite, mais que je ne peux employer... à mettre au chapitre des peaux de bananes Oracle...