TO_DATE() – Hatalı Kaydı Bulma

Oracle’da karakter tipindeki tarihleri, TO_DATE built-in fonksiyonu ile date tipine dönüştürürken bir çok hata ile karşılaşmamız olası. Özellikle veriambarı süreçlerinde milyonluk kayıtlar içinde hatalı kaydı bulmak çok zor olabiliyor. Hatalı kaydı bulmak için Log Errors Into yöntemi kullanılabilir ( bu methodu anlatmak için ayrıca bi başlık gerekir). Ben farklı bir yöntemden daha bahsedeceğim. Tablo oluşturmaya gerek kalmadan hatalı kaydı bulabilirsiniz.

Aşağıdaki sorguda with as içinde 2 doğru kayıt, 1 adet null kayıt var. Diğer kayıtlarda farklı hatalar var. With As’den dönen ornek objesine select atarken where condtition’da TO_DATE içine “DEFAULT NULL ON CONVERSION ERROR” ifadesini ekledim. Bu ifade, to_date’de hata veren kayıtları NULL’a çevirip sql’in hata almamasını sağlar. Where condition’da ilgili ifadeyi is null şeklinde girerek normalde hata verecek kayıtlar listelenir.

WITH ornek AS (
 SELECT '20230101'  as date_tx FROM dual  --DOGRU--correct
 UNION ALL
 SELECT '20230141'  FROM dual --ORA-01847: day of month must be between 1 and last day of month
 UNION ALL 
 SELECT '20232101'  FROM dual --ORA-01843: not a valid month
 UNION ALL
 SELECT '202x0101'  FROM dual --ORA-01858: a non-numeric character was found where a numeric was expected
 UNION ALL
 SELECT '202301019' FROM dual --ORA-01830: date format picture ends before converting entire input string
 UNION ALL
 SELECT '202301'    FROM dual --ORA-01840: input value not long enough for date format
 UNION ALL
 SELECT NULL FROM dual --null
 UNION ALL 
 SELECT '20230101'  FROM dual --DOGRU--correct
)
SELECT date_tx
  FROM ornek
 WHERE TO_DATE(date_tx DEFAULT NULL ON CONVERSION ERROR, 'YYYYMMDD') IS NULL
       and date_tx IS NOT NULL --normalde null olan kayıtları getirmez.

Tüm kayıtları getir, hatalı olanları null olarak göster demek istersek, where condition’ı boş bırakıp, to_date’in içine DEFAULT NULL ON CONVERSION ERROR ifadesini ekleriz:

WITH ornek AS (
 SELECT '20230101'  as date_tx FROM dual  --DOGRU--correct
 UNION ALL
 SELECT '20230141'  FROM dual --ORA-01847: day of month must be between 1 and last day of month
 UNION ALL 
 SELECT '20232101'  FROM dual --ORA-01843: not a valid month
 UNION ALL
 SELECT '202x0101'  FROM dual --ORA-01858: a non-numeric character was found where a numeric was expected
 UNION ALL
 SELECT '202301019' FROM dual --ORA-01830: date format picture ends before converting entire input string
 UNION ALL
 SELECT '202301'    FROM dual --ORA-01840: input value not long enough for date format
 UNION ALL
 SELECT NULL FROM dual --null
 UNION ALL 
 SELECT '20230101'  FROM dual --DOGRU--correct
)
SELECT TO_DATE(date_tx DEFAULT NULL ON CONVERSION ERROR, 'yyyymmdd')
  FROM ornek