home

Lumberjack - tree logger for PL/SQL

Simple to use yet powerful

Purpose of this project is to track all errors (and debug messages) in ORACLE database as a tree so you can quickly analyze what happened. It has automatic scope recognition, simple arguments passing and many interesting features.

Some features

• module tracing with simple arguments passing, automatic scope recognition and tree building; see demo below and tree.log_module
• tracking setup in a table (what, where, who); see logs_setup table
• DML errors linked to proper log record with passed args; see logs_dml_errors view
• LOBs linked to proper log record; see tree.attach_clob and tree.attach_blob
• support scheduler and context passing; see tree.log_scheduler and tree.start_scheduler
• include profiling; see logs_profiler and logs_profiler_sum views
• automatic error backtrace on errors
• environmental and application contexts logging
• APEX support (apps, pages, items...)
• automatic purging of old records; see tree.purge_old

Minimal example

1) mark all your procedures/functions with tree.log_module

CREATE OR REPLACE PACKAGE BODY demo AS

    PROCEDURE a (
        in_first    NUMBER      := NULL,
        in_second   NUMBER      := NULL,
        in_third    NUMBER      := NULL
    ) AS
    BEGIN
        -- MANDATORY call with passed arguments
        tree.log_module(in_first, in_second, in_third);

        -- your code
        tree.log_debug('FIRST MESSAGE');    -- optional
        NULL;

        -- wrap up
        tree.log_result('OK');              -- optional
        tree.update_timer();                -- optional to track time spend on module
    END;


    PROCEDURE b (
        in_first    NUMBER      := NULL,
        in_second   NUMBER      := NULL
    ) AS
    BEGIN
        -- MANDATORY call with passed arguments
        tree.log_module(in_first, in_second);

        tree.log_debug('SECOND MESSAGE');   -- optional
        NULL;
        --
        tree.update_timer();                -- optional to track time spend on module
    END;


    PROCEDURE run_both AS
    BEGIN
        tree.log_module();                  -- MANDATORY
        --
        tree.log_action('CALLING_A');       -- optional to split long procedures
        demo.a(1, 2, 3);
        --
        tree.log_action('CALLING_B');       -- optional
        demo.b(4, 5);
        --
        tree.update_timer();                -- optional to track time spend on module
    END;

END;
/

2) run your code

BEGIN
    tree.log_module('ANONYM');              -- MANDATORY
    --
    demo.run_both();
    --
    tree.update_timer();                    -- optional
    tree.set_tree_id(tree.get_root_id());   -- optional to prepare logs_tree view
END;
/

3) check log table

SELECT t.log_id, t.flag, t.action_name, t.module_name, t.line, t.arguments, t.timer
FROM logs_tree t;

LOG_ID	FLAG	ACTION_NAME	MODULE_NAME	LINE	ARGUMENTS	TIMER
313928	M		__anonymous_block	2	ANONYM	00:00:00,005588
313929	M		DEMO.RUN_BOTH	39		00:00:00,004213
313930	A	CALLING_A	DEMO.RUN_BOTH	41
313931	M		DEMO.A	10	1|2|3	00:00:00,002609
313932	D		DEMO.A	13	FIRST MESSAGE
313933	R		DEMO.A	17	OK
313934	A	CALLING_B	DEMO.RUN_BOTH	44
313935	M		DEMO.B	28	4|5	00:00:00,000401
313936	D		DEMO.B	30	SECOND MESSAGE

If you like my work, please consider small donation.