Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

Test applied on:

Oracle Server – Enterprise Edition – Version: 10.2.0.1.0 – 64 bit.

Goal:

This document gives a tip of how to get a log of the errors captured after executing a DML.

The tip:

CREATE TABLE t1 (col1 NUMBER PRIMARY KEY);
CREATE TABLE t2 (col1 NUMBER PRIMARY KEY);

INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
INSERT INTO t1 VALUES(3);
INSERT INTO t1 VALUES(4);
INSERT INTO t2 VALUES(1);
INSERT INTO t2 VALUES(2);
INSERT INTO t2 VALUES(3);
INSERT INTO t2 VALUES(8);
INSERT INTO t2 VALUES(9);
COMMIT;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as wissem

SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(‘t1′);

PL/SQL procedure successfully completed

SQL> insert into t1 select * from t2 log errors reject limit unlimited;

2 rows inserted

SQL> desc err$_t1;
Name Type
————— ————–
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ UROWID(4000)
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
COL1 VARCHAR2(4000)

SQL> select ORA_ERR_NUMBER$,ORA_ERR_MESG$,COL1 from err$_t1;

ORA_ERR_NUMBER$ ORA_ERR_MESG$ COL1
————— ——————————————————————————– ——————————————————————————–
1 ORA-00001: unique constraint (WISSEM.SYS_C0076732) violated 1
1 ORA-00001: unique constraint (WISSEM.SYS_C0076732) violated 2
1 ORA-00001: unique constraint (WISSEM.SYS_C0076732) violated 3

SQL>
You can easily note that 1, 2 and 3 are the values rejected because of the unique constraint.