Skip navigation

¿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

APPROX_COUNT_DISTINCT en Oracle 12.1

En muchos escenarios de negocio, se requiere conocer información acumulada (total de ventas, cantidad de clientes nuevos, etc. etc.) con un grado de certeza importante, pero sin llegar a la necesidad de que el resultado sea 100% exacto.

 

Por ejemplo, la gerencia de una cadena de supermercados puede decidir dejar de abrir los días domingos, basándose en la cantidad de operaciones realizadas en dichos días y comparándolo con los otros días de la semana. En dicha situación, conocer el  número exacto de operaciones de venta por cada día de la semana no es una necesidad, sino que con un resultado aproximado es posible tomar una decisión.

 

En Oracle 12c, se introdujo la función de agregación aproximada APPROX_COUNT_DISTINCT que permite obtener resultados aproximados en forma mas rápida y consumiendo menos recursos.

 

El principal inconveniente de esta nueva funcionalidad radica en la necesidad de modificar el código existente para utilizar esta nueva función, como podemos ver en el siguiente ejemplo:

 

 

SELECT APPROX_COUNT_DISTINCT(manager_id) AS "Active Managers"
  FROM employees;

 

 

Nuevos parámetros de Oracle 12.2

Oracle 12c Release 2 introduce tres nuevos parámetros que permiten que las funciones de agregación "normales" (aquellas que venimos utilizando en nuestro código) realicen los cálculos por aproximación, sin necesidad de modificar las consultas existentes.

 

Los mismos son:

 

approx_for_aggregation
approx_for_count_distinct
approx_for_percentile

 

Ejemplo

Si ejecutamos una sentencia con COUNT(DISTINCT) en nuestra base de datos, sin habilitar el uso de funciones de aproximación, y consultamos el resultado y el plan de ejecución, obtenemos lo siguiente:

 

SELECT name, value
FROM   v$ses_optimizer_env
WHERE  sid = SYS_CONTEXT('USERENV','SID')
AND    name LIKE '%approx%';

NAME                                     VALUE

---------------------------------------- -------------------------

approx_for_aggregation                   false

approx_for_count_distinct                false

approx_for_percentile                    none

 

SELECT COUNT(DISTINCT id) AS data_count FROM   t2;

DATA_COUNT

----------

   1010000

 

SET AUTOTRACE TRACE EXPLAIN
SELECT COUNT(DISTINCT id) AS data_count FROM   t2;

Execution Plan

----------------------------------------------------------

Plan hash value: 4170058314

 

-----------------------------------------------------------------------------------------

| Id  | Operation            | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |          |     1 |    13 |       |  4472   (1)| 00:00:01 |

|   1 |  SORT AGGREGATE      |          |     1 |    13 |       |            |          |

|   2 |   VIEW               | VW_DAG_0 |  1010K|    12M|       |  4472   (1)| 00:00:01 |

|   3 |    HASH GROUP BY     |          |  1010K|  4931K|    11M|  4472   (1)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| T2       |  1010K|  4931K|       |  1390   (1)| 00:00:01 |

-----------------------------------------------------------------------------------------

 

Analizando el plan de ejecución, podemos ver que que la consulta generó una vista agrupando por un HASH, para poder calcular el resultado acumulado, la cual consumió 11 Mb. de espacio temporal. El costo total de la consulta es de 4.472, correspondiendo 3.082 de ellos a dicha vista temporal.

 

