Debugging parameterised views outside of apex
Recently I've been working on a project that had some views that needed to reference some session state information, which uses the ever too familiar v function:
select *
from some_table
where some_item = v('P1_SOME_ITEM')
Since I work extensively in SQL Developer, when I'm debugging, it becomes a bit more difficult, because we are outside the context of your apex session, our views data comes back empty.
One solution I've come up with to help with this is using some un-documented procedures to create an apex session outside the apex scope. Actually, I can't take the credit, I stole the code from @martindsouza's blog with a few minor adjustments.
I've also place a copy of this code in a github gist for better readability: https://gist.github.com/trent-/1d02da19be85f46030ab
So, with this package in place, you can grab your session identifier from your apex URL and issue that following command:
This will give you immediate access to items like APP_USER and APP_SESSION. You should be able to set other items with apex_util.set_session_state.
Any views that reference this item should now return data! One gotcha I just noticed, setting an item value in SQL Developer will reflect back into your web session, but setting an item value in your web session doesn't reflect back in SQL Developer.
There may be a better way to handle this, but hope this helps!
select *
from some_table
where some_item = v('P1_SOME_ITEM')
Since I work extensively in SQL Developer, when I'm debugging, it becomes a bit more difficult, because we are outside the context of your apex session, our views data comes back empty.
One solution I've come up with to help with this is using some un-documented procedures to create an apex session outside the apex scope. Actually, I can't take the credit, I stole the code from @martindsouza's blog with a few minor adjustments.
create or replace package apex_session_utl as procedure re_init_session( p_session_id in apex_workspace_sessions.apex_session_id%type); function get_session_username( p_session_id in apex_workspace_sessions.apex_session_id%type) return apex_workspace_sessions.user_name%type; function get_session_application( p_session_id in apex_workspace_sessions.apex_session_id%type) return apex_workspace_activity_log.application_id%type; end apex_session_utl; / create or replace PACKAGE BODY apex_session_utl AS /* Example used from: http://www.talkapex.com/2012/08/how-to-create-apex-session-in-plsql.html */ procedure re_init_session( p_session_id in apex_workspace_sessions.apex_session_id%type) as l_workspace_id apex_applications.workspace_id%type; l_cgivar_name owa.vc_arr; l_cgivar_val owa.vc_arr; l_app_id NUMBER; begin htp.init; l_app_id := get_session_application(p_session_id); l_cgivar_name(1) := 'REQUEST_PROTOCOL'; l_cgivar_val(1) := 'HTTP'; owa.init_cgi_env( num_params => 1 , param_name => l_cgivar_name , param_val => l_cgivar_val); select workspace_id into l_workspace_id from apex_applications where application_id = l_app_id; wwv_flow_api.set_security_group_id (l_workspace_id); apex_application.g_instance := 1; apex_application.g_flow_id := l_app_id; apex_application.g_flow_step_id := 1; apex_custom_auth.post_login( p_uname => get_session_username(p_session_id) , p_session_id => NULL , p_app_page => apex_application.g_flow_id || ':' || 1); apex_custom_auth.set_session_id( p_session_id => p_session_id); end re_init_session; function get_session_username( p_session_id in apex_workspace_sessions.apex_session_id%type) return apex_workspace_sessions.user_name%type as l_user_name apex_workspace_sessions.user_name%type; begin select user_name into l_user_name from apex_workspace_sessions where apex_session_id = p_session_id; return l_user_name; end get_session_username; function get_session_application( p_session_id in apex_workspace_sessions.apex_session_id%type) return apex_workspace_activity_log.application_id%type as l_application_id apex_workspace_activity_log.application_id%type; begin select distinct application_id into l_application_id from ( select application_id , dense_Rank() over (order by view_date desc) ranked from apex_workspace_activity_log where apex_session_id = p_session_id and application_schema_owner not like 'APEX_%' ) where ranked=1; return l_application_id; end get_session_application; END apex_session_utl; /
I've also place a copy of this code in a github gist for better readability: https://gist.github.com/trent-/1d02da19be85f46030ab
So, with this package in place, you can grab your session identifier from your apex URL and issue that following command:
begin apex_session_utl.re_init_session('xxxxxxxxxxx'); end;
This will give you immediate access to items like APP_USER and APP_SESSION. You should be able to set other items with apex_util.set_session_state.
begin apex_util.set_session_State('P1_SOME_ITEM', 2); end;
Any views that reference this item should now return data! One gotcha I just noticed, setting an item value in SQL Developer will reflect back into your web session, but setting an item value in your web session doesn't reflect back in SQL Developer.
There may be a better way to handle this, but hope this helps!