Building a Custom Debug Package for Oracle APEX Using PL/SQL

While developing Oracle APEX applications, debugging page processes and backend PL/SQL logic can be challenging—especially when values are lost between processes or execution flow is unclear. 

Although DBMS_OUTPUT is useful, it doesn’t work well inside APEX runtime.

To solve this, I built a custom PL/SQL debug Package that logs execution flow and variable values into a database table. 

This approach helps trace exactly where the code reached, what values were passed, and whether a block executed or not - even inside page-level processes and packaged procedures

Why a Custom Debug Package?

Works seamlessly inside Oracle APEX page processes

Persists debug information even after session ends

Helps trace execution flow

Captures runtime values

Can be turned ON/OFF dynamically

Does not interrupt business logic

The Package consists of:-

Debug Table                     - Stores debug messages

Sequence                          - Generates unique debug IDs

PL/SQL Package              - Controls logging logic

Debug Status Table          - Enables or disables logging dynamically

Step 1: Debug Table

CREATE TABLE debug_flow (

    debug_id    NUMBER,

    message     VARCHAR2(1000),

    debug_date  DATE

);

This table stores:-

Execution identifiers

Debug messages

Timestamp of execution

Step 2: CREATE SEQUENCE debug_id_seq

                                START WITH 61

                                INCREMENT BY 1

                                CACHE 20;

Each debug entry is uniquely identified using this sequence.

Step 3: Package Specification

CREATE OR REPLACE PACKAGE pkg_debug_flow IS

    package_name        VARCHAR2(100);

    debug_id_seq_val    NUMBER;

    FUNCTION debug_active RETURN VARCHAR2;

    PROCEDURE debug_log (p_seq NUMBER, p_msg VARCHAR2);

    PROCEDURE seq_assign;

    PROCEDURE debug_start(p_message IN VARCHAR2);

END pkg_debug_flow;

/

DEBUG_ACTIVE → Checks whether debugging is enabled

DEBUG_START → Entry point for logging

DEBUG_LOG → Writes logs into table

SEQ_ASSIGN → Assigns sequence value

Step 4: Package Body

Enable / Disable Debug Dynamically. (create this table and insert 'Y' value)

FUNCTION debug_active RETURN VARCHAR2 AS

    debug_sts VARCHAR2(10);

BEGIN

    SELECT status INTO debug_sts FROM debug_active_sts;

    RETURN debug_sts;

EXCEPTION

    WHEN NO_DATA_FOUND THEN

        RETURN 'N';

END;

This allows debug logging to be controlled without redeploying code.

Main Debug Entry Procedure

PROCEDURE debug_start(p_message IN VARCHAR2) AS

    debug_sts VARCHAR2(10);

BEGIN

    debug_sts := pkg_debug_flow.debug_active;

    IF NVL(debug_sts, 'N') <> 'N' THEN

        seq_assign;

        debug_log(

            debug_id_seq_val,

            package_name || ' <---> ' || p_message

        );

    END IF;

END;

This procedure:-

Checks if debug is active

Assigns sequence

Logs message safely

Autonomous Logging Procedure:-

PROCEDURE debug_log(p_seq NUMBER, p_msg VARCHAR2) AS

    PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

    INSERT INTO debug_flow (debug_id, message, debug_date)

    VALUES (p_seq, p_msg, SYSTIMESTAMP);

    COMMIT;

END;

Using AUTONOMOUS_TRANSACTION ensures:-

Debug logs are saved

Even if main transaction fails or rolls back

Page Process or PL/SQL Block

PKG_DEBUG_FLOW.package_name := 'P42_SAVE_PROCESS';

PKG_DEBUG_FLOW.debug_start('Before insert into EMP table'); -- even check values also with variable (using concatenation after msg)

-- Business logic here

PKG_DEBUG_FLOW.debug_start('After insert into EMP table');

Then check use below query 

SELECT * 

FROM debug_flow

ORDER BY debug_date DESC;

This custom debug Package has significantly improved my productivity while developing complex Oracle APEX applications. 

It provides clear visibility into execution flow and variable values without disturbing the application logic.