Skip to Main Content

Query Session State for Page & Application Items


For this query, the database user needs to have select grants on two Apex tables: I tend to use system for this, it already has the grants.

I tested this code on all the Apex versions I had available at the time: 3.2, 4.1, 4.2, 5.0, 5.1 and 18.1. Other versions may need some tinkering.

Source (among others): http://www.grassroots-oracle.com/2013/01/using-sql-to-view-apex-session-state.html

Current Session ID

Get the current session id using something like this:
6207572850690

How to Create an APEX Session in PL/SQL

Some great PL/SQL code on "How to Create an APEX Session in PL/SQL" by Martin D'Souza can be found here: http://www.talkapex.com/2012/08/how-to-create-apex-session-in-plsql

Arnoud Aten has improved on this, see more details on that (and how to build an Apex URL with checksums from PL/SQL) here: https://apex.oracle.com/pls/apex/f?p=31989:145

With Apex 18.1 things got a lot simpler - check out this blog post by Marko Gorički: http://apexbyg.blogspot.com/2018/06/apex-181-generate-url-checksum-outside.html.
Basically what you need is this:

apex_session.attach

The ATTACH procedure allows you to access an Apex session from outside Apex, and use that to query Collections and Page items. Can come in very handy
Ashish Sahay wrote a nice blog post about this subject on Ontoor Blogs.

APEX_UTIL.set_session_state may or may not commit

A very interesting article titled "APEX_UTIL.set_session_state may or may not commit" by Jeff Kemp: https://jeffkemponoracle.com/2014/02/20/apex_util-set_session_state-may-or-may-not-commit/

[...] APEX_UTIL.set_session_state will issue a COMMIT – but only if the value of the item is changed. If you happen to call set_session_state with the value that the item already has, it does nothing, and does not COMMIT. [...]

v('SESSION') not null

When using v('SESSION') or v('APP_SESSION') outside of Apex, you would expect these to be null on every occasion. Turns out they aren't - well, not always anyway. May have something to do with shared server processes - seems logical, since connections are re-used in that sort of setup - but I'm not sure.

The solution is quite simple. The session id that is returned by v('SESSION') in these cases, is not present in the apex_workspace_sessions view; as expected, since it's not a real session (anymore). So I wrote a small wrapper function that does the trick, see the code below.

Session State Protection

The official Apex 5.1 documentation on this subject: http://docs.oracle.com/database/apex-5.1/HTMDB/understanding-developer-security-best-practices.htm#HTMDB25978

Martin Giffy D'Souza wrote some very helpful posts on Session State Protection (and the related subject Page Access Protection).
You can find these here:
http://www.talkapex.com/2009/05/apex-page-access-protection-and-session/
http://www.talkapex.com/2012/11/session-state-protection-in-detail/

Code

Region

Identification
Sequence
Title
Type
Source
Source Type
Region Source

Package

Package
Name
Source

Package Body

Package Body
Name
Source