ORA-01722 « invalid number » : pourquoi et comment la corriger

ORA-01722 « invalid number » survient quand Oracle tente de convertir une chaîne de caractères en nombre… et n’y arrive pas. Le piège : la conversion est souvent implicite — vous ne l’avez même pas demandée. Voici les 5 situations classiques et comment les corriger durablement.

Le mécanisme

SELECT TO_NUMBER('123abc') FROM dual;
-- ORA-01722: invalid number

Jusqu’ici, logique. Mais le plus souvent, l’erreur surgit sans TO_NUMBER visible :

-- code_postal est un VARCHAR2 contenant parfois 'N/A'
SELECT * FROM adresses WHERE code_postal = 75001;
-- ORA-01722 ! Oracle convertit chaque code_postal en nombre pour comparer

Cause 1 — Comparaison chaîne = nombre (conversion implicite)

Si la colonne est VARCHAR2 et la valeur comparée est un nombre, Oracle convertit la colonne (pas le littéral). Une seule ligne non numérique suffit à tout casser.

-- ✅ comparer chaîne à chaîne
SELECT * FROM adresses WHERE code_postal = '75001';

Règle d’or : les littéraux entre quotes pour les colonnes texte, toujours.

Cause 2 — Données sales dans une colonne « numérique » stockée en VARCHAR2

Trouvez les coupables :

-- Oracle 12c R2+ : VALIDATE_CONVERSION
SELECT * FROM ventes WHERE VALIDATE_CONVERSION(montant AS NUMBER) = 0;

-- avant 12c R2 : filtre regex
SELECT * FROM ventes WHERE NOT REGEXP_LIKE(TRIM(montant), '^-?\d+([.,]\d+)?$');

Puis convertissez en sécurisant (12c R2+) :

SELECT TO_NUMBER(montant DEFAULT NULL ON CONVERSION ERROR) FROM ventes;

Le DEFAULT … ON CONVERSION ERROR remplace l’erreur par une valeur de repli — idéal en chargement. Voir aussi notre tutoriel TO_NUMBER Oracle.

Cause 3 — Séparateur décimal (virgule vs point)

‘12.50’ échoue si la session attend une virgule (NLS français), et inversement :

-- forcer le format indépendamment de la session
SELECT TO_NUMBER('12.50', '9999D99', 'NLS_NUMERIC_CHARACTERS=''.,''') FROM dual;
-- ou neutraliser avant conversion
SELECT TO_NUMBER(REPLACE(montant, ',', '.') DEFAULT NULL ON CONVERSION ERROR) FROM ventes;

Cause 4 — UNION / jointure entre types différents

Un UNION aligne les types : si la 1ʳᵉ requête renvoie un NUMBER et la 2ᵉ un VARCHAR2 non numérique → ORA-01722. Idem pour une jointure ON a.id = b.code où code est texte. Solution : convertir explicitement du côté texte (TO_CHAR(a.id) = b.code) — et corriger le modèle à terme.

Cause 5 — L’optimiseur évalue le filtre « avant » votre protection

Piège vicieux : vous filtrez WHERE est_numerique = 'O' AND TO_NUMBER(valeur) > 10, mais l’optimiseur peut évaluer le TO_NUMBER en premier sur toutes les lignes. Protections :

-- la plus simple et robuste
SELECT * FROM mesures
WHERE TO_NUMBER(valeur DEFAULT NULL ON CONVERSION ERROR) > 10;

-- ou CASE, qui garantit l'ordre d'évaluation
SELECT * FROM mesures
WHERE CASE WHEN REGEXP_LIKE(valeur,'^\d+$') THEN TO_NUMBER(valeur) END > 10;

Checklist de résolution

1. Un littéral numérique comparé à une colonne VARCHAR2 ? → mettre des quotes.
2. Trouver les lignes non numériques (VALIDATE_CONVERSION / regex).
3. Virgule vs point ? → NLS_NUMERIC_CHARACTERS ou REPLACE.
4. UNION/jointure mixte ? → TO_CHAR côté nombre.
5. Conversion dans un filtre ? → DEFAULT ON CONVERSION ERROR ou CASE.

FAQ

Pourquoi l’erreur n’apparaît que sur certaines exécutions ?

Le plan d’exécution change (ordre d’évaluation des filtres) ou les données sales n’apparaissent que dans certaines plages scannées.

Comment éviter ORA-01722 définitivement ?

Stocker les nombres dans des colonnes NUMBER (pas VARCHAR2). Toute « colonne texte qui contient des nombres » finira par produire cette erreur.

Quelle différence avec ORA-06502 ?

ORA-01722 vient du moteur SQL (conversion en requête) ; ORA-06502 vient du PL/SQL (affectation à une variable trop petite ou conversion en code).