DBMS ERRLOG

From Robs_Wiki
Jump to: navigation, search

Introduction

  • By default, when a DML statement fails the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected.
  • In Oracle 10g Database Release 2, the DML error logging feature has been introduced to solve this problem. Adding the appropriate LOG ERRORS clause on to most INSERT, UPDATE, MERGE and DELETE statements enables the operations to complete, regardless of errors.
  • Any errors are logged inside the error log table.
  • You can optionally includes a tag (anumeric or string literal in parentheses) that is added to the error log to help identify the statement that caused the errors.If the tag is omitted, a NULL value is used. The value is inserted in column ORA_ERR_TAG$

Step 1 Create the Error Log table

We have the following table:

SQL> desc robla.tick_marks
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- ----------------------------------------
 TICK_ID                                                                  NOT NULL NUMBER
 CAND_ID                                                                  NOT NULL NUMBER
 COMMENTS                                                                          VARCHAR2(200 CHAR)

Now we create an error log for this table:

SQL> BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'TICK_MARKS');
END;
/

PL/SQL procedure successfully completed.

This creates the following table:

SQL> desc robla.err$_tick_marks
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- ---------------------------------------
 ORA_ERR_NUMBER$                                                                   NUMBER
 ORA_ERR_MESG$                                                                     VARCHAR2(2000 CHAR)
 ORA_ERR_ROWID$                                                                    ROWID
 ORA_ERR_OPTYP$                                                                    VARCHAR2(2 CHAR)
 ORA_ERR_TAG$                                                                      VARCHAR2(2000 CHAR)
 TICK_ID                                                                           VARCHAR2(4000 CHAR)
 CAND_ID                                                                           VARCHAR2(4000 CHAR)
 COMMENTS                                                                          VARCHAR2(4000 CHAR)

Use the Error Log inside a DML statement

INSERT INTO TICK_MARKS
(SELECT TICK_ID, cand_id, COMMENTS  FROM TICK_MARKS_TEMP where tick_id = c_tick_id)
LOG ERRORS INTO ERR$_TICK_MARKS('ROB_ERROR')
REJECT LIMIT UNLIMITED;