20.1.6.4 Logging Error Info for Troubleshooting

Log error details in a separate transaction so troubleshooting data survives the rollback.

The ADD_ERROR_LOG helper procedure receives the T_ERROR record the APEX engine passed your error handling function. It writes useful information this record contains to the following MESSAGE_APP_ERRORS table.

create table messages_app_errors 
(
    id number generated by default on null as identity not null,
    err_time        timestamp(6) default systimestamp,
    app_id          number,         /* Application ID           */
    app_page_id     number,         /* Page ID                  */
    app_user        varchar2(512),  /* Application User         */
    user_agent      varchar2(4000), /* Browser User Agent       */
    ip_address      varchar2(512),  /* Browser IP Address       */
    ip_address2     varchar2(512),  /* ORDS IP Address          */
    message         varchar2(4000), /* Error Message            */
    page_item_name  varchar2(255),  /* Page Item Name           */
    region_id       number,         /* Region ID                */
    column_alias    varchar2(255),  /* Column Alias             */
    row_num         number,         /* Row Number               */
    apex_error_code varchar2(255),  /* APEX Error Code          */
    ora_sqlcode     number,         /* Oracle SQL Code          */
    ora_sqlerrm     varchar2(4000), /* Oracle SQL Error Message */
    error_backtrace varchar2(4000), /* Error Backtrace          */
    procedure_name  varchar2(1000), /* Procedure Name           */
    error_text      varchar2(4000), /* Error Text               */
    constraint message_app_errors_id_pk primary key (id)
);

The procedure operates in a separate transaction by including the following in its declare section.

pragma autonomous_transaction;

This requires also that it perform an explicit commit, which it does at the end. This combination ensures the logged row is saved even though the APEX engine performs a rollback to abandon pending data changes when encountering an error.

The procedure starts by deleting log messages older than three weeks. Then it inserts a new MESSAGES_APP_ERRORS row. Some of the values comes from APEX substitution strings like:

• APP_ID – application id
• APP_PAGE_ID – page id
• APP_USER – currently logged-in user name

The remaining values are from the p_error record that includes, among others, details like:

• p_error.page_item_name – page item name
• p_error.message – error message
• p_error.region_id – region id
• p_error.apex_error_code – APEX error code
• p_error.ora_sqlcode – ORA error code
• p_error.ora_sqlerrm – ORA error message
• p_error.error_backtrace – PL/SQL call stack

Tip:

See the reference documentation for the APEX_ERROR package for complete details on the fields of the T_ERROR record.

-- In package messages_app
procedure add_error_log ( 
    p_error           in apex_error.t_error,
    p_procedure_name  in varchar2 default null,
    p_error_text      in varchar2 default null)
is
pragma autonomous_transaction;
begin
    -- Remove old errors
    delete from messages_app_errors where err_time <= current_timestamp - 21;
    -- Log the error.
    insert into messages_app_errors (
        app_id,
        app_page_id,
        app_user,
        user_agent,
        ip_address,
        ip_address2,
        message,
        page_item_name,
        region_id,
        column_alias,
        row_num,
        apex_error_code,
        ora_sqlcode,
        ora_sqlerrm,
        error_backtrace,
        procedure_name,
        error_text)
    select v('APP_ID'),
        v('APP_PAGE_ID'),
        v('APP_USER'),
        owa_util.get_cgi_env('HTTP_USER_AGENT'),
        owa_util.get_cgi_env('REMOTE_ADDR'),
        sys_context('USERENV', 'IP_ADDRESS'),
        substr(p_error.message,0,4000),
        p_error.page_item_name,
        p_error.region_id,
        p_error.column_alias,
        p_error.row_num,
        p_error.apex_error_code,
        p_error.ora_sqlcode,
        substr(p_error.ora_sqlerrm,0,4000),
        substr(p_error.error_backtrace,0,4000),
        p_procedure_name,
        p_error_text
    from dual;
    commit;
end add_error_log;