How to efficiently use APEX.DEBUG in your Oracle APEX applications

If you have been writing APEX for a while, you have almost certainly done this:

apex_debug.message('got here');
apex_debug.message('value of l_total is ' || l_total);

It works. You turn on debug, you run the page, you go to View Debug, and there are your messages. Job done.

But apex_debug can do quite a bit more than that, and once you start using the rest of it, your debug output goes from "a wall of text I have to scroll through" to "actually useful information I can filter." So let's have a look.

The typed message procedures

Here is the first thing worth knowing. Instead of apex_debug.message, there are procedures named after how important the message is:

apex_debug.error('something went badly wrong');
apex_debug.warn('this is not ideal but we can carry on');
apex_debug.info('just letting you know where we are');

These are not just nicer names. Each one logs at a different level, and that is what makes them useful.

• apex_debug.error logs at level 1

• apex_debug.warn logs at level 2

• apex_debug.info logs at level 4

apex_debug.message, the one you have been using, also logs at level 4 by default. So info and message are basically twins. The real wins are error and warn, because they log at low levels, and low levels are easier to find.

Why levels matter

When you enable debug in APEX, you are not just turning it on, you are picking how much detail you want. The APEX engine itself produces a huge amount of debug output at the higher levels. Your apex_debug.message calls are sitting in there at level 4, mixed in with hundreds of engine messages.

But if you logged something with apex_debug.error, it went in at level 1. And level 1 is the quietest, most important tier. When you are scanning a debug log for "what actually went wrong," the level 1 and level 2 messages are the ones you want, and they are not buried.

Think of it like this: info is for "here is what happened, normal stuff." warn is for "hmm, this is a bit off." error is for "this is the thing that broke." If you tag your messages, you can later look at just the errors and warnings and skip everything else.

There is one more thing about apex_debug.error that makes it special, and it is genuinely useful:

apex_debug.error logs even when debug mode is turned off.

Normally, if debug is not enabled, all your debug calls do nothing. But error always writes. So if you put apex_debug.error calls in your exception handlers, you get a record of real failures in production, where nobody has debug switched on. That is the difference between "a user said it broke last Tuesday" and "here is exactly what broke and when."

The %s trick that cleans up your messages

This is my favourite part, and a lot of people never notice it exists.

You know how debug messages turn into this mess of pipes and quotes?

apex_debug.info('processing order ' || p_order_id || ' for customer ' || p_customer_id || ' with status ' || l_status);

That is hard to read while you are writing it, and easy to get wrong (one missing space and everything runs together). There is a better way. Every typed procedure accepts extra parameters that get slotted into %s placeholders:

apex_debug.info('processing order %s for customer %s with status %s', p_order_id, p_customer_id, l_status);

Same result, much cleaner. You write the sentence once with %s everywhere a value goes, then list the values after. No string concatenation, no pipe soup. You can use up to ten values this way.

It reads better, it is harder to break, and honestly it just makes you feel a bit more organised. Once you start doing this you will not want to go back.

Tracing where your code goes

If you want to see the flow of your code, which procedure called which, apex_debug.enter is built for exactly that. You call it at the start of a procedure and pass in the procedure name and its parameters:

procedure process_order (
   p_order_id    in number,
   p_customer_id in number
) is
begin
   apex_debug.enter('process_order',
      'p_order_id',    p_order_id,
      'p_customer_id', p_customer_id);

   -- the rest of your procedure
end process_order;

What you get in the debug log is a tidy entry showing you walked into process_order and what values it was called with. Do this at the top of each of your packaged procedures and your debug log starts to read like a story: entered this, entered that, here is where it went. When something misbehaves, you can see the path it took to get there.

Turning debug on from code

Usually you switch on debug by clicking the Debug button in the developer toolbar. But sometimes you need it on for code that is not running from a normal page click, a scheduled job, or a process you are testing from SQL Workshop.

apex_debug.enable(p_level => apex_debug.c_log_level_info);

Notice the constant, c_log_level_info, instead of just typing 4. The package gives you named constants for every level (c_log_level_error, c_log_level_warn, c_log_level_info, and so on). Use them. c_log_level_warn tells the next person what you meant. The number 2 does not.

One thing to be careful with

Do not put debug calls inside a big loop without thinking about it.

for i in 1 .. l_orders.count loop
   apex_debug.info('processing row %s', i);   -- 50,000 times...
   -- ...
end loop;

If that loop runs tens of thousands of times, you are writing tens of thousands of debug messages. APEX actually has a flood-control limit on how many messages it will record per page view, so past a point it just stops logging, and you have slowed your page down for nothing.

If you genuinely need to see something inside a loop, log it every hundredth row, or log a summary after the loop instead of a line per iteration. Your debug log will thank you and so will your page load time.

So what should you actually do

Next time you reach for apex_debug.message, pause for a second and ask which one you really want:

Use apex_debug.error in your exception handlers, so production failures get recorded even with debug off. Use apex_debug.warn for the "this is odd but survivable" moments. Use apex_debug.info for normal progress messages. Use the %s placeholders instead of gluing strings together with pipes. And drop an apex_debug.enter at the top of your procedures when you want to trace the flow.

With following the above tips, you just end up with a debug log that actually helps you, instead of one more wall of text to scroll through.