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