Please visit www.oracle-class.com for Videos, Free posts, Books, Webinar and Free forum … ALL about Oracle!

Tip ; DML error capture

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.1.0 – 64 bit.
Information in this document applies to any platform.

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.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: