SQL Programmierer

ORA-01722: Ungültige Zahl

Bei der Umwandlung von Strings in Zahlen mittels SQL kommt manchmal die Fehlermeldung:
ORA-01722: invalid number oder
ORA-01722: Ungültige Zahl
Ein berühmter Fehler. Der kommt auch, wenn man denkt gar keine ungültige Zahl zugelassen zu haben in der SQL Abfrage. Der Fehler entsteht normalerweise, wenn Varchar2 oder andere String Größen in Zahlen umgewandelt werden durch die Funktion TO_NUMBER(). Dann muss alles was umgewandelt wird, eine Zahl darstellen, auch wenn sie als String gespeichert ist. Sind auch Werte gespeichert, die nicht nur Zahlen sind (z.B. ‚Mai 2015′), so muss man diese von der Abfrage ausschliessen, damit sie nicht in die Funktion TO_NUMBER() geraten können und eine Exception auslösen. So weit so gut. Aber wenn man gar kein TO_NUMBER() hat in der Abfrage passiert das auch. Überall wo Stringwerte mit Zahlenwerten verglichen werden oder wo Stringwerte statt Zahlenwerten als (konkret gespeicherte Werte in einer Tabellenspalte) Argument übergeben werden, oder wo solche Werte in Zahlenspalten eingetragen werden, da macht die Datenbank von sich aus eine Umwandlung mittels TO_NUMBER(). Diese ist die Ursache des Fehlers und oft nicht das konkret programmierte SQL. Verflixterweise geschieht diese automatische Umwandlung noch vor Auswertung der Where Klausel des eingegebenen SQL Befehls. Alle Eingrenzungen dort zur Vermeidung ungültiger Zahlen sind daher wirkungslos. Wenn irgendwo in der betreffenden Tabellenspalte eine ungültige Zahl steht löst die vorherige automatische Zahlenumwandlung diesen Fehler aus, auch wenn in der beabsichtigten Abfrage solche ungültigen Werte gar nicht abgefragt werden, sondern im where clause davon ausgeschlossen wurden. In dieser Datenbank führen automatische Umwandlungen vorsichtshalber eher zu Exceptions als zu Ersatzwerten. Dies zwingt dazu, explizite Umwandlungen zu programmieren. Obwohl das mühsamer ist hat man so doch immer die Kontrolle, wann welche Umwandlung vorgenommen wird und welche Datentypen gerade vorliegen.

– Macht man die Datentypumwandlung explizit mittels TO_NUMBER(COLUMNNAME), so sieht man, wo die nichtnumerischen Werte der Spalte COLUMNNAME herausgefiltert oder ersetzen werden müssen. Das geht am Besten in einer Case Struktur. Setzt man diese statt des COLUMNNAME in die Funktion ein, dann bekommt die Funktion keine ungültigen Zahlen mehr und arbeitet fehlerfrei. Zum Beispiel für Intergerzahlen in COLUMNNAME verwendet man:

CASE WHEN (TRIM(TRANSLATE(COLUMNNAME, '1234567890', ' ')) IS NOT NULL)
                 THEN '0'
                 ELSE COLUMNNAME
END

Dadurch wird dann die Ziffer 0 eingesetzt statt den nichtnumerischen Werten.

– Um die Abfragen übersichtlich zu halten ist es sinnvoll, solche expliziten Umwandlungen z.B. in eine View oder Stored Procedure zu verlagern. Vor allem bei der View kann man dann die Viewspalte wie eine numerische Tabellenspalte problemlos verwenden, obwohl sie eigentlich auf Stringwerten beruht.

– Wenn man die Datenstruktur bearbeiten darf, dann bietet sich an, Tabellenspalten, die numerisch gebraucht werden immer auch numerisch zu definieren oder in solche umzuwandeln. Das erspart explizite wie implizite Umwandlungen während der Abfragen.