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

 

 

Datenrettung

Ich mache selber keine Datenrettung weil ich dafür nur teilweise ausgerüstet bin, will aber dennoch einige Tips geben was zu tun ist und was im Ernstfall noch zu retten wäre.

Das Wichtigste zuerst

Wenn lebenswichtige, wertvolle oder unersetzliche Daten verschwunden oder defekt sind, dann besteht die Gefahr, dass sie endgültig verlorengehen weil der Computer den Platz wo sie gespeichert waren wieder verwendet und die Daten dadurch im Laufe der Zeit überschreibt. Daher sollte man gar nichts mehr tun auf dem betreffenden Computer, ihn auch nicht herunterfahren, sondern mit einem Fachmann alle weiteren Schritte besprechen.

In weniger wichtigen Fällen, wo ein Verlust der Daten in Kauf genommen werden kann, ist es möglich selber etwas zu tun.

Windows Papierkorb

Versehentlich gelöschte Dateien können in Windows einfach wiederhergestellt werden. Dazu öffnet man den Windows Papierkorb und sucht darin die gelöschte Datei. Klickt man auf diese mit der rechten Maustaste, dann kann man Wiederherstellen wählen und die Datei ist wieder dort wo sie vor dem Löschen war. So ist das Wiederherstellen recht unproblematisch. Das Betriebssystem nimmt einen nicht für voll und hat wohl manchmal recht. Oder man ist gerade deshalb so unkonzentriert weil da sowieso alles nochmal nachgefragt wird statt es einfach zu tun.

Datenverlust

– Ist die Datei nicht im Papierkorb, dann wurde sie vielleicht endgültig gelöscht. Das tut man wenn man im Papierkorbmenu auf Datei klickt und dann auf Papierkorb leeren. Selbst dann sind die Daten noch nicht weg. Erst wenn der PC an der Stelle etwas anderes speichert sind sie verloren.
– Es könnte auch sein, dass die Datei nicht auf der Festplatte des PC war sondern in einem Zusatzgerät (USB Stick, Kamera usw.) dann ist sie schon beim ersten Löschen verschwunden und nicht im Papierkorb. In dem Fall muss das Zusatzgerät wieder angeschlossen werden, um die Daten dort wo sie waren wiederherstellen zu können.
– Wenn man einen Datenträger (Festplatte oder Zusatzgerät) versehentlich formatiert oder partitioniert hat scheint alles weg zu sein was gespeichert war. Es ist aber noch da solange man nichts Neues speichert.

Datenwiederherstellung

In diesen Fällen ist es zweckmässig, sich ein Datenrettungsprogramm anzuschaffen und mit dem Computer bis dahin möglichst nichts mehr zu tun. Leider speichert der PC selber ständig etwas, so dass ein Datenverlust nicht ganz auszuschliessen ist. Die Wiederherstellungsprogramme gibt es teilweise kostenlos. Leider sind nicht alle gut und es ist sogar möglich, damit die Daten endgültig unlesbar zu machen. Je nach Wichtigkeit der Daten sollte man dafür mehr oder weniger ausgeben. Zum Beispiel: DataRecovery_EN
Um das teilweise Überschreiben verstümmelter oder verschwundener Daten zu verhindern ist es manchmal sogar sinnvoll beim Computer im laufenden Betrieb den Netzstecker zu ziehen. Gewiss, das schadet dem Computer und das sollte man wirklich nur tun wenn die verschwundenen Daten wichtiger sind als der Computer und das was aktuell am Computer gearbeitet wurde. Denn das kann dabei verlorengehen. In selteneren Fällen könnten die Daten der benutzten Programme nicht mehr zusammenpassen, wenn nicht ordentlich heruntergefahren wird. Dadurch könnte es sein, dass Programme mit den Daten nicht mehr funktionieren (wenn sie nicht so gut programmiert waren). Kann man diese Risiken eingehen, dann bestehen mehr Chancen die abhanden gekommenen Daten wiederherzustellen. Der Computer wird dann nicht heruntergefahren weil gerade dabei viel gespeichert wird, das die gelöschten Daten überschreiben könnte. Er wird auch nicht mehr hochgefahren, sondern von einem anderen PC aus die Festplatte gelesen. Der muss natürlich so eingestellt sein, dass er nichts auf diese Festplatte schreibt.

Datenfehler

