Function to check if an Oracle APEX session got cloned

is_apex_session_cloned.sql

CREATE OR REPLACE FUNCTION is_apex_session_cloned RETURN BOOLEAN IS
  --
  l_referer              VARCHAR2(4000);
  l_referer_query_string VARCHAR2(4000);
  l_session_before       NUMBER;
  l_bool                 BOOLEAN := FALSE;
  -- cursor to find session id of referer (page before)
  -- 3rd splitted part holds APEX session id
  CURSOR l_cur_session_before IS
    SELECT to_number(referer_tab.column_value) AS session_before
      FROM (SELECT column_value,
                   rownum AS row_num
              FROM TABLE(apex_string.split(l_referer_query_string,
                                           ':'))) referer_tab
     WHERE referer_tab.row_num = 3;
  --
BEGIN
  -- get referer from cgi_env
  l_referer              := owa_util.get_cgi_env('HTTP_REFERER');
  l_referer_query_string := substr(l_referer,
                                   instr(l_referer,
                                         'f?p=') + 1,
                                   length(l_referer));
  -- only process if referer is the same APEX app
  IF l_referer LIKE '%f?p=' || v('APP_ID') || ':%' THEN
    -- fetch session before cursor
    OPEN l_cur_session_before;
    FETCH l_cur_session_before
      INTO l_session_before;
    CLOSE l_cur_session_before;
    -- if session is different to session before --> session cloned
    IF l_session_before IS NOT NULL THEN
      IF l_session_before != nv('APP_SESSION') THEN
        l_bool := TRUE;
      END IF;
    END IF;
  END IF;
  --
  RETURN l_bool;
  --
END is_apex_session_cloned;