ProblématiqueSous 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 |
ALTER system SET NLS_LENGTH_SEMANTICS='CHAR' scope=both sid='*'
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.
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 |
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 |
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; |
| Code : | Sélectionner tout |
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*/ |
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 |
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...