Es una nueva funcionalidad, a partir de Oracle 10gR2, que nos permite ejecutar una sentencia DML completa y si en el transcurso de la ejecución, algún registro diera error, se insertará en una tabla de error para, posteriormente, poder corregirlo y volver a insertarlo, continuando con los siguientes registros.
Así no tendremos que ir controlando por registro o por bloque de registros si se ha producido un error para realizar el commit.
Lo primero de todo. Ver si lo tenemos instalado:
SELECT OBJECT_NAME FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE ‘%DBMS_%’
AND OBJECT_TYPE = ‘PACKAGE’ ORDER BY 1;
Si creamos una tabla con un campo varchar de 1 e intentamos insertar en ella niveles del 1 al 10, el último registro nos dará error:
create table PRUEBA (nombre varchar2(1));
Insert into prueba select level from dual CONNECT BY level <= 10;
Esta sentencia nos dará una especie de for del 1 al 10.
ERROR at line 1:
ORA-12899: value too large for column “MANTE90″.”PRUEBA”.”NOMBRE” (actual: 2,
maximum: 1).
Y como vemos no se habrá creado ningún registro en la tabla.
SQL> select * from prueba;
no rows selected.
Ahora crearemos la tabla de errores:
EXEC dbms_errlog.create_error_log(‘PRUEBA’, ‘ERROR_PRUEBA’);
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)
NOMBRE VARCHAR2(4000)
Cómo veis genera una columna por cada columna de la tabla a la que se asocia.
Bien, ahora vamos a insertar los registros otra vez.
INSERT INTO PRUEBA
SELECT level FROM dual
CONNECT BY level <= 10LOG ERRORS INTO ERROR_PRUEBA REJECT LIMIT UNLIMITED;
Con esto insertaremos en la tabla PRUEBA 9 registros correctos y uno en la de error, ya que el tamaño superará lo establecido en la columna.
No es necesario introducir INTO…, se insertarán en la tabla asignada por defecto cuando creamos la tabla de error asociada.
La última parte de la sentencia es que inserta en la tabla de errores cuando la sentencia genera un error.
Si ponemos REJECT LIMIT UNLIMITED Oracle insertará todo los registros erróneos en la tabla y continuará hasta que termine.
Podemos limitar cuanto queramos 10,100…
SQL>set lines 110
SQL> col num$ for 9999999
SQL> col ora_err_mesg$ for a50
SQL> col ora_err_rowid$ for a25
SQL> col nombre for a5
SQL> select ora_err_number$ num$,
ora_err_mesg$, ora_err_rowid$, nombre from ERROR_PRUEBA
SQL> /
NUM$ ORA_ERR_MESG$ ORA_ERR_ROWID$ NOMBRE
———- —————————————- ————————- —
12899 ORA-12899: value too large for column “???”.” 10
PRUEBA”.”NOMBRE” (actual: 2, maximum: 1)
| NOMBRE | DESCRIPCION |
| ORA_ERR_NUMBER$ | Error de Oracle |
| ORA_ERR_MESG$ | Mensaje de Error |
| ORA_ERR_ROWID$ | ROWID. En el caso del insert estará vacía, pero puede resultar de utilidad si es un update, merge o un delete |
| ORA_ERR_OPTYP$ | El tipo de operación realizada I=INSERT, U=UPDATE, D=DELETE |
| ORA_ERR_TAG$ | Etiqueta que podemos rellenar con información |
Podemos controlar el número de registro insertado en el campo en el campo ORA_ERR_TAG$, ya que al ser un Insert no tenemos el rowid.
FOR i in 1..100
LoopINSERT …
LOG ERRORS INTO ERROR_PRUEBA (‘Registro: ‘ || to_char(i))
REJECT LIMIT 1;
End loop;
Cuando hemos realizado la inserción decidiremos si realizar un commit o un rollback.
Si ejecutamos un rollback, los registros de la tabla de error no se perderán, hasta que no realicemos un trúncate de esa tabla.
Estas son los errores que podrán almacenarse en este tipo de tabla, los demás generarán un fallo y pararan la ejecución.
– Value too large for column.
– Constraint violations(NOT NULL, unique, referential, and check constraints).
– Errores durante la ejecución de un trigger
– Partition mapping errors
– Type conversion errors arising from type conversion between a column in a subquery and the corresponding column of the table
– A specific MERGE operation error (ORA-30926: Unable to get a stable set of rows).
Pero hay que tener en cuenta que:
– No se puede utilizar con LONG, LOB o columnas de tipo objeto.
– Violated deferred constraints
– Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.
– Any UPDATE or MERGE operation that raises a unique constraint or index violation.
– Violation of a constraint on a LONG, LOB, or object type column
Más información sobre este paquete:
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_errlog.htm#ARPLS680