ORA-01861: literal does not match format string

Bei der Umwandlung von Strings in ein Datum mittels SQL kommt es manchmal zur Fehlermeldung:
ORA-01861: literal does not match format string
Diese ist ein häufiger Begleiter, wenn man das Datum als varchar2 oder sonstigen String gespeichert hat. Will man damit Berechnungen anstellen, muss es vorher mittels TO_DATE() in ein Datumsformat umgewandelt werden. Das funktioniert zwar meistens, aber hat man in den Daten varchar mit einem falsch formatierten Datumstring, dann kommt dieser Fehler bei Auswertung eines solchen Datums. Die Datenbank ist etwas flexibel und besteht nicht zu 100 Prozent auf Einhaltung des Formats, sondern glaubt auch fehlerhafte Angaben noch lesen zu können. Dadurch kann es aber zu falschen Umwandlungen kommen. Es ist besser die genaue Formateinhaltung bei der Auswertung zu erzwingen mittels FX im Formatstring. Solange irgendwo noch ein falsches Datum gespeichert ist, kommt der Fehler oder ein Folgefehler immer wieder mal vor, meistens zum ungünstigsten Zeitpunkt. Deshalb ist es besser, gleich gründlich damit aufzuräumen.

– Gewiss ist es sinnvoll, Datentypen zu verwenden, die als Datum deklariert sind und kein falsches Datum zulassen. Arbeitet man jedoch mit verschiedenen Datenbanken, je nachdem was der Kunde auf dem Server installiert hat, so kann es sinnvoll sein zwecks Portierbarkeit von Programm und Daten als Strings zu speichern. Denn Datumsspalten sind kaum datenbankübergreifend kompatibel, da sind sich die Datenbankhersteller wieder mal nicht einig. Ferner hat man oft Daten aus älteren Quellen oder aus CSV-, XML-Export usw. wo man selber keinen Einfluss hat auf den Datentyp und die Korrektheit.

– Daher bleibt oft nur das Ausfiltern ungültiger Datumsstrings, bevor das TO_DATE zum Einsatz kommt. Das TO_DATE macht man am besten am Ende einer CASE Struktur, darin können vorher alle ungültigen Fälle abgefangen und gesondert behandelt werden. Dann wird dort kein TO_DATE angewendet, weil das mit fehlerhaftem Datum nur eine Fehlermeldung oder ein fälschlich korrigiertes Datum ergibt. Da ist es besser selber zu korrigieren. Deshalb wird (unbedingt als erstes) geprüft, ob die Datumsangabe das Format erfüllt, normalerweise ‚YYYY-MM-DD‘ oder ein anderes angegebenes Format. Man muss dabei sicherstellen, dass dort wo Zahlen sein sollen wirklich nichts als Zahlen sind. Denn in der weiteren Datumsprüfung müssen Zahlen ausgewertet werden. Das erfordert eine Umwandlung des Strings in Zahlen und die würde einen anderen Fehler ergeben, wenn das nicht wirklich nur Zahlen sind. Nach der Formatprüfung können immer noch formal korrekte aber ungültige Datumsangaben vorkommen, die bei weiterer Verarbeitung zu weiteren Fehlern führen. Man muss also auch ungültige Datumsangaben vorher abfangen, wie z.B. 31.11.2013, 29.2.2001 oder im Standardformat 2013-11-31 und 2001-02-29. Dazu liest man mittels SUBSTR() die Jahreszahl, das Monat und den Tag. Damit stellt man verschiedene Bedingungen auf wann ein Datum gültig ist. Zum Beispiel darf der Monat nicht grösser als 12 sein. Dazu gehören auch die Regeln für den 29.Februar, den es nur in Schaltjahren gibt. Und Schaltjahr ist nicht alle 4 Jahre, sondern genau dann, wenn das Jahr durch 4 teilbar ist aber nicht durch 100 teilbar ist, oder wenn es durch 400 teilbar ist. So wird aus einer simplen Datumsumwandlung doch oft ein ganzer SQL-Block, wenn man fehlerhafte Datumsangaben nicht anderweitig ausschliessen kann. Nur so bekommt man eine immer funktionierende Abfrage, und nicht nur eine meistens funktionierende.

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.

