Error logging for constraints violations

While inserting records in the tables we get constraint violations and it is difficult to identify what are rows are  violating constraints. We can do it by writing queries  by grouping based on key columns but again if we do not want our pl-sql code block to direct all bad records and display them then exception logging into exception table will be very helpful.

You can create exception logging table as

CREATE TABLE CONSCREATIONERRORS
(
  ROW_ID UROWID,
  OWNER VARCHAR2(30),
  TABLE_NAME VARCHAR2(30),
  CONSTRAINT VARCHAR2(30)
);

Alternatively  oracle provided script to create default exception logging table. You ca find the script as...

D:\oracle\product\11.2.0.2\dbhome_1\RDBMS\ADMIN\utlexcpt.sql


If constraint is not exists you can create constraint in disabled state and enable it as given below to log bad records in exception logging table.


delete from CONSCREATIONERRORS;
commit;

DECLARE
  v_stmt VARCHAR2(4000) := ' ';
BEGIN
  FOR x IN (
    select table_name, constraint_name from user_constraints where constraint_type IN ('C', 'R', 'U') AND constraint_name like '%ABC%'
  ) LOOP
    v_stmt := 'ALTER TABLE ' || x.table_name || ' ENABLE CONSTRAINT ' || x.constraint_name || ' EXCEPTIONS INTO CONSCREATIONERRORS';
    BEGIN
      EXECUTE IMMEDIATE (v_stmt);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/


Now you can display bad records present in the exception logging table in a presentable way.

ex:


spool show_badrecords.sql

begin
  dbms_output.put_line('set echo on');
  dbms_output.put_line('set feedback on');
  dbms_output.put_line('set heading on');
  dbms_output.put_line('set pagesize 30000');
  dbms_output.put_line('set linesize 300');
  dbms_output.put_line('set trimspool on');
  dbms_output.put_line('set serveroutput on size unlimited');
  dbms_output.put_line('spool show_badrecords.log append');
end;
/

DECLARE
 tablename varchar2(30);
 constraintname varchar2(50);

 PROCEDURE selectdata(tablename IN varchar2, constraintname IN varchar2)
 IS
   selstmt varchar2(300);
   loop_cnt number := 0;
   keycols varchar2(300) := ' ';
   colname varchar2(50);
   pkeycols varchar2(300) := ' ';
   pkcol varchar2(50);
   pktab varchar2(50);
   pkcons varchar(50);
 BEGIN
   dbms_output.put_line('-- For Table '||tablename||', constraint named '||constraintname||' failed...');
   FOR j IN (
     select column_name from user_cons_columns where table_name=tablename
                                    and constraint_name = constraintname
               order by position
   ) LOOP
     colname := j.column_name;
     if loop_cnt = 0 then
       keycols := colname;
       loop_cnt := loop_cnt + 1;
     else
       keycols := keycols||', '||colname;
     end if;      
   END LOOP;

   selstmt := 'select distinct '||keycols||' from '||tablename||', ConsCreationErrors s where '
              ||tablename||'.rowid = s.row_id and s.table_name = '''||tablename||''''
              ||' and s.constraint = '''||constraintname||''';';
   dbms_output.put_line(selstmt);

   -- Get parent table and columns being referenced, and log this diagnostic information
  loop_cnt := 0;
  FOR k IN (
  select constraint_name, column_name, table_name
      from user_cons_columns
      where constraint_name in (select r_constraint_name from user_constraints where
      constraint_name = constraintname and table_name = tablename)
   order by position
  ) LOOP
  pkcons := k.constraint_name;
  pktab := k.table_name;
  pkcol := k.column_name;
  if loop_cnt = 0 then
       pkeycols := pkcol;
       loop_cnt := loop_cnt + 1;
     else
       pkeycols := pkeycols||', '||pkcol;
     end if;

  END LOOP;

  dbms_output.put_line
  ('-- Referenced PK constraint for constraint that failed is '||pkcons||': Data is missing from '||pktab||' table for key columns = '||pkeycols);
  dbms_output.put_line('-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< -- ');
 END selectdata;

BEGIN
  dbms_output.put_line('-- These are the tables with inconsistent data...');
  dbms_output.put_line('select count(*), table_name from ConsCreationErrors group by table_name;');
  dbms_output.put_line('-- These are the constraint creations that failed because of inconsistent data...');
  dbms_output.put_line('select count(*), constraint from ConsCreationErrors group by constraint;');

  FOR i IN (
    select distinct table_name,constraint from ConsCreationErrors
        order by table_name ) LOOP
      tablename := i.table_name;
      constraintname := i.constraint;
      selectdata(tablename, constraintname);

  END LOOP;
END;
/

spool off

-- Execute select statements showing bad rows and spool results to log file
start show_badrecords.sql

exit;