In Memory Session State - Simple Example

Not too long ago I encountered an interesting behaviour with session state, turns out not long after Dick Dral posted a very similar article that I had not yet seen.

Recently I encountered an even easier scenario.

I had a bunch of columns where I wanted to use the same prefix across all headers, but I wanted to define the value once, referring to it many times. I'm betting on the fact someone may change their mind.

Interactive Report with pivoted data

So I created a hidden item called P222_PREFIX and gave it a static source of "Ch".

Page item source

If I substitute this item as part of the column heading for each column, it will use the value found session state - not the database value, but the value based on the defined source, placed only in memory for the duration of page rendering.

Column heading

So while the output may appear as desired after the page renders, if the region is partially refreshed by hitting "Go" on an interactive report or just triggering region refresh, the prefix will disappear because there is no value for P222_PREFIX in session state once the page finishes rendering.

Desired output

To fix the problem we add a computation on the hidden item to set the value, instead of using a static source. This ensures the value is in session state for current and later usage.

An alternative solution to the task at hand might be to use Shortcuts.

In Memory Session State - Use Case

Many moons ago Anton Nielson posted about the different kinds of session state in Oracle APEX.

Different kinds?!

I remember session state was hard enough when learning, particularly coming from Oracle Forms. But how is there more than one, you may ask?

98% of the time we just need to worry about the persisted session state that we all know and love. The one we see when clicking "Session" in the developer toolbar. Essentially, this is just a normal table with a session ID, item name and value.

In Memory Session State (IMMS) is the 2% that comes and bites us occasionally. Recently I had a problem the scenario described by Anton as "2A Item Rendering". His description is a little tricky, but I think this visualisation of a simpler example will help.

IMSS Demonstration

Some page facts:

• 'before region' has item P45_BEFORE, defaulted to A
• Region 1 has condition P45_BEFORE = A 
• Region 2 (after Region 1, not visible) has condition P45_AFTER = B 
• 'after region' has item P45_AFTER, defaulted to B
• The items have no source, and are just defaulted to those values shown.
• Session state (persisted) remains empty before/after. During is another story. 

In this case, while it renders Region 1, IMSS will actually report a value of A for P45_BEFORE while rendering Region 1, hence it's displayed. Since P45_AFTER is rendered after Region 2, the value is null during the evaluation of the condition.

Therefore, the results differ by moving the location of the items relative to the component with the condition.

Unfortunately I couldn't find any support/explanation for this behaviour in the debug log, even at LEVEL9.

In my actual case, I had an item that stopped a report from being rendered until the user selected criteria and clicked submit. I did this by defining a hidden P1_SUBMITTED, defaulted to Y, and used that as the condition in the report.

This usually worked fine since the item is usually with other criteria in the Right Side region, but a new page had this item at the top of the page. So P1_SUBMITTED was treated as having a value Y while rendering my report, just like in the example above with P45_BEFORE.

Happy APEXing!

See this post for another example.

APEX 5 Source Used attribute - tolerating less mistakes

If you've upgraded to APEX 5 and find yourself with an error complaining about the 'Source Used' attribute:
APEX.SESSION_STATE.DB_COLUMN_SOURCE_USED
then you should thank APEX 5 for finding (and no longer tolerating) a logic bug in your application.

APEX 5 - correcting your applications

Track down that field and set the Source Used attribute to 'Always, replacing any existing value in session state'.

On a side note, setting the application's Compatability Mode to 4.2 will also fix the problem, but in this case certainly not the appropriate solution. But this means by setting this attribute to 5.0 on your existing applications can help find these problems in the first place.

Using SQL to view APEX session state

There are times (mobile development, multi-browser development, most Fridays) when checking out what's in session state isn't as easy as clicking a button in the developer toolbar.

One option is to utilise provided data dictionary views and a supplied API.

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

Update - this query locates values for Page Items, you can also use apex_application_items to view Application Item values. I used both in my debug page (thanks, Marko)

You could include this as a link to a popup from the navigation bar, or a region on a global page (page zero) that is conditionally displayed using a build option.

You can also interrogate session state using SQL in your favourite IDE, but there's a catch.

If you have access to objects in the APEX owner, you can run a query like this to view session state detail:

select s.remote_addr
      ,d.flow_id app_id
      ,i.display_sequence seq
      ,d.item_name, d.item_value_vc2 item_value
      ,d.item_filter
      ,d.session_state_status
      ,d.is_encrypted
      ,s.cookie the_user
      ,s.security_group_id
      ,d.flow_instance
      --,d.* 
from apex_040200.wwv_flow_data d
    ,apex_040200.wwv_flow_sessions$ s
    ,apex_application_page_db_items i
where d.flow_instance = s.id
and   i.item_id(+)    = d.item_id
--and   s.security_group_id = 100001 -- workspace group ID 
--and   item_name is not null
and   flow_instance   = 4359890239697682 -- session ID
and   flow_id < 4000 -- ignore dev builder et al
order by flow_id, i.display_sequence, d.item_name

In my local laptop I have this query handy in SQL Developer, replacing the session ID in the highlighted line to whatever is shown in my browser's address bar. If you aren't talking about your local laptop, and have a good relationship with your DBA, perhaps this could be turned into a view
CREATE VIEW apex_040200.apex_session_state AS...

Then grant SELECT on the new view to your schema owners, and create a synonym. Note, if any item is set to encrypted, you still won't be able to read the item value. This view was also particular to 4.2, it was previously d.item_value -- _vc2

At the end of the day, there is more than one way to check out session state.

Scott

ps - on a related note, here is another way to check out the alert log
Exposing the Oracle Alert Log to SQL - Neil Chandler