Monday, July 11, 2016

Substitution Strings for IR Headers

The other day I helped a user on the Oracle APEX discussion forum with a question which led to an interesting question.  The user was using substitution variables for their column headers in an Interactive Report(IR).

Here's a quick example if you'd like to try it out yourself.

1. Create a hidden item with the following properties (The static value will be our header value):

2. Create an IR region on any table in your schema.  Edit your IR report attributes and set the heading value for one of your columns to the substitution string of your hidden field:

3. Run your report and you should see something like the following:

Everything appears fine until you click on the column header and try to perform any try of built-in IR action such as sorting or filtering.

4. Click on a sort icon and the header disappears.

After doing a quick search, I couldn't really find anything on this topic.  I thought about it for a bit and decided that it was probably a session state issue.  Luckily since IR regions do AJAX requests they allow you to specify page items to submit to session on refresh.  If you add your hidden item's name to the "Page Items to Submit" attribute, the header no longer disappears.

Here's the interesting question that it led to...

Why does the header value appear when the page first loads and not when the region is refreshed?

This is a topic that I stumbled upon while trying to create a session state demo.  In my demo application I use an APEX view called apex_application_page_items to fetch the current values of page items on the current page.  Let's add an IR region querying this view to further explore this topic.

5. Add a new IR region to your page using the following query:

select item_name, component_comment
      ,apex_util.get_session_state(item_name) session_value
from  apex_application_page_items
where application_id = :APP_ID
and   page_id        = :APP_PAGE_ID

Run your page in a new session and you'll see some interesting results:

We can see that the view is reporting that our hidden item's value is in session state.  However, if you click on the "Go" button of your session state report, you'll see that your hidden page item's value really is not saved in session state.

So what's going on?  I can't tell you exactly why because I'm just theorizing, but maybe someone from the development team can chime in on the topic.  However, this is what I think.

When a page loads you have access to the value of page items for various things like your queries, default values, etc...  In our case, we can set the value of our column heading using the value of a page item.  However, when a page is done loading the default values, static values, and the values used from an automatic row fetch process(ARF) are not set in session state.  So how are they available to our components on page load?  I think that during the page lifecycle of an initial page load, these values are actually set in session state before the page components are loaded, then reset when the page is finally done loading.  That's why our region reporting page item values from the apex_application_page_items view displays values for page items in session state, but when refreshed, it shows that the same items have no value in session state.

Clear as mud?  Thought so...


  1. Hi Paul,

    See the following for an explanation of what's happening:


    1. Dan,

      Thanks for the link. It's a nice explanation for what I was trying to describe.


  2. I note in 2020 points 1-4 are still valid in 18.1. I had to do a lot of searching to finally work out why my column headings were disappearing. Thanks Paul!