Ein anderer Grund warum Daten verloren gehen sind Fehler im Dateisystem des Speichers. Also dort wo gespeichert ist welche Bruchstücke in welcher Reihenfolge zur Datei gehören. Der Grund ist oft ein Computerabsturz oder Stromausfall. Dann kann man sich in Windows mit Rechtsklick auf das betreffende Laufwerk dessen Eigenschaften ansehen. Darin gibt es die Möglichkeit der Fehlerüberprüfung und Dateisystemfehler automatisch zu korrigieren. Diese Datenwiederherstellung von Windows findet solche verschwundenen Dateien oder Dateifragmente. Sie werden nicht wiederhergestellt sondern jeder Fund wird als neue Datei im Hauptverzeichnis gespeichert. Da findet man dann die Dateien mit seltsamen Namen wie FILE0000.CHK Das ist leider keine verwendbare Datei mehr. Man muss erraten welche Datei es vorher war. Es gibt auch Programme die einem raten helfen wenn es zu viele Dateien sind. Dann muss man die Datei umbenennen, am besten so wie sie früher geheissen hat. Zumindest die letzten Buchstaben müssen wieder stimmen beim Namen. Dann kann man versuchen, diese Datei zu verwenden. Funktioniert das und lässt sie sich öffnen, dann hat man sie wieder und kann sie dorthin abspeichern wo sie früher war. Funktioniert das Öffnen der Datei trotz Umbenennen nicht, dann könnte es sein, dass es früher eine andere Datei war und nicht die die man sucht. Besonders bei grossen Dateien (Videos) passiert es oft, dass es schon die richtige Datei wäre aber trotzdem nicht funktioniert. Das liegt daran, dass es nicht die vollständige Datei ist sondern nur ein Teil davon. Wenn dem Teil wichtige Sachen fehlen dann funktioniert das nicht. Der Grund ist das dynamische Speichersystem des PC. Der Speicher ist selten ganz leer und Platz gibt es oft nur da wo schonmal etwas gespeichert war das gelöscht wurde. Der Speicher besteht also aus vielen Abschnitten von Speicherplätzen, von denen manche besetzt sind und andere frei weil gelöscht oder frei weil noch nie benutzt. Speichert man nun so einen grossen Film, dann sucht er sich einen freien Platz und speichert den Anfang ab. Dann stellt er fest der freie Platz ist zu wenig für den Film. Dann sucht er sich die nächste freie Stelle im Speicher und setzt dort den Film fort. Auf diese Weise kann der Film im Speicher dann etliche verschiedene Stellen belegen. Er wird nur selten in einem Stück zusammenhängend gespeichert. Bei der Wiederherstellung bekommt man aber für jedes Teilstück eine eigene Datei und keines funktioniert weil der Film nirgends vollständig ist. Wenn man den Datenträger häufig defragmentiert, dann kann man vermeiden, dass der Film bruchstückweise abgespeichert wird. Dann hat man öfter Glück und die Wiederhergestellten Dateien lassen sich öffnen. Wenn nicht dann könnten spezielle Videoreparaturprogramme wenigstens Bruchstücke des Films wieder abspielbar machen. Zum Beispiel ein Programm zur Reparatur kleiner AVI Filme: DivFix

Hardwarefehler

Bei defekter Festplatte werden Teile der Daten unlesbar. Man kann die Festplatte per Programm überprüfen, wobei dann beschädigte Stellen der Platte nicht mehr verwendet werden. Die Daten die dort waren sind aber nicht wiederherstellbar. Es ist besser gleich eine neue Festplatte einzubauen und die Alte erstmal in Ruhe abkühlen lassen. Wenn möglich dann zuerst die wichtigsten Daten von der alten Festplatte auf die neue überspielen. Man erhält ggf. beschädigte Dateien. Es ist einen Versuch wert, solche Dateien irgendwie doch noch zu öffnen und den Defekt manuell zu reparieren. Eine defekte Festplatte die nicht mehr ganz lesbar ist aber noch halbwegs funktioniert kann man aufbewahren. Es kommt vor, dass zu einem späteren Zeitpunkt Dinge wieder lesbar werden die zunächst defekt waren.
Bei Totalschaden:
Hardwareschaden der Festplatte, Überspannung, Bruch, gelöscht und teilweise überschrieben. Da hilft dann nur mehr ein Spezialverfahren, das die noch vorhandenen Daten vom Speichermedium selbst abliest (Festplatte zerlegt) und die Millionen von Bruchstücken wieder zusammenführt zu Dateien. Das ist etwa so wie aus einem Pfund Semmelbrösel die Semmeln wieder zusammenzukleben. Langwierig und teuer. Auf diese Weise konnten sogar zerstörte Festplatten aus dem World Trade Center noch gelesen werden.