Nothing was broken and the rows were still missing

Debugging an APEX collection without App Builder, and the three tools I wish I had not forgotten about that morning.

TL;DR: A UAT tester reported that not every selected row got processed. I could not inspect the APEX session in UAT, and SQL Developer cannot see an APEX collection because it is not part of the APEX session. I worked around it with a report region that only shows in debug mode. It turned out nothing was broken: an inner join to a configuration table was silently dropping rows with incomplete reference data. Along the way I learned three ways to get visibility without App Builder: a debug-only diagnostic region behind a build option, APEX_SESSION.ATTACH to query a live session from your IDE, and APEX_SESSION.SET_DEBUG to debug someone else's session.

A tester in UAT reported that not everything they selected got processed.

The setup is common enough. The user selects rows in an interactive grid, the selection goes into an APEX collection and a process calls a package. Inside that package a cursor joins the collection to two tables. Some of the selected rows made it through and some did not.

I do this kind of work at Qualogy, building Oracle APEX applications for enterprise clients, and "some of it did not happen" is one of the more common reports I get. It is also one of the more annoying ones, because nothing errors.

If you do not work with APEX: a collection is a temporary, table-like structure APEX gives you for holding data during a user's session. It belongs to that one APEX session. Other sessions, including your own database connection, cannot see it.

The first wall: I could not look

We do not have App Builder in TEST or UAT. App Builder is APEX's development environment and it comes with the developer toolbar, the session state viewer and the collection browser. Without it I had no way to see what was in the tester's collection, so the finding was in the one environment where I had the least visibility.

That setup is correct, by the way. Disabling App Builder outside DEV is exactly what you should do. But it means the bugs and the tools live in different places.

So I recreated the tester's case in DEV. There I could see the collection and the selected IDs were in it. The collection was fine, so the cursor had to be dropping rows somewhere in its joins.

The second wall: I could not run the cursor

The obvious next step is to copy the cursor into SQL Developer and run it. I knew that would not work. SQL Developer opens its own database session, which is not part of any APEX session. That means the collection is empty there and the join returns nothing. That result would tell me nothing about the real join.

What I had forgotten was how to get SQL Developer into the right session. So I worked with what I knew would certainly work: a region on an APEX page runs inside the APEX session. I put the cursor's query into a report region on the page itself. There the collection join works and I could see exactly which rows came back and of course which did not.

Now I did not want anyone but me (or other developers) to see that report, so it should only appear when debug mode is on. That was the question I took to an AI assistant, and the conversation went further than the condition. What came out of it were three tools I had heard about already but don't use often. I will come back to what the report actually showed at the end.

1. A diagnostic region that only you can see

This is the one I built: a region that shows the collection contents, the join result, and the rows that fall out, visible only in debug mode.

Set the region's server-side condition to PL/SQL Expression:

apex_application.g_debug

G_DEBUG is a BOOLEAN global in the APEX_APPLICATION package, so the expression is the condition. No comparison needed.

When it wins: verifying your own scenarios while you build and test them. You click through the application normally, switch debug on, and see what the engine sees.

Do not deploy it: use a build option

I want to be direct about this part, because it is what turns a useful technique into a liability.

I am not deploying this region to production, and I would not recommend you do either. The g_debug condition controls whether the region renders. A build option controls whether the component exists in the running application at all. Build options are APEX's switch for including or excluding components per environment. For a diagnostic region you want that second level.

Why is the debug condition not enough? Oracle recommends disabling the application's Debugging attribute in production so users cannot view application logic. But the same page notes that debug can still be enabled programmatically regardless of that setting. So a region gated on g_debug is a convenience for you. It is not an access control.

Create a build option, call it DEV_DIAGNOSTICS, and assign every diagnostic region, page and process to it. Include in DEV, exclude everywhere else. Andre van der Put describes the same setup in his post on build options in deployments, and it is worth reading for the other things build options can do.

And if you genuinely want functional administrators to have a diagnostic panel in production, that is a different feature with a different gate: an authorization scheme, a role, and a deliberate decision about what it shows. Not a debug flag.

2. Attach your IDE to the APEX session

This is the one I had forgotten when I built the report region. I tested it afterward, attached to my own session from SQL Developer, and it is the one I would reach for first next time.

APEX_SESSION.ATTACH joins your database session to an existing APEX session. Take the session ID from the tester's URL (the session parameter with friendly URLs, or the third value in classic f?p syntax) and run:

begin
  apex_session.attach(
    p_app_id     => 100,
    p_page_id    => 1,
    p_session_id => 837851762118
  );
end;
/

After that a query from apex_collections returns what that session actually holds. I tried it on my own session first: navigated to a page, took the session ID from the URL, attached from SQL Developer and ran the collection query. It came back with real rows, the same rows I would otherwise only see through App Builder. Call apex_session.detach when you are done.

When it wins: anything involving collections or session state in an environment you cannot inspect from the UI.

3. Turn on debug for someone else's session

This is for when the person who can reproduce it is not you. Oracle documents APEX_SESSION.SET_DEBUG for exactly this case: issues that only reproduce for other users.

begin
  apex_session.set_debug(
    p_session_id => 837851762118,
    p_level      => apex_debug.c_log_level_info
  );
  commit;
end;
/

The level applies to all future requests in that session, so the tester has to click through the scenario again after you set it. Pass null as the level to switch it off.

I ran this on my own session the same way: set debug at info level, clicked back through the app so the new level actually applied, then queried the view from SQL Developer. The table was empty right after I set the level, because nothing had run yet under the new setting. Once I clicked through the scenario again, the rows were there.

Debug messages land in the APEX_DEBUG_MESSAGES view, which you can query from SQL Developer:

select message_timestamp, message_level, message
from   apex_debug_messages
where  session_id = 837851762118
order  by message_timestamp;

Two things will waste your afternoon if you do not know them.

Debug output is buffered. Christian Neumueller of the APEX development team explained this in 2019, as quoted by Dimitri Gielis: LEVEL9 writes immediately, while the other levels only write after 1000 records, at the end of request processing, or when the session detaches. If you set debug and the view is empty, the request probably has not finished yet. That explanation is from 2019, so check it against your release.

Start low. On a live user, begin at info level rather than 9. Higher levels log a lot more.

And this only helps if your code says something. SET_DEBUG gives you the engine's trace: which processes ran and how long they took. It will not tell you that rows fell out of a join unless you wrote that message yourself:

apex_debug.message('processing %s of %s selected rows', l_matched, l_selected);

Add debug messages now, so the tool works later. Unlike the diagnostic region, these lines should ship to production. By default they are only written when debug is enabled for that session, and they are the only reason this technique tells you anything about your own logic.

What it actually was

Back to the report region. The cursor was correct and the joins were correct. The tester's data was incomplete: they had picked records whose parent reference data had not been set up yet, and an inner join to a configuration table quietly removed them.

Nothing was broken. That is the part worth sitting with.

A join to a configuration table is an undeclared filter. Nobody wrote "and only rows whose parent is already configured" as a business rule, nobody told the user it existed, and the application had no way to say which rows it skipped and why. In UAT that cost me an hour. In production, on a real user's selection, it costs an explanation nobody can give.

So the fix is not in the SQL. It is telling the user what happened, in a message along the lines of: 7 of 10 processed, 3 skipped, reference data missing for A, B and C. That message ships to production. The diagnostic region does not.

The bugs that cost the most are not the ones that throw errors. They are the ones where everything succeeds and the answer is still wrong.

What do you reach for first when a tester says "some of it did not happen" and you cannot see their session?