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