For this query, the database user needs to have select grants on two Apex tables: grant select on apex_240100.wwv_flow_data to db_user; grant select on apex_240100.wwv_flow_sessions$ to db_user; I tend to use system for this, it already has the grants.
select d.flow_id as app_id , pi.page_id , i.display_sequence , d.item_name , d.item_value_vc2 , pi.display_sequence , d.item_filter , d.session_state_status , d.is_encrypted , s.remote_addr , s.cookie_value from apex_240100.wwv_flow_data d , apex_240100.wwv_flow_sessions$ s , apex_application_page_db_items i , apex_application_page_items pi where d.flow_instance = s.id and d.item_id = i.item_id (+) and d.item_id = pi.item_id (+) and d.flow_instance = v('APP_SESSION') -- your session ID and d.flow_id not between 4000 and 4999 -- ignore dev builder et al order by d.flow_id , pi.page_id nulls first , d.item_name nulls first
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

Get the current session id using something like this: select v('APP_SESSION') from dual;

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.create_session(p_app_id => 25384, p_page_id => 1, p_username => 'USERNAME');

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.

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

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.

function apex_v_session return apex_workspace_sessions.apex_session_id%type is v_result apex_workspace_sessions.apex_session_id%type; begin select max(t.apex_session_id) into v_result from apex_workspace_sessions t where t.apex_session_id = v('SESSION') ; return v_result; exception when others then raise_application_error(-20000, sqlerrm); end apex_v_session;

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/

Region

Identification
	Sequence	10
	Title	Query Session State for Page & Application Items
	Type	PL/SQL
Source
	Source Type	PLSQL_PROCEDURE
	Region Source	htp.p ( ted_p1029.session_state_query ( p_apex_version => :P1029_Q_APEX_VERSION , p_session_id => :P1029_Q_SESSION_ID ) ) ;

Package

Package
	Name	TED_P1029
	Source	package ted_p1029 is function session_state_query ( p_apex_version in varchar2 , p_session_id in varchar2 := null ) return varchar2 ; end ted_p1029;

Package Body

Package Body
	Name	TED_P1029
	Source	package body ted_p1029 is c_lf constant varchar2(2) := chr(13) || chr(10); function session_state_query ( p_apex_version in varchar2 , p_session_id in varchar2 := null ) return varchar2 is v_result varchar2(32767); v_version_1 pls_integer; v_version_2 pls_integer; v_version number; v_apex_schema varchar2(20); begin v_version_1 := substr( p_apex_version, 1, instr(p_apex_version,'.')-1 ); v_version_2 := substr( p_apex_version, -1, instr(p_apex_version,'.')+1 ); v_version := v_version_1 + (v_version_2 / power(10, length(trim(v_version_2)))); -- looks strange but will also work for e.g. Apex version 37.82, without NLS problems v_apex_schema := 'apex_' || to_char( v_version_1, 'fm00' ) || to_char( v_version_2, 'fm00' ) || '00' ; v_result := '<br/>' || c_lf || 'For this query, the database user needs to have select grants on two Apex tables:' || c_lf || '<textarea class="textarea codemirror-sql">grant select on ' || v_apex_schema || '.wwv_flow_data to db_user;' || c_lf || 'grant select on ' || v_apex_schema || '.wwv_flow_sessions$ to db_user;</textarea>' || c_lf || 'I tend to use system for this, it already has the grants.<br/>' || c_lf || c_lf || '<textarea class="textarea codemirror-plsql">select d.flow_id as app_id' || c_lf || ', pi.page_id' || c_lf || ', i.display_sequence' || c_lf || ', d.item_name' || c_lf || ( case when v_version >= 4.2 then ', d.item_value_vc2' || c_lf else ', d.item_value' || c_lf end ) || ', pi.display_sequence' || c_lf || ', d.item_filter' || c_lf || ', d.session_state_status' || c_lf || ', d.is_encrypted' || c_lf || ', s.remote_addr' || c_lf || ( case when v_version_1 >= 5 then ', s.cookie_value' || c_lf else ', s.cookie' || c_lf end ) || 'from ' || v_apex_schema || '.wwv_flow_data d' || c_lf || ', ' || v_apex_schema || '.wwv_flow_sessions$ s' || c_lf || ', apex_application_page_db_items i' || c_lf || ', apex_application_page_items pi' || c_lf || 'where d.flow_instance = s.id' || c_lf || 'and d.item_id = i.item_id (+)' || c_lf || 'and d.item_id = pi.item_id (+)' || c_lf || 'and d.flow_instance = ' || nvl(p_session_id, 'v(''APP_SESSION'')') || ' -- your session ID' || c_lf || 'and d.flow_id not between 4000 and 4999 -- ignore dev builder et al' || c_lf || 'order by d.flow_id' || c_lf || ', pi.page_id nulls first' || c_lf || ', d.item_name nulls first</textarea>' || c_lf || '<br/>' || c_lf || '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.<br/>' || c_lf || '<br/>' || c_lf || 'Source (among others): <a href="http://www.grassroots-oracle.com/2013/01/using-sql-to-view-apex-session-state.html">http://www.grassroots-oracle.com/2013/01/using-sql-to-view-apex-session-state.html</a>' || c_lf ; return v_result; end session_state_query; end ted_p1029;