Zetetic's custom oamPageSentry function for APEX 3.0

oamPageSentry-apex3.sql

CREATE OR REPLACE FUNCTION PASSPORT.oamPageSentry(pUser IN VARCHAR2 DEFAULT 'APEX_PUBLIC_USER') RETURN BOOLEAN
IS
  vUsername  VARCHAR2(512);
  vSession   NUMBER;
BEGIN
  -- extract user from HTTP header
  vUsername := UPPER(owa_util.get_cgi_env('REMOTE_USER'));
  -- extract session id
  vSession := wwv_flow_custom_auth_std.get_session_id_from_cookie;

  -- check that the executing user account is the
  -- same as the apex application user, and that
  -- a username was populated in the header
  IF USER ^= UPPER(pUser) OR vUsername IS NULL THEN
    RETURN FALSE;
  END IF;

  -- Get SessionId.
  -- Check Application Session Cookie.

  IF wwv_flow_custom_auth_std.is_session_valid THEN
    apex_application.g_instance := vSession;

    -- check requeted username matches session username
    IF vUsername = wwv_flow_custom_auth_std.get_username THEN
      wwv_flow_custom_auth.define_user_session(p_user => vUsername, p_session_id => vSession);
      RETURN TRUE;
    ELSE
      -- Unset the Session Cookie and redirect back here to take other branch.
      wwv_flow_custom_auth_std.logout(p_this_flow => v('FLOW_ID'),
        p_next_flow_page_sess => v('FLOW_ID') || ':' || NVL(v('FLOW_PAGE_ID'), 0)
        || ':' || vSession);
      -- Tell Apex Engine to quit.
      apex_application.g_unrecoverable_error := TRUE;
      RETURN FALSE;
    END IF;
  ELSE
    -- Application Session Cookie not valid --> Define a new Apex Session.
    wwv_flow_custom_auth.define_user_session(p_user => vUsername, p_session_id => wwv_flow_custom_auth.get_next_session_id);
    -- Tell Apex Engine to quit.
    apex_application.g_unrecoverable_error := TRUE;
    IF owa_util.get_cgi_env('REQUEST_METHOD') = 'GET'  THEN
      wwv_flow_custom_auth.remember_deep_link(p_url => 'f?' ||
        wwv_flow_utilities.url_decode2(owa_util.get_cgi_env('QUERY_STRING')));
    ELSE
      wwv_flow_custom_auth.remember_deep_link(p_url => 'f?p=' ||
        TO_CHAR(apex_application.g_flow_id) || ':' ||
        TO_CHAR(NVL(apex_application.g_flow_step_id, 0)) || ':' ||
        TO_CHAR(apex_application.g_instance));
    END IF;
    -- Register the Session in Apex Sessions Table, set Cookie, redirect back.
    wwv_flow_custom_auth_std.post_login(p_uname => vUsername,
      p_session_id => nv('APP_SESSION'), p_flow_page => apex_application.g_flow_id
      || ':' || NVL(apex_application.g_flow_step_id, 0));
    RETURN FALSE;
  END IF;
END oamPageSentry;
/