prepare_url outside Apex
Description

Package specification

  ...
  -- Checksum types
  c_public         constant varchar2( 1 ) := '1';
  c_private        constant varchar2( 1 ) := '2';
  ...
  --
  function prepare_url
    ( p_url            varchar2
    , p_url_charset    varchar2                                   default null
    , p_checksum_type  varchar2                                   default null
    , p_app_user       apex_workspace_activity_log.apex_user%type default null
    )
  return varchar2
  ;
  --
  ...

Package body

  ...
  -- Private constant declarations
  c_package   constant varchar2( 100 )  := lower( $$plsql_unit ); -- for debug reasons
  ...
function apex_v_session
  return apex_workspace_sessions.apex_session_id%type
  is
  -- Check whether v( 'SESSION') contains a valid session.
  --
  l_result apex_workspace_sessions.apex_session_id%type;
  --
begin
  --
  select    max( wsn.apex_session_id )
  into      l_result
  from      apex_workspace_sessions    wsn
  where     wsn.apex_session_id = v( 'SESSION' );
  --
  return l_result;
  --
exception
  when others
  then
    raise_application_error( -20000, sqlerrm );
  -- 
end;
--
function prepare_url
    ( p_url            varchar2
    , p_url_charset    varchar2                                   default null
    , p_checksum_type  varchar2                                   default null
    , p_app_user       apex_workspace_activity_log.apex_user%type default null
    )
  return varchar2
  is
    --
    c_proc            constant varchar2( 100 ) := c_package || '.prepare_url'; -- for debug reasons
    --
    c_url_start       constant varchar2( 100 ) := 'f?p=';
    c_url_start_len   constant number          := length( c_url_start );
    --
    l_vc_arr2         apex_application_global.vc_arr2;
    l_app_id          number;
    l_page_id         number;
    --
    l_cgivar_name     owa.vc_arr;
    l_cgivar_val      owa.vc_arr;
    --
    l_workspace_id    apex_applications.workspace_id%type;
    l_app_user        apex_workspace_activity_log.apex_user%type;
    --
    l_url             varchar2( 32000 );
    --
  begin
    --
    l_vc_arr2 := apex_util.string_to_table( p_url );
    --
    if apex_v_session is null                                               -- not in a Apex session?
    and lower( substr( l_vc_arr2( 1 ), 1, c_url_start_len ) ) = c_url_start -- 'f?p=' format?
    and (
          ( p_checksum_type = c_public )
        or
          ( p_checksum_type = c_private and p_app_user is not null )
        )
    then
      --
      l_app_id  := substr( l_vc_arr2( 1 ), c_url_start_len + 1 );
      l_page_id := l_vc_arr2( 2 );
      --
      --  get workspace id by application id
      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 ); 
      --
      -- set up apex session vars
      apex_application.g_instance     := apex_custom_auth.get_next_session_id; 
      apex_application.g_flow_id      := l_app_id; 
      apex_application.g_flow_step_id := l_page_id; 
      --
      -- set up cgi environment
      htp.init;
      --
      l_cgivar_name( 1 ) := 'REQUEST_PROTOCOL';
      l_cgivar_val( 1 )  := 'HTTP';
      --  
      owa.init_cgi_env
        ( num_params     => l_cgivar_name.count
        , param_name     => l_cgivar_name
        , param_val      => l_cgivar_val 
        ); 
      --
      -- "login"
      l_app_user := nvl( p_app_user, 'DUMMY' );
      --
      apex_custom_auth.define_user_session
        ( p_user         => l_app_user
        , p_session_id   => apex_application.g_instance
        );
      --
      apex_custom_auth.post_login
        ( p_uname        => l_app_user
        , p_session_id   => apex_application.g_instance
        , p_app_page     => l_app_id || ':' || l_page_id
        ); 
      --
      -- set array of protected page IDs for session state protection
      apex_application.g_protected_page_ids( 1 ) := l_page_id;
      --
      l_url := apex_util.prepare_url( p_url, p_url_charset, p_checksum_type );
      --
      -- remove dummy session
      apex_custom_auth.remove_session;
      apex_application.g_instance := null;
      --
    else
      --
      l_url := apex_util.prepare_url( p_url, p_url_charset, p_checksum_type );
      --
    end if;
    --
    return l_url;
    --
  exception
    when others
    then
      return apex_util.prepare_url( p_url, p_url_charset, p_checksum_type );
    --
  end;
  --
  ...