packages tree.log_progress

tree.log_progress

Repository spec: tree.log_progress, body: tree.log_progress

This procedure allow you to easily track long operations thru logs records (tree.flags_longops flag) and also in system views v$session and v$session_longops as you would call DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS yourself.

If you pass 0 or NULL as in_progress it starts new tracking. You will see percentage done in logs.arguments column and time spent in logs.timer column.

Signature

PROCEDURE log_progress (
    in_progress         NUMBER              := NULL  -- in percent (0-1)
);

Show code (69 lines)

PROCEDURE log_progress (
    in_progress         NUMBER          := NULL
) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    --
    slno                BINARY_INTEGER;
    rec                 logs%ROWTYPE;
BEGIN
    tree.get_caller__ (
        out_module_name     => rec.module_name,
        out_module_line     => rec.module_line,
        out_parent_id       => rec.log_parent
    );

    -- find longops record
    IF COALESCE(in_progress, 0) = 0 THEN
        -- first visit
        SELECT e.* INTO rec
        FROM logs e
        WHERE e.log_id = rec.log_parent;
        --
        rec.message         := DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT;    -- rindex
        rec.log_parent      := rec.log_id;  -- create fresh child
        rec.log_id          := log_id.NEXTVAL;
        rec.flag            := tree.flag_longops;
        --
        INSERT INTO logs
        VALUES rec;
    ELSE
        SELECT e.* INTO rec
        FROM logs e
        WHERE e.log_parent  = rec.log_parent
            AND e.flag      = tree.flag_longops;
    END IF;

    -- update progress for system views
    DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS (
        rindex          => rec.message,
        slno            => slno,
        op_name         => rec.module_name,     -- 64 chars
        target_desc     => rec.action_name,     -- 32 chars
        context         => rec.log_id,
        sofar           => COALESCE(in_progress, 0),
        totalwork       => 1,                   -- 1 = 100%
        units           => '%'
    );

    -- calculate time spend since start
    rec.timer :=
        LPAD(EXTRACT(HOUR   FROM LOCALTIMESTAMP - rec.created_at), 2, '0') || ':' ||
        LPAD(EXTRACT(MINUTE FROM LOCALTIMESTAMP - rec.created_at), 2, '0') || ':' ||
        RPAD(REGEXP_REPLACE(
            REGEXP_REPLACE(EXTRACT(SECOND FROM LOCALTIMESTAMP - rec.created_at), '^[\.,]', '00,'),
            '^(\d)[\.,]', '0\1,'
        ), 9, '0');

    -- update progress in log
    UPDATE logs e
    SET e.message       = rec.message,
        e.arguments     = ROUND(in_progress * 100, 2) || '%',
        e.timer         = rec.timer
    WHERE e.log_id      = rec.log_id;
    --
    COMMIT;
EXCEPTION
WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

Minimal example

BEGIN
    tree.log_module();

    -- create as many steps you need
    FOR i IN 0 .. 100 LOOP
        tree.log_progress (
            in_progress => i / 100  -- track percentage
        );

        -- your code
        DBMS_SESSION.SLEEP(DBMS_RANDOM.VALUE() / 1);
    END LOOP;
END;
/

Check results from another session

SELECT t.log_id, t.log_parent, t.arguments, t.message, t.timer
FROM logs t
ORDER BY 1 DESC;