How to generate a list of exceptions generated due to constraint violations?

The following shows how exceptions can be captured.

— Create exceptions table
SQL> @?/rdbms/admin/utlexcpt.sql
Table created.

SQL> desc exceptions;
Name Null? Type
—————————————– ——– ————————
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)

SQL> select * from xx;
X
———-
11
11

— add constraint but disable it
SQL> alter table xx add constraint xx_pk primary key (x) disable;
Table altered.

— enable constraint and generate any exceptions to exceptions table
SQL> alter table xx enable validate constraint xx_pk exceptions into exceptions;
alter table xx enable validate constraint xx_pk exceptions into exceptions
*
ERROR at line 1:
ORA-02437: cannot validate (SYSTEM.XX_PK) – primary key violated

— List exceptions
SQL> select * from exceptions;
ROW_ID OWNER TABLE_NAME
—————— —————————— ——————————
CONSTRAINT
——————————
AAAErDAABAAAHy5AAB SYSTEM XX
XX_PK
AAAErDAABAAAHy5AAA SYSTEM XX
XX_PK

SQL> select * from xx where rowid IN ( <AAAErDAABAAAHy5AAB>, <AAAErDAABAAAHy5AAA> );

X
———-
11
11

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