DML Error Logging

Alguna vez trataron de actualizar 10 millones de registros? Que sucedía cuando uno de los registros fallaba? Oracle realizaba un rollback automático de los cambios... y qué sucede si el registro que falló era uno de los últimos en actualizarse? Y bueno... seguramente desperdiciamos todos el tiempo de ejecución de ese proceso ya que los cambios se perdieron tan solo porque un registro falló! A muchos les habrá pasado lo mismo al ejecutar una sentencia del tipo INSERT AS SELECT cierto? En donde uno de los registros no pudo insertarse por X motivo y por consecuencia toda la sentencia falló!

Generalmente, sabemos que la manera más rápida de realizar un DML es en una sola sentencia. Cuando tenemos una sentencia en donde pueden haber registros que terminen en error, se solía armar un procedimiento que recorra los datos que se quieren insertar, updatear, eliminar, etc. e ir ejecutando la senetencia de a un/o varios registros a la vez, y los registros que fallaban, se insertaban en una tabla de errores. Este procedimiento suele ser muy lento, ya que no estamos realizando un DML en una sola sentencia y en una sola vez para todos los registros; sino que estamos recorriendo los datos proceduralmente y realizando el DML de a X cantidad de registros a la vez con el fin de loguear los registros que terminaron en error e ir comiteando, a medida que se va ejecutando la sentencia DML, los registros que terminan satisfactoriamente.

En Oracle 10g Release 2, existe una nueva funcionalidad llamada "DML Error Logging" que nos permite ejecutar una sentencia DML de "principio a fin"... y si en el transcurso de ejecución de esa sentencia, uno o más registros fallaran, esos registros se loguean en una tabla de errores para que luego podramos corregirlos, sin necesidad, de volver a insertar todos los registros nuevamente ya que sólo tendremos que volver a insertar los que terminaron en error.
Por ejemplo: Si intentamos insertar 1 millón de registros, y sólo un registro falla, se insertarán en la tabla final 999,999 de registros, y el registro que terminó en error, se loguea en otra tabla para que luego podamos corregirlo y volverlo a insertar. Acaso no es sensacional ésto???

Veamos un ejemplo muy simple:

Creamos una tabla llamada TEST, en donde vamos a insertar 1 millón de registros.

SQL_10gR2> CREATE TABLE test
2  (
3  ID     NUMBER,
4  NOMBRE VARCHAR2(7)
5  );

Tabla creada.

Creamos una tabla llamada ERROR_LOG_TEST que apunta a la tabla TEST. Esta tabla va a contener todos los registros que terminen en error cuando queramos realizar un DML en la tabla TEST.

SQL_10gR2> EXEC dbms_errlog.create_error_log('TEST','ERROR_LOG_TEST') ;

Procedimiento PL/SQL terminado correctamente.

SQL_10gR2> DESC error_log_test

Nombre                                                Nulo?    Tipo
----------------------------------------------------- -------- ------------------------------------
ORA_ERR_NUMBER$                                                NUMBER
ORA_ERR_MESG$                                                  VARCHAR2(2000)
ORA_ERR_ROWID$                                                 ROWID
ORA_ERR_OPTYP$                                                 VARCHAR2(2)
ORA_ERR_TAG$                                                   VARCHAR2(2000)
ID                                                             VARCHAR2(4000)
NOMBRE                                                         VARCHAR2(4000)

Bien, ahora vamos a insertar los registros en la tabla TEST.

SQL_10gR2> INSERT INTO test
2  SELECT level, 'nom_'||level
3  FROM dual
4  CONNECT BY level <= 1000000   
5  LOG ERRORS INTO ERROR_LOG_TEST REJECT LIMIT UNLIMITED;  

999 filas creadas.

WAW! De 1 millón de registros, sólo 999 registros fueron insertados satisfactoriamente, el resto de los registros se insertaron en la tabla de logueo de errores.

Como notarán, use la cláusula LOG ERRORS INTO ... REJECT LIMIT UNLIMITED. Esta cláusula nos permite decirle a Oracle, que queremos utilizar "DML Error Logging" para nuestra sentencia. Fijense que coloqué UNLIMITED como parámetro de REJECT LIMIT. Esto le dice a Oracle, que no se fije en la cantidad de registros que terminan en error, que simplemente me inserte todos esos registros en la tabla de logueo de errores y que continúe con la ejecución de la sentencia hasta que termine. En vez de UNLIMITED, podría haber puesto 100, 1000, etc... que denota el máximo número de registros que quiero que Oracle loguee. Si hay más registros que terminan en error, Oracle simplemente aborta la ejecución de la consulta y devuelve un error por pantalla.

SQL_10gR2> SELECT count(*) FROM error_log_test;

COUNT(*)
----------
999001

1 fila seleccionada.

Como verán, no perdimos ningún registros. Los registros que se insertaron satisfactoriamente están en la tabla final TEST y el resto en la tabla de logueo de errores. Tener los registros en la tabla de logueo, nos permite corregirlos y tratar de insertarlos nuevamente en la tabla TEST.

Veamos porqué falló la inserción de la mayoría de los registros...

SQL_10gR2> SELECT count(*), ora_err_mesg$ FROM error_log_test GROUP BY ora_err_mesg$;

COUNT(*) ORA_ERR_MESG$
---------- ----------------------------------------------------------------------------------------------------
90000 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 9, máximo: 7)
 9000 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 8, máximo: 7)
    1 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 11, máximo: 7)
900000 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 10, máximo: 7)

4 filas seleccionadas.

Como podemos ver, los registros fallaron porque el campo NOMBRE tiene un tamaño de 7 caracteres, y nosotros estamos intentando insertar valores más grandes. Solucionar éste problema es muy sencillo, simplemente, tenemos que agrandar el campo NOMBRE.

SQL_10gR2> ALTER TABLE test MODIFY nombre VARCHAR2(100);

Tabla modificada.

Ahora vamos a tratar de insertar nuevamente los registros en la tabla TEST desde la tabla de logueo de errores.

SQL_10gR2> INSERT INTO test
2  SELECT id, nombre
3  FROM error_log_test;

999001 filas creadas.

SQL_10gR2> SELECT count(*) FROM test;

COUNT(*)
----------
1000000

1 fila seleccionada.

Excelenete!!! Ya tenemos todos los registros en la tabla TEST! Ahora sólo resta truncar la tabla de logueo de errores o simplemente borrarla en caso de que no la vayamos a utilizar nunca más.

SQL_10gR2> TRUNCATE TABLE error_log_test;

Tabla truncada.

CONCLUSIÓN:

Esta nueva funcionalidad está acotada para ser utilizada sólo para algunos casos en particulares, pero espero que en lo posible, todos puedan comenzar a hacer uso de ésta funcionalidad ya que es muy simple de utilizar y muy eficiente a la hora de realizar carga masiva de datos y de logueo de errores de manera simultánea en una sola sentencia DML sin necesidad de recurrir a procedimientos costosos en cuanto a performance, desarrollo, mantenimiento y debuging.