Tale of a strange quirk in apex session state initialization

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Today was a good day because i finally managed to allocate some time and get to the bottom of an old standing issue that I've been encountering at intermittent times during the last five or six years spent developing applications with Apex:

how come that sometimes page item initialization doesn't *always* work as advertised?

In brief, items, page and region items alike, come with a source value attribute that may contain an initialization value. This value can be set in a variety of ways, most common being a static value, a query or a PL/SQL expression.
Now, given Apex defaults, the most frequent usage pattern for an item is to define a source value of type static assignment to be used only when current value in session state is null.

For instance, say we have a region containing an item called P20_MAX_ROWS and we want to initialize it to 11.


Now, say we have a report in a nearby region based on a query like this:

select page_name
from apex_application_pages
where application_id = :APP_ID
and rownum <= :P20_MAX_ROWS

I guess that everybody would expect to see a report displaying a maximum of 11 rows (or less).
What you get instead is an empty report.

In other words, if you have a report containing an item belonging to a different region and this item is used in the WHERE clause, it's very likely that you get an inconsistent or empty report (it depends on the WHERE conditions of course) upon the first loading of the page. Subsequent page loads may not be affected if the page is submitted and session state is consequently updated, hence it's easy to be puzzled by this behavior.

This behavior seems to be "consistent" throughout various Apex releases, including the most recent one, 3.2.1, as shown in the linked test page.

It's interesting to note that:

1. the type of source value (static, pl/sql expression or sql query) doesn't seem to make any difference.
2. if we relocate the item inside the report region, the initialization seems to work just fine and the report is generated correctly.

If we cannot relocate the item to the report region, then another workaround is to add a before header computation for initializing the item(s).

This is the way I've always dealt with this problem until now, even without knowing exactly what was wrong with item initialization, but this isn't always the best approach, it's much better to know how and when a certain feature will work or not.