APEXtras

Page Alias APEX Application Computation (from the
Oracle Quirks blog)

SELECT page_alias
FROM apex_application_pages
WHERE application_id = :APP_ID
AND page_id = :APP_PAGE_ID

Page Visit Logging APEX Application Process

DECLARE
  l_activity_name USER_ACTIVITY.ACTIVITY_NAME%TYPE;
  l_activity_detail USER_ACTIVITY.ACTIVITY_DETAIL%TYPE;
  l_additional_information USER_ACTIVITY.ADDITIONAL_INFORMATION%TYPE;
BEGIN
  -- We keep USER_ID, QUESTIONNAIRE_ID, and SCREEN_ID in application level variables.
  -- USER_ID is set at login, QUESTIONNAIRE_ID and SCREEN_ID are set each time the
  -- questionnaire page loads
  IF (UPPER(NVL(:APP_PAGE_ALIAS,'NULL')) = 'QUESTIONNAIRES') THEN
    l_activity_name   := 'Questionnaire ID: ' || TO_CHAR(:QUESTIONNAIRE_ID);
    l_activity_detail := 'Screen ID: '        || TO_CHAR(:SCREEN_ID);
    --Record anything else you need here in l_additional_information
    -- ELSIF (UPPER(NVL(:APP_PAGE_ALIAS,'NULL')) = 'ANOTHERPAGE') THEN
    -- Add more conditional sections as needed for other page aliases
  END IF;
  IF (:USER_ID IS NOT NULL) THEN
    APEXTRAS_PAGE_VISIT_LOGGING.UserActivityAdd(
        p_user_id                 => :USER_ID,
        p_apex_page_alias         => :APP_PAGE_ALIAS,
        p_apex_page_id            => :APP_PAGE_ID,
        p_activity_name           => l_activity_name,
        p_activity_detail         => l_activity_detail,
        p_additional_information  => l_additional_information);
  END IF;
END;

Page Visit Log Table

create table USER_ACTIVITY
(
  USER_ID                NUMBER not null,
  APEX_PAGE_ID           NUMBER not null,
  APEX_PAGE_ALIAS        VARCHAR2(100) not null,
  ACTIVITY_NAME          VARCHAR2(100),
  ACTIVITY_DETAIL        VARCHAR2(100),
  ACTIVITY_DATE          DATE default SYSDATE,
  ADDITIONAL_INFORMATION VARCHAR2(1000)
);

Page Visit Logging PL/SQL Package

CREATE OR REPLACE PACKAGE APEXTRAS_PAGE_VISIT_LOGGING is
  PROCEDURE UserActivityAdd(p_user_id                IN NUMBER,
                            p_apex_page_alias        IN USER_ACTIVITY.APEX_PAGE_ALIAS%TYPE,
                            p_apex_page_id           IN USER_ACTIVITY.APEX_PAGE_ID%TYPE,
                            p_activity_name          IN USER_ACTIVITY.ACTIVITY_NAME%TYPE,
                            p_activity_detail        IN USER_ACTIVITY.ACTIVITY_DETAIL%TYPE := NULL,
                            p_additional_information IN USER_ACTIVITY.ADDITIONAL_INFORMATION%TYPE := NULL);
END APEXTRAS_PAGE_VISIT_LOGGING;
/
CREATE OR REPLACE package BODY APEXTRAS_PAGE_VISIT_LOGGING is
  PROCEDURE UserActivityAdd(p_user_id                IN NUMBER,
                            p_apex_page_alias        IN USER_ACTIVITY.APEX_PAGE_ALIAS%TYPE,
                            p_apex_page_id           IN USER_ACTIVITY.APEX_PAGE_ID%TYPE,
                            p_activity_name          IN USER_ACTIVITY.ACTIVITY_NAME%TYPE,
                            p_activity_detail        IN USER_ACTIVITY.ACTIVITY_DETAIL%TYPE := NULL,
                            p_additional_information IN USER_ACTIVITY.ADDITIONAL_INFORMATION%TYPE := NULL) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO user_activity
    FIELDS
      (USER_ID,
       APEX_PAGE_ALIAS,
       APEX_PAGE_ID,
       ACTIVITY_NAME,
       ACTIVITY_DETAIL,
       ADDITIONAL_INFORMATION)
    VALUES
      (p_user_id,
       p_apex_page_alias,
       p_apex_page_id,
       p_activity_name,
       p_activity_detail,
       p_additional_information);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
-- Log error; we use error logging to a table based on ideas and example code from
-- Steve Feuerstein's "Oracle PL/SQL Programming" - see http://examples.oreilly.com/oraclep4/
      ROLLBACK;
  END;
END APEXTRAS_PAGE_VISIT_LOGGING;
/

Back to Page Visit Logging