One of many small enhancements in Oracle APEX 5.0 is a small utility that can be used to help debugging an APEX application from SQLPLUS or sqlcl.
The debug directory contains 4 SQL scripts:
Now lets take a look at what those scripts are doing:
It immediately switches global debugging on. That means EVERYTHING on this instance will be debugged, every application in every workspace. This also includes the Application Builder, SQL Workshop and so on. So if you are working on a large system with many users and many applications you’ll have to think twice about using it. It is probably not a good idea…
Anyway, since we are connected to an internal DEV instance, it should be OK.
Running d0.sql a second time disables debugging again.
d1.sql and ds.sql
Nice enhancement here is that it not only shows debug info of Page Loads (show) and Page Submits (accept), but also AJAX calls (ajax plugin) which are normally not shown in debug.
With d2.sql you pass a PAGE_VIEW_ID and it shows the detailed debug messages in your favorite editor.
So at first make sure to define what is your favorite editor. When you run d0.sql to enable debugging it shows how to use vim as editor for example: DEFINE _EDITOR=vim
Since my favorite editor on Mac is Sublime, I tend to use that by setting DEFINE _EDITOR=subl
The big question there is: why would anyone want to use these tools instead of the regular debugging facility in the APEX UI?
To me it is a matter of doing things simple and quick. Enabling debug for the whole instance is quick and easy and it ensures it captures everything. Enabling debug in your application might miss important things, like AJAX calls triggered by native Dynamic Actions.
I know this is very low level and might be an overkill for most situations, but I do love to have that option.
If you are new to APEX and still struggle with understanding what actually is going on, then I would recommend to stay clear of these tools for now and come back once you understand the impact of switching your instance to debug.
Edit 13.10.2021: add link to Christian Neumüllers Blogpost: https://chrisonoracle.wordpress.com/2020/04/03/debugging-apex-authentication-issues/