DML error handling consolidated

O

One of the most overlooked features of Oracle since Oracle 10g?
DML error logging.

When you insert/update/merge/delete data in a table, you might get an error if you are trying to insert string into a number column, if the string is too long, if the key is not valid... You will get an error message but you don't exactly know which column and value caused this problem. That's where you can leverage this feature. All you need to do is to:

• add 1 line at the end of your DML statement
• create error tables

If you already know how this works, scroll down to My problem section, there is the twist.

Magic line

The one line you have to add looks like:

LOG ERRORS INTO table_name_erros (your_tag)

So your INSERT became like:

INSERT INTO roles (role_id, role_name)
VALUES (
    1,
    'DEVELOPER'
)
LOG ERRORS INTO err$_roles (123456);

Same goes for other DML operations. Simple change in your code and huge difference on error.

Error tables

To create the error tables you can use DBMS_ERRLOG package. It basically create a clone of your table where all column are as VARCHAR2(4000) (or VARCHAR2(32767) depending on your DB setup) and add some additional ORA_ERR_* columns. I wrapped it in a procedures (simplified code, full version available at CORE.APP package):

    FUNCTION get_dml_table (
        in_table_name           logs.module_name%TYPE
    )
    RETURN VARCHAR2
    AS
    BEGIN
        RETURN REGEXP_REPLACE(REGEXP_REPLACE(in_table_name,
                '(' || REPLACE(app.dml_tables_postfix, '$', '\$') || ')$', ''),
                '^(' || REPLACE(app.dml_tables_prefix, '$', '\$') || ')', '') ||
            app.dml_tables_postfix;
    END;



    PROCEDURE create_dml_table (
        in_table_name           logs.module_name%TYPE
    )
    AS
    BEGIN
        -- drop existing tables
        app.drop_dml_table(in_table_name);
        --
        DBMS_ERRLOG.CREATE_ERROR_LOG (
            dml_table_name          => in_table_name,
            err_log_table_name      => app.get_dml_table(in_table_name),
            skip_unsupported        => TRUE
        );
    END;



    PROCEDURE drop_dml_table (
        in_table_name           logs.module_name%TYPE
    )
    AS
    BEGIN
        -- process existing data first
        app.process_dml_errors(in_table_name);
        --
        BEGIN
            EXECUTE IMMEDIATE
                'DROP TABLE ' || app.get_dml_table(in_table_name) || ' PURGE';
        EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE NOT IN (-942) THEN  -- table doesnt exists
                RAISE;
            END IF;
        END;
    END;

On error

So what happen on error? With table LOG ERRORS INTO clause all values used in your DML statement are on error logged into the specified table and with your tag, so you can know from which operation error come from. I generally log start of every module (procedure), so I pass this number to link the error with the procedure causing it.

Lets try it. Create a test table and its error table clone:

--DROP TABLE roles2;
CREATE TABLE roles2 (
    app_id              NUMBER(6),
    role_id             VARCHAR2(30),
    role_name           VARCHAR2(64),
    is_active           CHAR(1),
    --
    CONSTRAINT pk_roles2
        PRIMARY KEY (role_id)
);

BEGIN
    app.create_dml_table('ROLES2');
END;
/

DESC roles2_e$;

/*
Name            Null? Type            
--------------- ----- --------------- 
ORA_ERR_NUMBER$       NUMBER          
ORA_ERR_MESG$         VARCHAR2(2000)  
ORA_ERR_ROWID$        UROWID          
ORA_ERR_OPTYP$        VARCHAR2(2)     
ORA_ERR_TAG$          VARCHAR2(2000)  
APP_ID                VARCHAR2(4000)  
ROLE_ID               VARCHAR2(32767) 
ROLE_NAME             VARCHAR2(32767) 
IS_ACTIVE             VARCHAR2(32767) 
*/

Now lets create an error (insert string to a number column):

DECLARE
    recent_log_id       logs.log_id%TYPE;
BEGIN
    recent_log_id := app.log_module('CREATE_ROLE');
    --
    INSERT INTO roles2 (app_id, role_id)
    VALUES ('NOT A NUMBER', 'DEVELOPER')
    LOG ERRORS INTO roles2_e$ (recent_log_id);
    --
    COMMIT;
END;
/

As expected, you will see this error:

ORA-01722: invalid number

Which is not very helpful on big tables. Lets check the error table content:

SELECT * FROM roles2_e$;

You have your values and your tag there.

My problem

Here come my problem. With hundreds tables I don't want to check all of them. I don't want to have these values scattered across tables. I want them on one place, preferrably in my error log table LOGS linked to the procedure responsible for this error (I log errors as a tree, with references to previous caller). So when I check errors in my log, I will also see table columns and passed values as JSON object on the left (Arguments) and as generated MERGE statement with data types on the right (Payload). Check get_dml_query function for the MERGE statement, process_dml_errors procedure to moving data from error tables to log. Now I can very quickly fix the problem.

I have a CORE_SYNC_LOG job which will go through all DML error tables and move lines to the error log and link them to the actual error, as you can see on the screen above.

APEX & DML error handing

Unfortunatelly the support for this feature is completely missing in APEX. I posted it as an idea FR-2325, so if you like to see it, you can support it by your vote.