In general I do instrument all my code, but usually I have plsql code that calls some framework like logger. Because of that I rarely add extra APEX instrumentation code. Any trace or debug information is already written into the logger table.
Sometimes it makes sense to add calls to apex_debug too. Especially if this part of the logic is checked frequently using the APEX built-in debug functionality.
Debugging in APEX has a huge advantage. As a developer it is very easy to do and to access the output.
The apex_debug package is available at least since APEX 5. Among others it includes a procedure enter to store parameters. It is recommended to call this at the beginning of your modules and add the combination of parameter name and value to the procedure.
Recently I added apex_debug.enter to several of my modules. Here are a few tricks I’d like to share.
Tipp 1: Debug level 5
Apex_debug has several levels. The default level is 4 (info).
If you want to see the information stored with apex_debug.enter, you need to show at least level 5.
The level can be set in the url. Instead of YES, set it to LEVEL5.
f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly
In APEX I use the enter procedure to store page item values that are used inside plsql blocks but also to see what is going on at the database level. Here is an example
The process first calls apex_debug.enter. Then the procedure pk_setze_defaults.prepare_topbar calls apex_debug.enter a second time and stores the supplied parameter values.
And this is how the view debug output can look like
Useful to see the state of page items at time of processing. And even more interesting is to see what procedures where called and which parameters had been used.
Tipp 2: avoid hardcoding the module name
The first parameter of the enter procedure is p_routine_name.
Instead of hardcoding the name we can call utl_call_stack to return the name of the module. You need to have at least database version 12c to use it.
utl_call_stack.concatenate_subprogram(
utl_call_stack.subprogram(1))
Utl_call_stack.subprogram gives us access to the module names inside the call stack. Number 1 in the stack is always the current module. Subprogram returns a collection which holds the package name and the submodule name. Sometimes multiple submodule names. The concatenate_subprogram function translates this collection into a readable string (divided by dots).
Example: Instead of hardcoding the module name ‘post_authenticate’
apex_debug.enter(p_routine_name=>'post_authenticate'
,p_name01 =>'p_user', p_value01 => p_user);
I use utl_call_stack to have the database fetch the module name at runtime
apex_debug.enter(p_routine_name=>utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1))
,p_name01 =>'p_user', p_value01 => p_user);The result:
PK_APEX_UTIL.post_authenticate p_user=>Sven
The first part is the module name (incuding package name), the second part is a list of parameter=>value combinations.
There are some side effects to it. The name is fetched at runtime, instead of decided at compile time.1 In some cirumstances (module inlining) a procedure can be rewritten by the plsql optimizer, so that the name of the module disappears. Utl_call_Stack would then return the name of the module, where the code was inlined into.The runtime call is also slightly slower than the literal value.
Inside a plsql process in APEX the name will be __anonymous_block . Which is correct. APEX executes those blocks using dbms_sql. The name of the process is not known inside the block. But it can be seen in the APEX debug view output one line before the anonymous block.
So the advantage of not hardcoding the module name must be weighted against the possible side effects.
If you want the procedure name, but not the package name, then the following code will help. It returns only the name of the current (innermost) submodule:
utl_call_stack.subprogram(1)(utl_call_stack.lexical_depth(1)+1)Tipp 3: use the newest SQL Developer version (18.4)
SQL Developer 18.4 has the built-in ability to grey out instrumentation code. I like this feature a lot. It allows the trained developers eye to quickly scan plsql code – without resting too much on the less important bits and pieces.
What surprised me is that this also includes apex_debug.
Here is an example screenshot. Notice how the whole call to apex_debug is greyed out.
Other packages that are greyed out are dbms_output, log and logger.
And we can add our own instrumentation framework to it. Which leads me to tipp 4.
Tipp 4: configure SQL Developer – add your instrumentation framework
It is a bit hard to find, but the color rule PlSqlCustom2 is where we can add our own package. Search for color in the preferences to find the entry. In one of my projects the instrumentation package is called pk_logging. So I add it like the screenshot shows.
Tools / Preferences => Code Editor / PL/SQL Syntax colors
And this is how the sample result looks like.
Tipp 5: use snippets
Snippets are a nice little feature in SQL Developer. And you can add your own useful code snippets to it. I added a snippet for apex_debug.enter
Once implemented I simply double click the snippet and it adds the following code block.
apex_debug.enter(p_routine_name => utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1))
,p_name01=>'XXX',p_value01=>xxx
,p_name02=>'YYY',p_value02=>yyy
,p_name03=>'ZZZ',p_value03=>zzz
);
If you like you can download this snippet from the SQL Developer exchange plattform (https://apex.oracle.com/pls/apex/f?p=43135:16:0::NO:RP,16:P16_ID:1141)
But it is easier just to create your own snippet and copy the code from here.
Conclusion
Make use of the developer tools you have. Use apex_debug, use SQLDeveloper. Think about your instrumentation framework. Make it as easy to use as possible.
Further readings
- Carsten Szarski (German): Debugging und Instrumentierung für APEX Anwendungen: APEX_DEBUG
- Apex 18 doc: APEX_DEBUG
- Steven Feuerstein: sophisticated call stack analysis
- Jeff Smith: I say Snippets, Snippets good!
- Apex 18 doc: understanding url syntax
svenweller 