|
Did you already know: Konvertierungsfunktionen (conversion function) im Fehlerfall
Immer wieder bin ich überrascht, wie wenig bekannt gewisse sehr nützliche Funktionen und Techniken der Oracle Datenbank
aus den Bereichen Oracle SQL und PL/SQL sind.
Aus diesem Grunde kam ich auf die Idee eine kleine Serie mit dem Präfix "Did you already know" zu starten. Die unterschiedlichsten
Funktionen und Technologien aus verschiedenen Oracle Releases sollen kurz erklärt und mit Beispielen veranschaulicht werden, um schnell die Funktionsweise zu erlernen.
Als erstes Thema möchte ich eine mit Oracle Database 12.2 eingeführte sehr nützliche Erweiterung von Oracle Konvertierungsfunktionen aufgreifen.
Während der DOAG Veranstaltung ist dieses Feature von vielen Datenbank Entwicklern sehr positiv aufgenommen worden.
Aber zuerst zum Standardverhalten: Tritt ein Konvertierungsfehler auf, brechen die Konvertierungsfunktionen normalerweise mit einer Fehlermeldung ab. Mit der Funktion TO_NUMBER
ist dieses Verhalten sehr leicht zu zeigen. TO_NUMBER konvertiert einen Ausdruck in einen Wert vom Datentyp NUMBER.
Dabei kann ein Ausdruck eine Zeichenkette vom Typ CHAR, VARCHAR2, NCHAR oder NVARCHAR2,
ein numerischer Wert vom Typ NUMBER, BINARY_FLOAT oder BINARY_DOUBLE oder ein Null Wert sein. Das erste Beispiel demonstriert nun das Standardverhalten.
SQL> select to_number(sysdate) from dual; ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number.
Dies ist natürlich nicht wünschenswert und führt dazu, dass man in den Programmen oder Skripten ein Fehlerhandling einbauen muss. Aus diesem Grund gibt es ab Oracle Database 12.2 eine nützliche Erweiterung: Innerhalb der Funktionen kann man nun einen Wert mitgeben, der bei einem Konvertierungsfehler zurückgegeben werden soll. Es wird also kein Fehler mehr ausgegeben. Die allgemeine Syntax dazu lautet : TO_NUMBER(expr [ DEFAULT return_value ON CONVERSION ERROR ]). Folgendes Beispiel zeigt die Verwendung.
SQL> select to_number(sysdate default 1000 on conversion error) wert from dual;
WERT
----------
1000
Folgende Funktionen unterstützen diese Erweiterungen:
- CAST: Konvertierung von built-in Datentypen oder Collection Types in einen anderen built-in Datentyp oder Collection Typ
- TO_BINARY_DOUBLE: Konvertierung in eine "double-precision" Gleitkommazahl
- TO_BINARY_FLOAT: Konvertierung in eine "single-precision" Gleitkommazahl
- TO_DATE: Konvertierung von Character Datentypen zu DATE
- TO_DSINTERVAL: Konvertierung zu INTERVAL DAY TO SECOND Datentyp
- TO_NUMBER: Konvertierung nach Number Datentyp
- TO_TIMESTAMP: Konvertierung von Character Datentyp zu TIMESTAMP Datentyp
- TO_TIMESTAMP_TZ: Konvertierung zu TIMESTAMP WITH TIME ZONE
- TO_YMINTERVAL: Konvertierung zu INTERVAL MONTH TO YEAR
In diesem Zusammenhang gibt es noch eine weitere Funktion, die sehr nützlich sein kann - nämlich VALIDATE_CONVERSION. Diese prüft vorab, ob sich der Ausdruck überhaupt in den angegebenen Datentyp konvertieren lässt. Wenn der Ausdruck erfolgreich konvertiert werden kann, dann gibt diese Funktion 1 zurück, anderenfalls wird 0 ausgegeben. Wenn der Ausdruck mit NULL verwendet wird, gibt diese Funktion 1 zurück. Wenn bei der Auswertung eines Asudrucks ein Fehler auftritt, gibt die Funktion den Fehler zurück. Folgende Beispiele demonstrieren die Funktionsweise.
SQL> SELECT VALIDATE_CONVERSION('TEST' AS NUMBER) Ergebnis FROM DUAL;
ERGEBNIS
----------
0
SQL> SELECT VALIDATE_CONVERSION(1000 AS BINARY_DOUBLE) ergebnis FROM DUAL;
ERGEBNIS
----------
1
SELECT VALIDATE_CONVERSION('1.1.2019' AS date) ergebnis FROM DUAL;
ERGEBNIS
----------
1
Möchte man mehr dazu erfahren, können Sie im Handbuch SQL Language Reference Guide die Details zu den einzelnen Funktionen finden.
Lizenzierung
Die Konvertierungsfunktionen stehen in allen Datenbank Editionen On-Premise und in der Cloud zur Verfügung.
Weitere Informationen
Zurück zur Community-Seite