Oracle Illustrated

Sep 16 2010   2:56AM GMT

Generic DML Error logging



Posted by: Lakshmi Venkatesh
Tags:

Create couple of transaction tables and one error log table.

Create table emp1 (id number, dept_no number, salary number)

create table dept1 (id number, name varchar2(10))

Create a generic error logging table -

create table error_log
    (ora_err_number$ number,
     ora_err_mesg$ varchar2(2000),
     ora_err_rowid$ rowid,
     ora_err_optyp$ varchar2(2),
     ora_err_tag$ varchar2(2000),
     ID    NUMBER
    )
/

We know about ORA_ERR_NUMBER$, ORA_ERR_MESG$, ORA_ERR_ROWID$ (will be populated only for update / delete), ORA_ERR_OPTYP$, ORA_ERR_TAG$ – These are mandatory columns for any DML error logging table. Note, I have created a column called ID which is common to EMP1 and DEPT1 table.

Now, lets try to perform INSERT into EMP1 and DEPT1 tables. Here I am using multi table inserts just for the sake of it.

INSERT ALL
    WHEN (1 = 1) THEN
    INTO emp1 (id, dept_no, salary)
    VALUES (empid, depid, ‘a’)
         LOG ERRORS INTO error_log (‘While inserting into emp1 table’) REJECT LIMIT UNLIMITED
    WHEN (1 = 1) THEN
    INTO dept1 (id, name)
    VALUES (depid, name)
         LOG ERRORS INTO error_log (‘While inserting into dept1 table’) REJECT LIMIT UNLIMITED    SELECT object_id empid,
           rownum depid,
           object_name name,
    object_id salary FROM all_objects where rownum < 50   
/

Query ERROR_LOG table -

 

ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ ID
1722 ORA-01722: invalid number   I While inserting into emp1 table 367
1722 ORA-01722: invalid number   I While inserting into emp1 table 369
1722 ORA-01722: invalid number   I While inserting into emp1 table 370
1722 ORA-01722: invalid number   I While inserting into emp1 table 372
12899 ORA-12899: value too large for column “RFD_1FTEST”.”DEPT1″.”NAME” (actual: 20, maximum: 10)   I While inserting into dept1 table 7
12899 ORA-12899: value too large for column “RFD_1FTEST”.”DEPT1″.”NAME” (actual: 19, maximum: 10)   I While inserting into dept1 table 8
12899 ORA-12899: value too large for column “RFD_1FTEST”.”DEPT1″.”NAME” (actual: 19, maximum: 10)   I While inserting into dept1 table 9
12899 ORA-12899: value too large for column “RFD_1FTEST”.”DEPT1″.”NAME” (actual: 21, maximum: 10)   I While inserting into dept1 table 10

 

Explanation for above result set – The first 4 records Oracle error number 1722 – is due to insertion of character value in number field in EMP1 table.

The next 4 records Oracle error number 12899 is due to insertion of value larger than expected into DEPT1 table name field.

Note the ID column – that has the value of respective ID’s for the table concerned. Now, we can easily identify which table it belongs to from ORA_ERR_TAG$ table – which contains the description. In this way we can make DML error loging process generic and efficient. DML error logging is quite good when we try to perform INSERT based on SELECT from global temporary table / normal table. As there is no direct error logging feature for bulk inserts using SELECT as we have it for BULK DML operations using COLLECTIONS.

Having said all this - its quite important to know why it had caused the error in first place.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: