Multiple constraints in the table: How to get all the violations?

I have a table in Oracle with several constraints. When I insert a new record and not all constraints are valid, then Oracle raise only the "first" error. How to get all violations of my record?

CREATE TABLE A_TABLE_TEST (
  COL_1 NUMBER NOT NULL,
  COL_2 NUMBER NOT NULL,
  COL_3 NUMBER NOT NULL,
  COL_4 NUMBER NOT NULL
);

INSERT INTO A_TABLE_TEST values (1,null,null,2);

ORA-01400: cannot insert NULL into ("USER_4_8483C"."A_TABLE_TEST"."COL_2")

I would like to get something like this:

Column COL_2: cannot insert NULL
Column COL_3: cannot insert NULL

This would be also sufficient:
Column COL_2: not valid
Column COL_3: not valid

Of course I could write a trigger and check each column individually, but I like to prefer constraints rather than triggers, they are easier to maintain and don't require manually written code.

Any idea?


There no straightforward way to report all possible constraint violations. Because when Oracle stumble on first violation of a constraint, no further evaluation is possible, statement fails, unless that constraint is deferred one or the log errors clause has been included in the DML statement. But it should be noted that log errors clause won't be able to catch all possible constraint violations, just records first one.

As one of the possible ways is to:

  1. create exceptions table. It can be done by executing ora_home/rdbms/admin/utlexpt.sql script. The table's structure is pretty simple;
  2. disable all table constraints;
  3. execute DMLs;
  4. enable all constraints with exceptions into <<exception table name>> clause. If you executed utlexpt.sql script, the name of the table exceptions are going to be stored would be exceptions.

Test table:

create table t1(
  col1 number not null,
  col2 number not null,
  col3 number not null,
  col4 number not null
);

Try to execute an insert statement:

insert into t1(col1, col2, col3, col4)
  values(1, null, 2, null);

Error report -
SQL Error: ORA-01400: cannot insert NULL into ("HR"."T1"."COL2")

Disable all table's constraints:

alter table T1 disable constraint SYS_C009951;
alter table T1 disable constraint SYS_C009950;
alter table T1 disable constraint SYS_C009953;
alter table T1 disable constraint SYS_C009952;

Try to execute the previously failed insert statement again:

insert into t1(col1, col2, col3, col4)
  values(1, null, 2, null);

1 rows inserted.

commit;

Now, enable table's constraints and store exceptions, if there are any, in the exceptions table:

alter table T1 enable constraint SYS_C009951 exceptions into exceptions;
alter table T1 enable constraint SYS_C009950 exceptions into exceptions;
alter table T1 enable constraint SYS_C009953 exceptions into exceptions;
alter table T1 enable constraint SYS_C009952 exceptions into exceptions;

Check the exceptions table:

column row_id     format a30;
column owner      format a7;
column table_name format a10;
column constraint format a12;

select *
  from exceptions 

ROW_ID                         OWNER   TABLE_NAME CONSTRAINT
------------------------------ ------- -------    ------------
AAAWmUAAJAAAF6WAAA             HR      T1         SYS_C009951
AAAWmUAAJAAAF6WAAA             HR      T1         SYS_C009953

Two constraints have been violated. To find out column names, simply refer to user_cons_columns data dictionary view:

column table_name   format a10;
column column_name  format a7;
column row_id       format a20;

select e.table_name
     , t.COLUMN_NAME
     , e.ROW_ID
  from user_cons_columns t
  join exceptions e
    on (e.constraint = t.constraint_name)

TABLE_NAME COLUMN_NAME ROW_ID
---------- ----------  --------------------
T1         COL2        AAAWmUAAJAAAF6WAAA
T1         COL4        AAAWmUAAJAAAF6WAAA

The above query gives us column names, and rowids of problematic records. Having rowids at hand, there should be no problem to find those records that cause constraint violation, fix them, and re-enable constraints once again.

Here is the script that has been used to generate alter table statements for enabling and disabling constraints:

column cons_disable format a50
column cons_enable format a72

select 'alter table ' || t.table_name || ' disable constraint '||
        t.constraint_name || ';' as cons_disable
     , 'alter table ' || t.table_name || ' enable constraint '||
        t.constraint_name || ' exceptions into exceptions;' as cons_enable
  from user_constraints t
where t.table_name = 'T1'
order by t.constraint_type