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