¿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;

 

2017-10-18 Converiones 01.png

 

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;

 

2017-10-18 Converiones 02.png

 

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;

2017-10-18 Converiones 03.png

 

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

FROM vDatos v

 

2017-10-18 Converiones 04.png