apex_collection.create_collection_from_query('TEST', dbms_sql_cursor_id);

create or replace package MY_APEX is
  
  LOGIN_NAME CONSTANT MP_USERS.USERNAME%TYPE := 'TEST';
  PASSWORD CONSTANT MP_USERS.PASSWORD%TYPE := 'TEST';
  APP_ID CONSTANT VARCHAR2(3) := '100';
    
  procedure login(
    pSessionId in out nocopy apex_workspace_sessions.apex_session_id%type
  );
  
  procedure logout(
    pSessionId in out nocopy apex_workspace_sessions.apex_session_id%type
  );

end MY_APEX;

/

create or replace package body MY_APEX is

  procedure login(
    pSessionId in out nocopy apex_workspace_sessions.apex_session_id%type
  ) is
    vc_arr OWA.vc_arr;
  begin
    /* ЕСЛИ HTTP СРЕДА ОТСУТСВУЕТ
     * Используется как заплатка к пакету OWA_UTIL
     */
    vc_arr(1) := 'TEST';
    OWA.init_cgi_env(vc_arr);
    
    wwv_flow_api.set_security_group_id(
      nvl(wwv_flow_application_install.get_workspace_id(),3167627337932591)
    );

    if pSessionId is null then
      pSessionId := apex_custom_auth.get_next_session_id();

      apex_custom_auth.login(
          p_uname => LOGIN_NAME,
          p_password => PASSWORD,
          p_session_id => pSessionId,
          p_app_page => APP_ID,
          p_entry_point => null,
          p_preserve_case => null
      );
    else
      declare
        l_session char(1);
      begin
        select null into l_session
          from apex_workspace_sessions s
         where apex_session_id = pSessionId;
      exception when no_data_found then
        raise_application_error(-20009, 'Invalid Session ID!');
      end;
    end if;

    apex_custom_auth.set_session_id(pSessionId);
    apex_custom_auth.set_user(LOGIN_NAME);
    apex_application.g_flow_id := APP_ID;
  end;

  procedure logout(
    pSessionId in out nocopy apex_workspace_sessions.apex_session_id%type
  ) is
  begin
    if pSessionId is not null then
      wwv_flow_api.set_security_group_id(
        wwv_flow_application_install.get_workspace_id
      );

      apex_custom_auth.set_session_id(pSessionId);
      apex_custom_auth.set_user(LOGIN_NAME);
      apex_application.g_flow_id := APP_ID;
      
      apex_custom_auth.remove_session;
      pSessionId := null;
    end if;
  end;
end MY_APEX;