ab Oracle 12c R2 !
Problem:
Wer kennt nicht das Problem, dass bei einem ETL Load eine von xxxxx tausend Zeilen einen Fehler aufweist und der ganze Import mit einem „ORA-01722: invalid number“ oder einem „ORA-01843: not a valid month“ fehlschlägt.
Wie kann nun aber der fehlerhafte Datensatz gefunden werden?
Lösung
Ab der Version 12c R2 der DB steht uns dazu die VALIDATE_CONVERSION Function zur Verfügung und Erweiterte Parameter bei den to_xxxx Funktionen, die eine Fehlerhandling ermöglichen.
Test Daten:
CREATE TABLE convtest ( id NUMBER, wert1 varchar2(20)); INSERT INTO convtest (id,wert1) VALUES ( 1,'78609'); INSERT INTO convtest (id,wert1) VALUES ( 2,'786,67'); INSERT INTO convtest (id,wert1) VALUES ( 3,'12.12.2017'); INSERT INTO convtest (id,wert1) VALUES ( 4,'01.30.2018'); commit;
Alle Zeilen mit Zahlen mit der VALIDATE_CONVERSION Funktion erkennen:
SELECT id FROM convtest WHERE VALIDATE_CONVERSION(wert1 AS NUMBER) = 1; ID --- 1 -- With format mask SELECT id FROM convtest WHERE VALIDATE_CONVERSION(wert1 AS NUMBER,('999D99'),'NLS_NUMERIC_CHARACTERS = '',.''') = 1; ID ----- 2 -- read Date SELECT id FROM convtest WHERE VALIDATE_CONVERSION(wert1 AS DATE,('dd.mm.yyyy')) = 1; ID ----- 3
In einer 12.2 R2 Umgebung habe ich allerdings folgendes Problem : PL/SQL: ORA-43918: This Argument Must Be A Literal (Doc ID 2463944.1)
Demnächst mehr