Using APEX_DEBUG Without an APEX Session

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

Code instrumentation is key to developing maintainable and testable applications. The APEX_DEBUG PL/SQL API has everything I need and a lot of features under the covers that most home-grown systems lack (like flood control). Despite initial appearances, you can use APEX_DEBUG without being in the context of an APEX application, though it takes just a little extra. Below are a few steps that will give you all of the features of APEX_DEBUG without needing an APEX session.

Create the following table

This will allow you to control the debug level when you don’t have an APEX context. You can add additional columns to support additional requirements. For example, you could add a column to indicate a particular database session for which you want to set the debug level. If you alter the table below you will need to change the corresponding package (further below) to meet your needs.

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

Add 1 row to the table

In this case we are just going to set the debug level for ALL sessions that do not have an APEX context.

insert into xxtln_debug (debug_level) values (1);

Create the following package

This package just handles setting the APEX security group id (aka workspace id). Setting that allows you to see the contents of the APEX debug message log.

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;