Skip navigation

Como vimos en el artículo anterior, se puede evitar que una sentencia de DML aborte su ejecución en caso de errores mediante el uso de la cláusula LOG ERRORS.  Lo que veremos a continuación es cómo obtener un comportamiento similar mediante el uso de la opción SAVE EXCEPTIONS de FORALL.

 

Construyendo el ambiente para las pruebas

El primer paso consiste en construir un ambiente de pruebas con algunas tablas donde podamos probar las distintas formas que Oracle provee para capturar errores en una sentencia de tipo DML sin abortar la ejecución de la misma, para lo cual utilizaremos el mismo script que en el artículo anterior, obteniendo estos resultados:

 

 

 

En este artículo nos vamos a saltear la generación de los errores, como hicimos en los UPDATE del artículo anterior, viendo directamente de qué forma podemos usar BULK COLLECT y FORALL con SAVE EXCEPTIONS para detectar errores y no abortar la ejecución de la sentencia DML

 

Usando SAVE EXCEPTIONS

Esta cláusula nos permite guardar las excepciones detectadas en operaciones de DML ejecutadas con FORALL. La sintaxis es la siguiente:

 

 

Al utilizar la clausula SAVE EXCEPTIONS, Oracle guarda las mismas y podemos continuar ejecutando las operaciones de DML, accediendo posteriormente a las excepciones encontradas para mostrar o loguear las mismas.

 

Vamos a utilizar el siguiente script para actualizar la tabla tActualizame en forma similar a como lo hicimos con UPDATE ... LOG ERRORS, pero usando FORALL y SAVE EXCEPTIONS.

 

Al ejecutar el script, obtenemos el siguiente resultado:

 

 

 

Como habrán visto en el script, la sintaxis es mas complicada, ya que estamos leyendo los datos de la tabla, almacenando la version deseada en una colección y luego actualizando la tabla con los datos de la colección.

 

La ventaja de este enfoque es que el mismo permite continuar la operación cuando el error se debe a una clave duplicada, lo cual podemos comprobar con el siguiente script, el cual genera este resultado:

 

 

La desventaja de este enfoque que utiliza PL/SQL en este caso está en la performance, ya que los tiempos de ejecución promedio de las operaciones DML usando solo SQL, como vimos en el artículo anterior, se mantenían en torno a 1.2 segundos, mientras que las pruebas con BILK COLLECT y FORALL ... SAVE EXCEPTIONS dieron resultados superiores a los 3 segundos (aunque permitieron capturar errores de clave duplicada).

Manejo de Errores en operaciones de DML masivo - LOG ERRORS

 

Siguiendo una pregunta en la comunidad de Oracle (update in bulk) surgió una conversación con Jonathan Lewis sobre la frecuente recomendación de no hacer en PL/SQL lo que se puede hacer en SQL, en la cual Jonathan incluyó un artículo suyo donde demuestra un caso donde PL/SQL tiene mejor performance que SQL puro.

A raíz de la discusión, consideré interesante realizar una prueba donde poder comprobar el funcionamiento de la cláusula "LOG ERRORS" de las sentencias DML, disponibles a partir de Oracle 10g, comparada con el uso de "SAVE EXCEPTIONS" en una construcción FORALL.

 

En este primer artículo vamos a ver como funciona LOG ERRORS.

 

 

Construyendo el ambiente para las pruebas

El primer paso consiste en construir un ambiente de pruebas con algunas tablas donde podamos probar las distintas formas que Oracle provee para capturar errores en una sentencia de tipo DML sin abortar la ejecución de la misma.

 

El siguiente script permite crear la tabla con la que vamos a trabajar, al ejecutarlo obtenemos estos resultados:

 

 

 

El paso siguiente consiste en probar de actualizar todas las filas, y en algunos casos generar errores. Para ello vamos a ejecutar un segundo script, el cual contiene tres sentencias UPDATE

 

  • La primer sentencia le intenta asignar el valor 'FILA 0000001' a la descripción en todas las filas en que el ID es múltiplo de 13.000, causando un error por violación de la Unique Key.
  • La segunda sentencia le intenta asignar el valor 1000000 a OtrosDatos en todas las filas en que el ID es múltiplo de 17.000, causando un error por violación de la Check Constraint.
  • La tercer sentencia le intenta asignar el valor 11 a TipoID en todas las filas en que el ID es múltiplo de 19.000, causando un error por violación de la Foreign Key.

 

El resultado obtenido es el siguiente:

 

 

 

 

 

Como se ve en la imagen, las tres sentencias UPDATE fallaron por diversos motivos, y ninguna fila fue modificada, aunque solo 17 filas en total tenían problemas.

 

 

