Blog

Blog

To_number, ORA-01722 et NLS_NUMERIC_CHARACTERS

Une utilisatrice m'a appelée à l'aide car elle n'arrivait pas à corriger ses données en base. Elle avait une colonne de type VARCHAR2 dans laquelle étaient stockés des nombres (modèle de données fourni avec un progiciel). Pour une quarantaine de lignes de sa table, elle avait des nombres avec 28 décimales après la virgules, alors qu'elle ne souhaitait en conserver que 2, cette précision excessive ayant causé un incident de production (là encore, la qualité du code du progiciel ne semble pas extraordinaire). Elle me dit que la fonction ROUND en fonctionne pas et que pourtant sa syntaxe est bonne.

L'erreur ORA-01722

Tout d'abord, habitude que j'ai acquise rapidement en étant au contact avec les utilisateurs : vérifier leurs dire. Je lui demande donc un exemple précis de données avec lesquelles elle joue et je lance sa requête, qui revient à ça (son champ était de type texte) :
SELECT round('355.7885219720776879089176240276',2)
FROM dual;
Ce à quoi Oracle me répond :
ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
Le message est pourtant assez clair : il n'a pas reconnu un nombre.

La conversion explicite

Qu'à cela ne tienne, je tente une conversion explicite de ma chaîne de caractères en nombre :
SELECT to_number('355.7885219720776879089176240276')
FROM dual;
A nouveau ORA-01722. Je tente avec une virgule à la place du point :
SELECT to_number('355,7885219720776879089176240276')
FROM dual;
J'obtiens une réponse correcte. Il s'agit donc bien d'un souci de séparateur décimal. Le séparateur décimal est défini au niveau de la base dans le paramètre nls_numeric_characters.

Le paramètre nls_numeric_characters

Pour vérifier la valeur du paramètre, il suffit d'aller sur la vue v$nls_parameters.
SELECT *
FROM v$nls_parameters
WHERE parameter='NLS_NUMERIC_CHARACTERS'
-> La base me répond alors ", ". Cela signifie que pour la base un nombre a pour séparateur décimal la virgule et pour séparateur de groupe l'espace. Pour utiliser le paramètre nls_parameter sous Oracle, je suis obligée d'utiliser le paramètre format.

Le format d'un nombre

Pour modifier la valeur de nls_numeric_characters, il y a deux solutions :
  • Soit on modifie la valeur au niveau de la sessions (ALTER session SET nls_numeric_characters=', ')
  • Soit on précise la valeur à prendre en compte dans la fonction to_number, mais cela impose de préciser le format du nombre

Format des nombres sous Oracle

Ne me voyant pas expliquer la modification du paramètre nls_numeric_characters dans la sessions à mon utilisatrice, j'opte pour la deuxième option. Etant donné que la conversion implicite se plante, il va falloir lui donner le format de nos nombres. Il est bon de savoir que le format peu comporter plus de chiffres que le nombre en lui même mais en aucun cas il ne pourra en comporter moins. Cela nous donne donc ce format :
999D99999999999999999999999999999
Chaque 9 correspond à un chiffre, le D correspond au symbole représentant le séparateur décimal.

La conversion

Il faut donc convertir la chaîne de caractères en nombre, arrondir et reconvertir en chaîne de caractères.
SELECT to_char(round(to_number('355.7885219720776879089176240276','999D9999999999999999999999999999', 'NLS_NUMERIC_CHARACTERS=''. '''),2),'999D99','NLS_NUMERIC_CHARACTERS=''. ''')
FROM dual
Après, c'est vrai que la solution peut sembler complexe, mais avec des nombres stockées dans une colonne de type varchar, je ne peux pas faire de miracle. je suis DBA pas magicienne!

Pour aller plus loin

Vous pouvez consulter la documentation Oracle :