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.

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!