René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Exception clause [Oracle SQL clause]

exceptions into exception-table
The exception clause appears as part of the constraint state part in the constraint clause. In addition, it seems (but I am not 100% sure) that the exception-clause can only appear as part of an alter table statement.
The exception-table can be created with either the utlexpt1.sql or the utlexcpt.sql script: 
@?\rdbms\admin\utlexpt1
@?\rdbms\admin\utlexcpt.sql
The former creates a table that has physical rowids while the latter creates a table that has universal rowids. Universal rowids are necessary for index organized tables.
The name of the created table is exceptions and it looks like: 
Name                                  Null?    Type
------------------------------------- -------- --------------
ROW_ID                                         ROWID
OWNER                                          VARCHAR2(30)
TABLE_NAME                                     VARCHAR2(30)
CONSTRAINT                                     VARCHAR2(30)

Example

create table failed_check_tab (
  id    number,
  data  varchar2(10)
);

insert into failed_check_tab values (1, 'Moo');
insert into failed_check_tab values (2, 'foo');
insert into failed_check_tab values (3, 'Bar');
insert into failed_check_tab values (4, 'Bar Foo');
insert into failed_check_tab values (5, 'moo Bar');

alter table failed_check_tab 
  add check(data = initcap(data)) 
  exceptions into exceptions;

ERROR at line 1:
ORA-02293: cannot validate (RENE.SYS_C0011043) - check constraint violated

select data from failed_check_tab 
 where rowid in (select row_id from exceptions 
                   where table_name = 'FAILED_CHECK_TAB' and 
                              owner = user;

DATA
----------
foo
moo Bar