Logueo de errores en sentencias DML

A partir de Oracle 10g (10.2) es posible "ignorar" cuando una sentencia DML genera un error y continuar con la operación, guardando un detalle de los errores en una tabla para su posterior análisis y/o corrección.

 

La sintaxis a incluir en las sentencias DML es la siguiente:

 

LOG ERRORS [ INTO [schema.] table ] [ (simple_expression) ] [ REJECT LIMIT { integer | UNLIMITED } ]
Las secciones a tener en cuenta son:

 

  • "INTO [schema.] table": Indica en que tabla guardar los errores. Por defecto, utiliza una tabla ERR$_xxxxx, donde xxxxx son los primeros 25 caracteres del nombre de la tabla sobre la cual se ejecuta la operación. La misma puede crearse utilizando un procedimiento del paquete "DBMS_ERRLOG", tal cual veremos a continuación.
  • "simple_expression": Cualquier expresión que permita o ayude a identificar la operación actual en la tabla de errores, por ejemplo "TO_CHAR(SYSDATE)".
  • "REJECT LIMIT": Permite definir el numero máximo de errores que provocaran que la sentencia DML falle. Se puede usar la opción "UNLIMITED" para que la sentencia se ejecute ignorando todos los errores.
Esta cláusula tiene algunas limitaciones que generaran que la sentencia de DML falle pese a incluir la cláusula , las cuales son:

 

  • Constraints (Restricciones) de tipo diferidas.
  • Cualquier operación INSERT o MERGE de Direct Path que genere una violación de clave única o indice único.
  • Cualquier operación de actualización UPDATE o MERGE que genere violación de clave única o indice único.

 

Creación de la tabla de Log de Errores

Como mencionamos al explicar la sintaxis, es necesario que la información sobre los errores generados al ejecutar una sentencia DML con la opción LOG ERRORS se guarden en una tabla. Para ello, podemos usar el procedimiento DBMS_ERRLOG.create_error_log el cual crea una tabla de log para una tabla dada.

 

En nuestro caso, lo tenemos que ejecutar de la siguiente forma (script):

 

BEGIN 
     DBMS_ERRLOG.create_error_log (dml_table_name => 'tActualizame');
END;
/
Esta sentencia crea una tabla llamada "ERR$_tActualizame", la cual posee las columnas de la tabla original mas las siguientes columnas:

 

  • ORA_ERR_NUMBER$: Número de Error
  • ORA_ERR_MESG$: Mensaje de error
  • ORA_ERR_ROWID$: ROWID de la fila que provocó el error (en caso de UPDATE o DELETE)
  • ORA_ERR_OPTYP$: Tipo de Operación (U = Update, etc.)
  • ORA_ERR_TAG$: Etiqueta asignada a la operación en la cláusula LOG ERRORS

Probando un UPDATE con distintos errores - Prueba 1

A continuación, vamos a ejecutar un script que contiene una sentencia UPDATE que combina los tres tipos de errores que probamos anteriormente, pero incluyendo una cláusula LOG ERRORS, y luego vamos a consultar el contenido de la tabla "ERR$_tActualizame".

 

El resultado de ejecutar el mismo es el siguiente:

 

 


Lo primero que llama la atención es que la sentencia terminó con un error (ORA-00001) pero igualmente generó información en la tabla de log. El motivo por el cual la sentencia terminó con error esta explicado anteriormente, cuando vimos las limitaciones de la cláusula LOG ERRORS:

 

"Cualquier operación de actualización UPDATE o MERGE que genere violación de clave única o indice único."

 

Este comportamiento es el esperado, ya que Oracle no puede comprobar la violación de la restricción antes de aplicar el cambio (ya que depende de la existencia de otras filas a las cuales puede no haber accedido), a diferencia de los casos de Check Constraint o Foreign Key, donde toda la información requerida para validar si la fila cumple o no las condiciones esta disponible en la misma fila.

 

 

Probando un UPDATE con distintos errores - Prueba 2

Por lo que tenemos que probar solo con los errores de Check Constraint o Foreign Key, tal cual lo haremos en el siguiente script, obteniendo estos resultados:

 


Como vemos en la imagen, la sentencia terminó de forma correcta, actualizando 99.990 filas de la tabla, y los diez errores quedaron registrados en la tabla "ERR$_tActualizame".

 

Performance

Después de realizar varias pruebas, concluimos que el tiempo promedio requerido (en Oracle Live SQL) para ejecutar el UPDATE fue de 1.12 segundos. Veremos en el próximo artículo cuanto requiere el uso de la cláusula SAVE EXCEPTIONS en PL/SQL.