Ratgeber

Sichere EDV

Datenrettung

Datensicherheit

(in der Reihenfolge wird das meistens gemacht, einfacher wäre es umgekehrt)

Bugreport

Als Bugs (englisches Wort für Käfer) bezeichnen wir Programmfehler, die dem Programmierer meistens schnell zeigen was noch zu tun ist. Die meisten sind bereits hinreichend dokumentiert, manche sind irreführend und die Interessantesten haben tiefgründige Ursachen, denen ein guter Programmierer nachgehen muss. Sie sind schuld, dass Programmierer immer schlauer werden und zeitlebens dazulernen. Aber sie kosten auch viel Zeit. Und so ist es sinnvoll sich darüber auszutauschen, so dass jemand mit ähnlichen Sorgen schneller zu einer Lösung kommt. Im folgenden sind einige interessante Bugs aufgelistet:

ORA-00936: Ausdruck fehlt

ORA-01722: Ungültige Zahl

ORA-01861: literal does not match format string

 

 

ORA-00936: Ausdruck fehlt

Eine recht häufige Fehlermeldung in der Oracle Datenbank betrifft die SQL Syntax:
ORA-00936: Ausdruck fehlt
In der Regel muss das SQL dann ergänzt werden. Jedoch gibt es auch Fälle wo die Fehlermeldung irreführend ist. Dieser Fehler kommt nicht nur, wenn man etwas weggelassen hat, sondern oft auch da wo gar kein Ausdruck fehlt. Zum Beispiel wenn die Select Liste der Abfrage am Ende ein Komma hat das da nicht hingehört. Er passiert auch unerwartet wegen Sonderregeln der Syntax bei Unterabfragen. Verwendet man als Argument einer Funktion eine Unterabfrage, dann braucht es doppelte statt einfache Klammern: Zur Erläuterung:

SELECT TRIM(COLUMNNAME) FROM MYTABLE

ist korrekt.

SELECT TRIM(SELECT COLNAME FROM OTHERTABLE WHERE...) FROM MYTABLE

ist nicht korrekt und führt zu so einer Fehlermeldung.

SELECT TRIM((SELECT COLNAME FROM OTHERTABLE WHERE...)) FROM MYTABLE

So ist das korrekt, es braucht seltsamerweise eine doppelte Klammerung, einmal die Klammer der Funktionsargumente und zusätzlich die Klammer des Subselect. Die Fehlermeldung, dass ein Ausdruck fehlt ist da fehl am Platz. Auf docs.oracle.com wird erklärt:
In all cases, a scalar subquery must be enclosed in its own parentheses, even if its syntactic location already positions it within parentheses (for example, when the scalar subquery is used as the argument to a built-in function).
Obwohl das trivial ist wird das doch häufig übersehen, weil es nicht ins Weltbild der Programmierung passt. Hier verhält sich SQL anders als man es von den Klammerregeln der meisten Programmiersprachen gewohnt ist. Ferner fällt man darauf herein, wenn man mittels Suchen und Ersetzen schnell Änderungen im Programm umsetzen will. Ersetzt man in den Datenbankabfragen eine Tabellenspalte durch ein Subselect oder umgekehrt, muss man aufpassen. Oder wenn das SQL aus Variablen einer anderen Programmiersprache dynamisch aufgebaut wird, darf man Subselects und Spaltennamen nicht einfach austauschen in der Variablen, sonst wird ein korrektes Programm zur Laufzeit doch noch fehlerhaft. Aber es funktioniert ganz gut, wenn man die Klammern des Subselect gleich in die Variable aufnimmt. Also wenn die Fehlermeldung kommt ist das nur manchmal einem fehlenden Ausdruck geschuldet. Vielmehr ist es eine allgemeine Syntaxwarnung, die diverse Ursachen haben kann.