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

Current Session ID

Get the current session id using something like this:

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:

Arnoud Aten has improved on this, see more details on that (and how to build an Apex URL with checksums from PL/SQL) here:

With Apex 18.1 things got a lot simpler - check out this blog post by Marko Gorički:
Basically what you need is this:


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:

[...] 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:

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:



Source Type
Region Source



Package Body

Package Body