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;

DBMS error logging with oracle 10g

When a oracle DML statement failed , it will rollback whole pl-sql block and come out of execution or will roll back whole statement. To out come this problem we used to write handle(Exception  handler) for each row or execute it separately. To over come this problem oracle introduced a new feature as DML error logging.Adding the appropriate LOG ERRORS clause on to most INSERT, UPDATE, MERGE and DELETE statements enables the operations to complete, regardless of errors.

Example:

1.Creating error logging table: It will define a error logging table for a specific table we mention for error logging.


DECLARE
 v_cnt NUMBER := 0;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM USER_TABLES WHERE TABLE_NAME = 'TMP_SSO_ERRLOG';
  IF v_cnt = 0 THEN
     DBMS_ERRLOG.CREATE_ERROR_LOG('ANAND_SERVICE', 'TMP_SSO_ERRLOG');
  END IF;   
END;
/



2. Error logging while inserting records into table.


BEGIN

        FOR CUR_OPTION_SET_ID IN (SELECT OPTION_SET_ID
                                    FROM ANAND_SERVICE_SET
                                   WHERE SERVICE_NAME = 'A.Plan' AND OPTION_SET_NAME = 'B.SERVICE')
        LOOP
                INSERT INTO ANAND_SERVICE(OPTION_SET_ID, OPTION_NAME, OPTION_VALUE, VALUE_ORDER)
                     VALUES (CUR_OPTION_SET_ID.OPTION_SET_ID, 'SASASAS', '0', 0)
                        LOG ERRORS INTO TMP_SSO_ERRLOG ('Record Exists') REJECT LIMIT 100;

                INSERT INTO ANAND_SERVICE(OPTION_SET_ID, OPTION_NAME, OPTION_VALUE, VALUE_ORDER)
                     VALUES (CUR_OPTION_SET_ID.OPTION_SET_ID, 'ASDAADADA', 'false', 0)
                        LOG ERRORS INTO TMP_SSO_ERRLOG ('Record Exists') REJECT LIMIT 100;

        END LOOP;
END;
/

You can read more from the link provided below.
http://www.oracle-base.com/articles/10g/dml-error-logging-10gr2.php

Oracle 11g now has a feature to silently allow (or ignore)insert SQL to accept duplicates with the ignore_row_on_dupkey_index hint.  When ignore_row_on_dupkey_index hint is used in a SQL insert on a table with a unique key index, all duplicates will be silently ignored, rather than causing the traditional error, ORA-00001 unique constraint violated.

For more details refer: http://www.dba-oracle.com/t_ignore_row_on_dupkey_index.htm