Using APEX_DEBUG Without an APEX Session

Check out APEX Instant Tips episode 178 for more information on this topic.

I will flush out this blog post soon, but for now, if you want to use APEX_DEBUG without being in the context of an APEX application, take the following steps.

Create the following table

CREATE TABLE  "XXTLN_DEBUG" 
   (    "DEBUG_LEVEL" NUMBER
   )
/

Add 1 row to the table

insert into xxtln_debug (debug_level) values (1);

Create the following package

create or replace package XXTLN_DEBUG_util as

-- Set how often you would like to check for a change to the debug level.
-- Note: do NOT set this while things are running...you should set it to what you want it to be all the time and leave it alone.
-- It is reasonable to set it 1 so that it always check, but you may decide to check less fequently.
gc_workspace_name               constant    varchar2(256) := 'ANTON';

g_check_every_n_calls           number := 1;
g_count_since_debug_checked     number := g_check_every_n_calls + 1;
g_debug_level                   number;

procedure setup;

end;
/

create or replace package body XXTLN_DEBUG_UTIL as

procedure setup is

l_sg_id         number;
l_debug_level   number;
begin
    -- Make sure we always have a workspace ID so that errors and "forced messages" are visible.
    if sys_context('APEX$SESSION','WORKSPACE_ID') is null then
        apex_util.set_security_group_id(
            p_security_group_id => apex_util.find_security_group_id(p_workspace => gc_workspace_name)
            );
    end if;

    -- If we are NOT in an APEX application session set the "no APEX session" debug level.
    if sys_context('APEX$SESSION','APP_ID') is null then
        -- only check every n calls
        if g_count_since_debug_checked >= g_check_every_n_calls then
            -- Get the current "no APEX session" debug level
            select max(debug_level) into l_debug_level
              from xxtln_debug;

            -- Set the debug level if it has changed.
            if nvl(g_debug_level, -1) != nvl(l_debug_level, -1) then

                if l_debug_level is not null then
                    apex_debug.enable(l_debug_level);
                else
                    apex_debug.disable;
                end if;

                g_debug_level := l_debug_level;                
            end if;

            g_count_since_debug_checked := 1;
        else
            g_count_since_debug_checked := g_count_since_debug_checked + 1;
        end if;
    end if;
end setup;

end XXTLN_DEBUG_UTIL;
/

Add the following line of code to the beginning of all of your public functions and procedures within your PL/SQL code

xxtln_debug_util.setup;