How to debug/trace APEX session

In most cases you don't have access to production environment and reproducing of a production bug can be a real problem.

If you don't use logger (which I strongly recommend) or you don't have a feedback page you can always use APEX's native debugging tool.

There's a easy way to use native APEX debug and you can turn it on:

• with 5th URL parameter that can be set to YES or to some value between LEVEL0 to LEVEL9 (there's no LEVEL3 and LEVEL7). YES equals LEVEL4 (more on debug levels here and here). You can use this only for applications that have debugging property turned on (under application properties). 
• programatically with apex_debug package.

But on production environment you probably want to track a specific user session. To turn it on for specific user session you can use apex_session package (new from APEX 5.1).

To find out what session you want to track you can ask your end user to read you a third parameter in URL (don't do that 🙂) right after application and page id or you can track it yourself by querying APEX dictionary view apex_wokspace_sessions:

select *
  from apex_workspace_sessions 
 where workspace_name = &WORKSPACE_NAME 
   and user_name = &USERNAME;

After that you can call apex_session.set_debug to turn on debugging for this specific user session:

begin 
  apex_session.set_debug(p_session_id => &SESSION, p_level => 4); 
  commit; 
end;
/

Other solution is to use APEX builder (if you have access). To turn it on in APEX builder go to the Monitor Activity > Active Sessions find the session and change session attribute Debug Level

Then you can ask your end user to try to reproduce an application bug and after that you can query apex_debug_messages dictionary view to check for errors or you can use APEX Builder to do the same (Application > Utilities > Debug Messages).

You can use same package (apex_session) to turn on SQL tracing (procedure set_trace). Tracing can be turned on also with URL parameter. 

Enjoy!

Tested on APEX 5.1.2.00.09

APEX debug levels

From time to time I run into some new stuff about APEX. So few days ago I was investigating something about debug messages and figured it out that there are different levels of debug messages in APEX.

You probably know that as a sixth argument in APEX URL you can pass either YES or NO and that will turn on/off page debugging (if it's enabled in application properties). So the thing I discovered is that you can also pass LEVELn where n is between 1 and 9 and stands for level of debug detail to display. The value of YES is equal to LEVEL4.

If you take a look at apex_debug package specification you can see meaning of each level (although missing levels 3 and 7):

-- critical error
c_log_level_error        constant t_log_level := 1; 

-- less critical error
c_log_level_warn         constant t_log_level := 2; 

-- default level if debugging is enabled (e.g. used by apex_application.debug)
c_log_level_info         constant t_log_level := 4; 

-- application: messages when procedures/functions are entered
c_log_level_app_enter    constant t_log_level := 5; 

-- application: other messages within procedures/functions
c_log_level_app_trace    constant t_log_level := 6; 

-- apex engine: messages when procedures/functions are entered
c_log_level_engine_enter constant t_log_level := 8; 

-- apex engine: other messages within procedures/functions
c_log_level_engine_trace constant t_log_level := 9; 

You can set this level when adding debug messages manually using apex_debug.message procedure.