The :P#_ITEM placeholder that binds NULL: a filter that never worked, and a check that passed

I keep a snippet for an optional report filter. It’s written for no particular page, so it uses # where the page number goes:

where (:P#_STATUS is null or status = :P#_STATUS)

I pasted it into the Orders report on page 8 and forgot to replace # with 8. Nothing complained. The page ran, the report showed its rows, and the Status field sat above it. The filter just didn’t do anything. That’s easy to miss, because a report that shows every order looks perfectly normal.

Why there was no error#

Two things line up.

# is a legal character in an Oracle name. Unquoted identifiers can contain letters, digits, _, $ and #, so :P#_STATUS is a valid bind variable name. The query parses, and a bind value can be set for it:

parse + bind of :P#_STATUS: OK

APEX binds the name, whether an item exists or not. When APEX runs the region’s query, :P#_STATUS gets the value of an item called P#_STATUS. There is no such item, so the value is NULL, and the page shows no error.

With NULL, the first half of the filter is true for every row:

(:P#_STATUS is null or status = :P#_STATUS)
--  NULL is null  -> true, for every order

So the filter always says “no filter”.

I reproduced it on APEX 26.1.0: an interactive report on DEMO_ORDERS (5 orders: 2 NEW, 2 SHIPPED, 1 PAID) with a P8_STATUS text field above it.

The fix#

Use the real item name:

where (:P8_STATUS is null or status = :P8_STATUS)

That’s the whole code change. The more useful part is why my check didn’t catch the mistake.

The check that passed for the wrong reason#

After pasting, I checked the region source with a search like this:

select page_id, region_name,
       case when instr(region_source, '_STATUS') > 0 then 'found' else 'missing' end as check_result
  from apex_application_page_regions
 where application_id = :APP_ID
   and page_id = 8;

It said found, because _STATUS is also part of P#_STATUS. I had also confirmed the query parses, which it does, because # is legal. Both checks answered a different question from the one that mattered: does every bind variable in this query belong to a real item?

A check that answers the right question#

This query lists every bind variable in the application’s region sources that doesn’t match a page item, an application item or a built-in name:

with binds as (
  select r.page_id, r.region_name,
         upper(to_char(regexp_substr(r.region_source, ':([A-Za-z][A-Za-z0-9_$#]*)', 1, l.n, null, 1))) as bind_name
    from apex_application_page_regions r,
         lateral (select level as n from dual
                  connect by level <= regexp_count(r.region_source, ':[A-Za-z][A-Za-z0-9_$#]*')) l
   where r.application_id = :APP_ID
)
select distinct b.page_id, b.region_name, b.bind_name
  from binds b
 where b.bind_name not in (select item_name from apex_application_page_items where application_id = :APP_ID)
   and b.bind_name not in (select item_name from apex_application_items      where application_id = :APP_ID)
   and b.bind_name not in ('APP_ID', 'APP_USER', 'APP_SESSION', 'APP_PAGE_ID', 'REQUEST', 'DEBUG')
 order by 1, 2, 3;

On the broken application it returned one row, and none after the fix:

PAGE_ID  REGION_NAME  BIND_NAME
-------  -----------  ---------
      8  Orders       P#_STATUS

A few notes before you rely on it:

• It reads region sources only. Page processes (APEX_APPLICATION_PAGE_PROC.PROCESS_SOURCE), LOVs (APEX_APPLICATION_LOVS.LIST_OF_VALUES_QUERY) and validations can hold binds too. The same pattern works on those columns, but I only ran it on regions.
• It can report false positives. A time format such as 'HH24:MI' inside the SQL shows up as a bind called MI. Built-ins you use that aren’t in the list (for example APP_ALIAS) show up too. Add them to the list.
• A bind that points to an item on another page (:P3_STATUS on page 8) passes the check. The item exists, so the query can’t tell the bind is wrong.

Two habits that would have caught it earlier#

Test a filter with a value that must return nothing. NOSUCH returned 5 rows with the broken filter and 0 with the fixed one. A filter that returns the same rows for every value is doing nothing, whatever its source code looks like.

Use a placeholder that can’t be valid SQL. # is a bad placeholder in SQL precisely because it’s legal in names. With {page} instead, a forgotten replacement fails at once:

... where (:P{page}_STATUS is null or status = :P{page}_STATUS)
ORA-00907: missing right parenthesis

A loud error on the first run is much better than a filter that silently does nothing.