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:
8861176758706

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.

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