Package to re initialise an existing Application Express session from SQL Developer

apex_session_utl.sql

/* Usage:

Package to get an apex session from SQL Developer (or other external tool) based on an existing session ID. 
Useful to have parameterised views return data

--Get session in SQL Dev, replacing xxxxxxxxx with your actual session
begin
  apex_session_utl.re_init_session('xxxxxxxxx');
end;

--set an item value:
begin
  apex_util.set_session_state('P1_ITEM_NAME', 'ITEM_VALUE');
end;

--verification query
select v('P1_ITEM_NAME') p1_item, v('APP_USER'), v('APP_SESSION')
from dual

*/

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