Al habilitar el uso de funciones de agregación aproximadas por defecto (sin necesidad de cambiar la funcion COUNT()por la nueva función APROX_COUNT_DISTINCT(), la misma consulta devuelve otro resultado y nos genera el siguiente plan de ejecución:

 

ALTER SESSION SET approx_for_aggregation = TRUE;

Session altered.

 

SELECT name, value
FROM   v$ses_optimizer_env
WHERE  sid = SYS_CONTEXT('USERENV','SID')
AND    name LIKE '%approx%';

NAME                                     VALUE

---------------------------------------- -------------------------

approx_for_aggregation                   true

approx_for_count_distinct                true

approx_for_percentile                    all

 

SELECT COUNT(DISTINCT id) AS data_count FROM   t2;

DATA_COUNT

----------

    979478

 

SET AUTOTRACE TRACE EXPLAIN
SELECT COUNT(DISTINCT id) AS data_count FROM   t2;

Execution Plan

----------------------------------------------------------

Plan hash value: 3321871023

 

------------------------------------------------------------------------------

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |      |     1 |     5 |  1390   (1)| 00:00:01 |

|   1 |  SORT AGGREGATE APPROX|      |     1 |     5 |            |          |

|   2 |   TABLE ACCESS FULL   | T2   |  1010K|  4931K|  1390   (1)| 00:00:01 |

------------------------------------------------------------------------------

 

Podemos ver que la segunda consulta devuelve un resultado (979.478) que difiere con el resultado exacto (1.010.000), con una diferencia del 3.02% respecto al resultado exacto.

 

Pero en este caso, el plan de ejecución nos muestra que Oracle no necesitó agrupar los datos en una vista, reduciendo el costo total en de 4.472 a 1.390 y sin consumir los 11 Mb. de espacio temporal.

 

En este ejemplo, se logra una reducción del 69% del costo de la consulta sacrificando un 3% de exactitud, lo cual puede ser un valor aceptable para muchos casos.

 

 

En Oracle 11gR2 se introdujo una nueva función analítica, LISTAGG, la cual ordena los datos dentro de cada grupo especificado en la cláusula ORDER BY y luego concatena los valores de la columna o expresión seleccionados.

 

Por ejemplo, si queremos obtener una lista de las tablas a las que tenemos acceso, junto con una lista de todas las columnas, ordenadas por nombre de columna, de cada una de tablas, podemos ejecutar la siguiente consulta:

 

SELECT table_name,
  LISTAGG(column_name, ',') WITHIN GROUP 
  (ORDER BY column_name) Columns
FROM all_tab_cols
GROUP BY table_name;

 

Lo cual produce el siguiente resultado:

 

2017-09-27 Listagg1.png

Pero el problema surge cuando la lista concatenada de valores supera el tamaño máximo que el lenguaje SQL soporta en Oracle, el cual es 4000 bytes. En el ejemplo, siguiente, repetimos 50 veces los datos para aumentar el tamaño del resultado de LISTAGG, obteniendo el siguiente error:

 

SELECT table_name,
  LISTAGG(column_name, ',') WITHIN GROUP 
  (ORDER BY column_name) Columns
FROM all_tab_cols
  CROSS JOIN (SELECT level FROM dual CONNECT BY level <=50)
GROUP BY table_name;

ORA-01489: result of string concatenation is too long
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size

 

Oracle 12cR2 introduce una sintaxis ampliada para la función LISTAGG, la cual nos permite obviar el error, mostrando sólo los primeros 4000 bytes que la función obtiene, sin producir un error, como podemos ver a continuación (aquí "cortamos' la cadena concatenada en dos pedazos para poder ver el principio y fin de la misma):

 

SELECT x.table_name,
  SUBSTR(LISTAGG(x.column_name, ',' ON OVERFLOW TRUNCATE) 
  WITHIN GROUP (ORDER BY x.column_name),1,30) AS "Comienza con...",
  SUBSTR(LISTAGG(x.column_name, ',' ON OVERFLOW TRUNCATE)
  WITHIN GROUP (ORDER BY x.column_name),-30) AS "Termina con..."
FROM all_tab_cols x
  CROSS JOIN (SELECT level FROM dual CONNECT BY level <=50)
GROUP BY x.table_name;

 

 

2017-09-27 Listagg ON OVERFLOW.png

 

Como vimos en el ejemplo anterior, la opción "ON OVERFLOW TRUNCATE" por defecto corta el resultado hasta el valor anterior cuyo tamaño no exceda los 4000 bytes, agrega "..." a continuación del último valor que puede ser mostrado, y luego muestra entre paréntesis la cantidad de caracteres que fueron truncados.

 

Podemos reemplazar los puntos suspensivos con cualquier cadena que deseemos utilizar como indicador para informar que el texto ha sido truncado, de la siguiente forma:

 

ON OVERFLOW TRUNCATE '///'

2017-09-27 Listagg ON OVERFLOW SPECIAL.pngTambien podemos indicarle que no deseamos saber el numero de caracteres truncados, de la siguiente forma:

 

ON OVERFLOW TRUNCATE WITHOUT COUNT

 

2017-09-27 Listagg ON OVERFLOW NOCOUNT.png

 

La sintaxis completa de la función LISTAGG en oracle 12c Release 2 puede encontrarse aqui.