DML LOG ERROR

Saludos,

A muchos nos ha pasado que estamos realizando un mega insert de registros desde un select y de pronto nos falla por error constraint lo que lleva a la fastidiosa tarea de buscar cual es el registro que esta causando que el insert no finalice.

Pensando en estos tediosos casos, Oracle introduce la claúsula DML LOG ERROR que es una caracteristica introducida desde la versión 10g R2, esto nos permite registrar los errores que se prenseten por inconsistencia, validaciones de integridad durante las operaciones IMSERT, DELETE, UPDATE Y MERGE.

ClaúsulaLOG ERRORS [INTO [esquema.]tabla] [('INSERT|UPDATE|DELETE')] [REJECT LIMIT integer|UNLIMITED]
[INTO [esquema.]tabla] : Esta es opcional e indica a que tabla log se enviará los registros que no se pudieron procesar en la sentencia, por defecto lo enviará a la tabla log asociada a la tabla.
[REJECT LIMIT integer|UNLIMITED]: Para definir cual es la cantidad de errores que podrá soportar antes de generar un error, si se indica UNLIMITED, no importará la cantidad de errores que se almacenen el en la tabla Log.

Ejemplo
Para entenderlo mejor pongamos un ejemplo rutinario, tenemos una tabla ORIGEN con 100,000 registros y necesitamos traspasar a otra tabla DESTINO, lo que se nos viene a la cabeza es INSERT SELECT, pero al realizar esta operación nos arroja un error por que uno de los registro presenta una inconsistencia de validación de datos no soportada por la tabla DESTINO. Por un condenado registro se nos agrió el día ya que debemos buscar cual de los 10 millones de registros es el condenado que no nos deja finalizar la transacción.

--INSERT
SQL> --Creamos la tabla que contendra los datos
SQL> create table origen pctfree 0 as
  2  select rownum codigo, decode(rownum,90000,'C',90001,'B','A')estado
  3  from dual
  4  connect by level <=100000;
Tabla creada.
SQL>
SQL> select count(*) registros from origen;
 REGISTROS
----------
    100000
SQL>
SQL> --Creamos la tabla a la que se enviara los datos
SQL> create table destino
  2  (
  3      codigo_des number,
  4      estado_des varchar2(1)
  5  );
Tabla creada.
SQL>
SQL> --Creamos una restriccion de integridad CHECK
SQL> alter table destino add ( constraint chk_destino_estado
  2                              check(estado_des in ('A','B','X')));
Tabla modificada.
SQL>
SQL> --Insertamos los datos
SQL> insert into destino(codigo_des, estado_des) select codigo, estado from orig
insert into destino(codigo_des, estado_des) select codigo, estado from origen
*
ERROR en lÝnea 1:
ORA-02290: restricci¾n de control (CONTROL.CHK_DESTINO_ESTADO) violada

SQL>
SQL> ------------------------------------------------------------------
SQL> --Creamos la tabla log de errores, err_log_table_name es opcional si no lo
         indicamos creara la tabla log concatenando 'ERR$_' mas los primeros 25
         caracteres del nombre de la tabla pasado en el parametrodml_table_name
SQL> BEGIN
  2    DBMS_ERRLOG.create_error_log (dml_table_name => 'destino',
  3                                  err_log_table_name=>'err$_destino');
  4  END;
  5  /
Procedimiento PL/SQL terminado correctamente.

SQL>
SQL> --Probamos de nuevo la sentencia pero con la clausula LOG ERROR
SQL> insert into destino(codigo_des, estado_des)
  2  select codigo, estado from origen
  3  log errors into err$_destino  ('INSERT') reject limit unlimited;
99999 filas creadas.
SQL>
SQL> commit;
Confirmación terminada.

SQL> select * from err$_destino;

Como vemos, el insert se realizó solo de los registros que no generaron errores, los que presentaron errores se envian a la tabla ERR$_DESTINO.

UPDATE
SQL> --Borramos la tabla log
SQL> delete err$_destino;

2 filas suprimidas.
SQL>
SQL> --Contamos los datos
SQL> select count(*) cantidad from destino;
  CANTIDAD
----------
     99999
SQL>
SQL> --Ejecutamos el update
SQL> update destino
  2  set estado_des=decode(estado_des,'A','X','B','Z');
update destino
*
ERROR en lÝnea 1:
ORA-02290: restricci¾n de control (CONTROL.CHK_DESTINO_ESTADO) violada

SQL>--Ya no es necesario crear la tabla LOG con el procedimiento ya que en el
             ejemplo anterio del INSERT ya lo creamos
SQL> --Update con LOG ERROR
SQL> update destino
  2  set estado_des=decode(estado_des,'A','X','B','Z')
  3  log errors into err$_destino  ('UPDATE') reject limit unlimited;
99998 filas actualizadas.
SQL>
SQL> --Consultamos los errores
SQL> select * from err$_destino;

Conclusión
Esta es una gran utilidad para gestionar de manera automatico el registro de errores que se producen al momento de realizar operaciones DML en operaciones masivas o individuales. Puede ser implemtado tambien en procedimiento PL/SQL .