Oracle 12c R2 - Conversiones de Tipo de Datos y Manejo de Error

¿Como validar si un dato puede ser convertido? 

Cuando se deben realizar conversiones de datos, es frecuente encontrar valores que no pueden ser convertidos al tipo de datos deseado.

Tomemos el siguiente ejemplo (el texto en azul lo utilizaremos en todos los ejemplos del presente artículo):

WITH vDatos (ID, Fecha) AS
( SELECT 1, '20170101' FROM DUAL
  UNION ALL
  SELECT 2, '20171501' FROM DUAL
  UNION ALL
  SELECT 3, '20170115' FROM DUAL
  UNION ALL
  SELECT 4, '01-apr-2017' FROM DUAL
  UNION ALL
  SELECT 5, '01-abr-2017' FROM DUAL
  UNION ALL
  SELECT 6, '01/08/17' FROM DUAL
  UNION ALL
  SELECT 7, '30-Feb-17' FROM DUAL
)
SELECT ID, Fecha, TO_DATE(Fecha)
FROM vDatos v;

Según cómo tengamos configurados los parámetros NLS_DATE_FORMAT y NLS_DATE_LANGUAGE, al ejecutar la consulta anterior nos encontraremos con alguno de estos mensajes de error, los cuales impiden la ejecución de la consulta:

ORA-01861: literal does not match format string
ORA-01843: not a valid month
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-01839: date not valid for month specified

Es por ello que es normal que quienes se han encontrado repetidamente con este problema, hayan creado funciones del estilo "es_una_fecha()" o "es_un_numero()" para poder identificar los datos que pueden ser convertidos, evitando los errores que cancelan la ejecución de la consulta.

En Oracle 12c Release 2, se introduce una nueva función "VALIDATE_CONVERSION" que recibe como entrada un dato y un tipo de datos al que deseamos convertir el dato original, y la función nos devuelve 1 cuando es posible realizar la conversión y 0 cuando no es posible.

Ejemplo (utilizando la clausula WITH del ejemplo anterior)

SELECT v.ID, v.Fecha, TO_DATE(v.Fecha)
FROM vDatos v
WHERE VALIDATE_CONVERSION(v.Fecha AS DATE) = 1;

También es posible (y recomendado) utilizar un formato específico para probar si el dato puede ser convertido, como vemos en este ejemplo (utilizando la clausula WITH del ejemplo anterior)

SELECT v.ID, v.Fecha, TO_DATE(v.Fecha,'YYYYMMDD')
FROM vDatos v
WHERE VALIDATE_CONVERSION(v.Fecha AS DATE, 'YYYYMMDD') = 1;

De esa forma, podemos identificar aquellos registros que pueden ser convertidos y aquellos que no pueden serlo. Esto resuelve muchos problemas de conversión de datos, permitiendo ignorar los registros que no poseen un dato que puede ser convertido.

¿Qué pasa cuando necesitamos procesar TODOS los registros, y utilizar algún valor especial cuando no es posible realizar la conversión?

En muchos escenarios, identificar que dato puede ser convertido y cual no puede no ser suficiente. Por eso Oracle 12C Release 2 introduce una nueva opción dentro de las funciones de conversión de datos que permite especificar un valor por defecto cuando no es posible realizar la conversión, en vez de generar un error.

Esta opcion es "DEFAULT {VALOR|NULL} ON CONVERSION ERROR" y puede ser utilizada tanto en la función CAST como en las funciones de conversión tradicionales ("TO_DATE", "TO_NUMBER", etc.).

A continuación podemos ver dos ejemplos de como utilizarlos (utilizando la clausula WITH del primer ejemplo para generar los datos)

SELECT v.ID, v.Fecha, TO_DATE(v.Fecha DEFAULT '19000101' ON CONVERSION ERROR,'YYYYMMDD')
FROM vDatos v

SELECT v.ID, v.Fecha, TO_DATE(v.Fecha DEFAULT NULL ON CONVERSION ERROR,'DD/MM/YY')
FROM vDatos v