Immer wieder ORA-01722 (invalid number)

Read this article in English →

Ein Problem, zu dem ich immer wieder befragt werde, ist das Auftauchen eines ORA-01722 in Abfragen, bei denen der Entwickler davon ausging, die Daten richtig eingegrenzt zu haben.

Hier ein Beispielszenario:

• In einer Tabelle wird eine VARCHAR2-Spalte durchsucht, die u.a. auch rein numerische Werte enthält.
• Die WHERE-Klausel schränkt die durchsuchten Zeilen so ein, daß eigentlich nur Werte mit Ziffern zurückgeliefert werden sollten.
• Dennoch kommt es zum ORA-01722: invalid number (ungültige Zahl)

Und das ist die Ursache:

• Beim Vergleich alphanumerischer  mit numerischen Werten führt Oracle ein implizites TO_NUMBER durch.
• Siehe hierzu die Oracle-Doku: „When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.“
• Dieses implizite TO_NUMBER findet vor der Anwendung der WHERE-Klausel statt.
• Dadurch kommt es zum Abbruch bei Zeilen, die eigentlich gar nicht in die Ergebnismenge gehören.

Beispiel

Wir erstellen uns eine Beispieltabelle mit einer VARCHAR-Spalte, die auch alphanumerische Werte enthält. Zur Unterscheidung zwischen numerischen und alphanumerischen Werten wird zusätzlich noch eine Spalte für den Typ angelegt:

CREATE TABLE t1( content_type VARCHAR2(1), mycontent VARCHAR2(10) )
/
INSERT INTO t1 VALUES( 'N', '12345' );
INSERT INTO t1 VALUES( 'T', 'a1234' );
COMMIT
/

Es ist noch ziemlich offensichtlich, warum dieses SQL fehlschlägt:

SELECT count(*)
  FROM t1
 WHERE mycontent > 1;

FEHLER in Zeile 3:
ORA-01722: invalid number

In der WHERE-Bedingung wurde zum Vergleich der numerische Wert 1 angegeben. Dies veranlasst Oracle, beim Durchsuchen der Spalte „mycontent“ ein implizites TO_NUMBER durchzuführen. Trifft dieses implizite TO_NUMBER auf einen alphanumerischen Wert, bricht das Statement ab.

Nun könnten wir versuchen, das Problem zu umgehen, indem wir über die Typ-Spalte nur die numerischen Werte heraussuchen:

SELECT count(*)
  FROM t1
 WHERE mycontent > 1
   AND content_type = 'N';

  COUNT(*)
----------
         1

Glück gehabt.

Aber Vorsicht! Es ist nicht gewährleistet, daß Oracle die Prädikate einer Abfrage immer zu unseren Gunsten auswertet. In komplexeren Joins kann es durchaus vorkommen, daß der Vergleich mycontent > 1 vor content_type = 'N' ausgeführt wird. Wir können dies simulieren, indem wir mit einem Hint Oracle dazu zwingen, die Prädikate in der geschriebenen Reihenfolge auszuwerten:

SELECT /*+ ORDERED_PREDICATES */ count(*)
  FROM t1
 WHERE mycontent > 1
   AND content_type = 'N'
;
ERROR:
ORA-01722: invalid number

Update vom 16.12.2008: In einem Artikel von J. Lewis wird erwähnt, daß speziell ab Oracle 10g dieses Szenario verstärkt auftaucht, da das „CPU Costing“ des Optimizers für eine veränderte Abfolge der Prädikate sorgen kann. Jonathan empfiehlt hier eine Änderung des Datenmodells (siehe Ansatz 3 unten).

Gegenmaßnahmen

Ansatz 1: Wir filtern die Werte heraus, die nur aus Ziffern bestehen. Dafür bietet sich die Funktion translate an. Wir benötigen allerdings noch ein zusätzliches Zeichen, das wir 1:1 konvertieren, denn wenn wir einen Leerstring zur Konvertierung einsetzen, werden alle Ergebnisse NULL. Im folgenden Beispiel gehen wir davon aus, daß das Zeichen ‚#‘ nicht in der durchsuchten Spalte vorkommt und verwenden daher dieses als „Dummy“:

SELECT ROWID, mycontent
  FROM t1
 WHERE translate( mycontent, '#0123456789','#' ) IS NULL;

Ansatz 1a: Ab Oracle 10g können in SQL auch Regular Expressions verwendet werden. Damit lässt sich die Filterung noch eleganter umsetzen. Beispielsweise können Vorzeichen und auch noch genau ein „.“ als Dezimaltrenner berücksichtigt werden:

SELECT ROWID, mycontent
  FROM t1
 WHERE REGEXP_LIKE( mycontent, '^[\+-]?\d+(\.\d+)?$' );

Ansatz 2: Es wird ein Constraint angelegt, das alle ROWIDs der fehlerhaften Datensätze in eine separate Tabelle „exceptions“ schreibt, die man mit dem von Oracle mitgelieferten Skript „utlexcpt.sql“ einrichten kann.

ALTER TABLE t1 ADD
CONSTRAINT check_numeric
 CHECK ( translate( mycontent, '#0123456789','#' ) IS NULL ) ENABLE
 VALIDATE
 EXCEPTIONS INTO exceptions;

Anhand der so in der Tabelle „exceptions“ gespeicherten ROWIDs können die fehlerhaften Daten nun entfernt werden.

Ansatz 3: Nach einer Datenbereinigung mit einem der o.g. Ansätze ein Redesign vornehmen, d.h. entweder den Datentyp der Spalte anpassen oder den passenden Datentyp im Code verwenden.