DML Error Logging

1. DML Error Logging David ScottIntec Billing Georgia Oracle Users’ Group president@gouser.org

2. Good Things for Developers • Write less code • Write better code

3. Objectives • Introduce DML Error Logging • Explain the error logging clause syntax and features. • Identify uses of error logging in data warehousing and transactional systems. • Show how to use the DBMS_ERRLOG package to create error logging tables.

4. Typical Problems • “Throw out the baby with the bath water” • Causes • Datatype mismatch • Constraint violations • NO_DATA_FOUND • … and so many others! • Data must be ‘perfect’ before it is processed.

5. Traditional Methods • Attempt / Rollback • Complimentary Sets • Row-by-Row (aka “slow-by-slow”) • BULK COLLECT, FORALL, SAVE EXCEPTIONS

6. What is DML Error Logging? • 10gR2 extension to the INSERT, UPDATE, DELETE, and MERGE command • Places ‘bad’ data into an existing error table • Logs error information • Error Number (ORA-xxxx) • Error Message • ROWID • Operation Type • Supplied Identifier

7. ERRLOG vs. Original Table SQL> desc t Name Null? Type ----------------------------------------- -------- ---------------- PK NUMBER DT DATE VC VARCHAR2(10) SQL> desc ERR$_T Name Null? Type ----------------------------------------- -------- ---------------- ORA_ERR_NUMBER$ NUMBER ORA_ERR_MESG$ VARCHAR2(2000) ORA_ERR_ROWID$ ROWID ORA_ERR_OPTYP$ VARCHAR2(2) ORA_ERR_TAG$ VARCHAR2(2000) PK VARCHAR2(4000) DT VARCHAR2(4000) VC VARCHAR2(4000)

8. LOG ERRORS Syntax

9. LOG ERRORS Syntax • INTO [schema.] table • Default = 'ERR$_' || substr(table_name,1,25) • (simple expression) • character expression in VARCHAR2(2000) • (to_char(sysdate,’MM/DD/YY HH24:MI:SS’)) • REJECT LIMIT • Default = 0 (turns it off!) • integer • UNLIMITED

10. Interesting Behavior • If statement DML fails (and thus rolls back), the LOG INTO still completes! • Useful approach (cf. Roshak) • Execute DML statement. • Check error logging table for errors. • If no errors found in the error logging table, commit. Else, roll back. • You can now pass information on exactly which rows failed to your application or debug log.

11. When it works! • Column values are too large • Constraint violations • Errors raised during trigger execution • Type conversion errors comparing subquery to table • Partition mapping errors

12. When it fails… • Violated deferred constraints • Out of space errors (use resumable space allocation) • Unique constraint or index violations during direct-path INSERT or MERGE • Unique constraint or index violations during UPDATE or MERGE

13. Uses • Batch • Data loads • Dimension and summary generation • OLTP • Reference data maintenance • Online manipulation of ‘child’ data • In short, anywhere data is maintained on more than a row-by-row basis

14. For those with CTD… • Is it faster? • Yes, because it allows for use of more efficient techniques. • But… • The code itself is not a FAST=TRUE panacea. • Read Mark Rittman’s article!

15. Using DBMS_ERRLOG DBMS_ERRLOG.CREATE_ERROR_LOG (dml_table_name IN VARCHAR2,err_log_table_name IN VARCHAR2 := NULL,err_log_table_owner IN VARCHAR2 := NULL,err_log_table_space IN VARCHAR2 := NULL,skip_unsupported IN BOOLEAN := FALSE); exec dbms_errlog.create_error_log(‘MY_TABLE’);

16. CREATE_ERROR_LOG • dml_table_name • err_log_table_name • Default = 'ERR$_' || substr(table_name,1,25) • err_log_table_owner • err_log_table_space • skip_unsupported • TRUE skips unsupported datatypes when table is created • FALSE errors out with unsupported datatypes

17. Summary • Finally, a ‘bad file’ for INSERT, UPDATE, DELETE, and MERGE! • Simple to integrate and maintain • Delivers options for higher performance

18. Resources • Faster Batch Processing, Mark Rittman, Oracle Magazine, March/April 2006 • Data Warehousing Guide • PL/SQL Packages and Types Reference • 10gR2 New Feature: DML Error Logging, Natalka Roshak, http://www.orafaq.com/node/76

19. Questions?