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