Who did what, when, and where: logging user activity in oracle apex

Logging User Activity in Oracle APEX: Who Did What, When, and Where?

If you want to understand how your Oracle APEX application is being used, it’s essential to log who accessed which page, when, and from where. This kind of tracking is crucial for building dashboards, analyzing user behavior, and improving security.

In this guide, I’ll show you step-by-step how to implement a comprehensive user activity log in Oracle APEX. This solution records every page view, including the user, timestamp, device, browser, operating system, and IP address.

Even if you’re new to APEX, you’ll find this approach easy to follow and adapt to your own projects!

1. Create the User Activity Log Table

First, we need a table to store all user activity.
Here’s the SQL code (run this in SQL Workshop > SQL Commands):

 

SQL

xxxxxxxxxx

19

 

1

-- Drop existing table

2

DROP TABLE USER_ACTIVITY_LOG CASCADE CONSTRAINTS;

3

​

4

-- Create new slim version

5

CREATE TABLE USER_ACTIVITY_LOG (

6

    USAL_id          NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

7

    USAL_app_id      NUMBER,                -- APEX Application ID

8

    USAL_page_id     NUMBER,                -- APEX Page ID

9

    USAL_session_id  VARCHAR2(4000),        -- Session ID

10

    USAL_username    VARCHAR2(4000),        -- Username (APP_USER)

11

    USAL_event_type  VARCHAR2(4000),        -- Event type (e.g. PAGE_VIEW, LOGIN)

12

    USAL_access_ts   TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL, -- Access timestamp

13

    USAL_ip_address  VARCHAR2(4000),        -- Client IP

14

    USAL_device_type VARCHAR2(4000),        -- Device (Mobile, Desktop, Tablet)

15

    USAL_browser     VARCHAR2(4000),        -- Browser name

16

    USAL_os          VARCHAR2(4000),        -- Operating System

17

    USAL_created_by  VARCHAR2(4000) DEFAULT SYS_CONTEXT('USERENV','SESSION_USER'),

18

    USAL_created     TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL

19

);

Tip:
You can find SQL Workshop in the Oracle APEX menu. Use the “SQL Commands” or “SQL Scripts” section to run the statements above.

2. Create the Logging Procedure

Next, we’ll create a PL/SQL procedure that collects all the relevant information and writes it to our log table.
This procedure can be created in SQL Workshop > SQL Commands as well.

 

SQL

xxxxxxxxxx

76

 

1

CREATE OR REPLACE PROCEDURE pr_log_user_activity AS

2

    v_app_id     NUMBER       := v('APP_ID');

3

    v_page_id    NUMBER       := v('APP_PAGE_ID');

4

    v_session_id VARCHAR2(4000) := v('APP_SESSION');

5

    v_user       VARCHAR2(4000) := NVL(v('APP_USER'),'GUEST');

6

    v_ip         VARCHAR2(4000);

7

    v_agent      VARCHAR2(4000);

8

    v_device     VARCHAR2(4000);

9

    v_browser    VARCHAR2(4000);

10

    v_os         VARCHAR2(4000);

11

BEGIN

12

    -- IP

13

    v_ip := owa_util.get_cgi_env('X-Forwarded-For');

14

    IF v_ip IS NULL THEN

15

        v_ip := owa_util.get_cgi_env('REMOTE_ADDR');

16

    END IF;

17

​

18

    -- User Agent

19

    v_agent := owa_util.get_cgi_env('HTTP_USER_AGENT');

20

​

21

    -- Device

22

    v_device := CASE

23

                    WHEN v_agent LIKE '%Mobile%' THEN 'Mobile'

24

                    WHEN v_agent LIKE '%Tablet%' THEN 'Tablet'

25

                    ELSE 'Desktop'

26

                END;

27

​

28

    -- Browser

29

    v_browser := CASE

30

                     WHEN v_agent LIKE '%Chrome%' AND v_agent NOT LIKE '%Edg%' THEN 'Chrome'

31

                     WHEN v_agent LIKE '%Safari%' AND v_agent NOT LIKE '%Chrome%' THEN 'Safari'

32

                     WHEN v_agent LIKE '%Firefox%' THEN 'Firefox'

33

                     WHEN v_agent LIKE '%Edg%' THEN 'Edge'

34

                     WHEN v_agent LIKE '%Opera%' OR v_agent LIKE '%OPR%' THEN 'Opera'

35

                     ELSE 'Unknown'

36

                 END;

37

​

38

    -- Operating System

39

    v_os := CASE

40

                WHEN v_agent LIKE '%Windows%' THEN 'Windows'

41

                WHEN v_agent LIKE '%Android%' THEN 'Android'

42

                WHEN v_agent LIKE '%iPhone%' OR v_agent LIKE '%iPad%' THEN 'iOS'

43

                WHEN v_agent LIKE '%Mac OS%' OR v_agent LIKE '%Macintosh%' THEN 'MacOS'

44

                WHEN v_agent LIKE '%Linux%' THEN 'Linux'

45

                ELSE 'Unknown'

46

            END;

47

​

48

    -- Insert

49

    INSERT INTO USER_ACTIVITY_LOG (

50

        USAL_app_id,

51

        USAL_page_id,

52

        USAL_session_id,

53

        USAL_username,

54

        USAL_event_type,

55

        USAL_access_ts,

56

        USAL_ip_address,

57

        USAL_device_type,

58

        USAL_browser,

59

        USAL_os

60

    ) VALUES (

61

        v_app_id,

62

        v_page_id,

63

        v_session_id,

64

        v_user,

65

        'PAGE_VIEW',

66

        SYSTIMESTAMP,

67

        v_ip,

68

        v_device,

69

        v_browser,

70

        v_os

71

    );

72

EXCEPTION

73

    WHEN OTHERS THEN

74

        NULL;

75

END pr_log_user_activity;

76

/

Tip:
After running this code, you’ll find your procedure under “SQL Workshop > Object Browser > Procedures”.

3. Add an Application Process for Logging

To ensure every page view is logged, add an Application Process in your APEX app:

• Go to your application in the APEX App Builder.
• In the left menu, click Shared Components.
• Under Logic, click Application Processes.
• Click Create and enter:
• Name: user log of the application process
• Point: On Load: Before Header
• Sequence: 10 (so it runs first)
• Type: PL/SQL Code

Paste this code into the process:

 

Ruby

xxxxxxxxxx

 

1

BEGIN

2

    log_user_activity;

3

END;

Tip:
This process will run automatically on every page load, capturing all the information you need.

4. What’s Next? How to Use Your Log Data

Now, every page visit is recorded in your USER_ACTIVITY_LOG table.
You can use this data to:

• Build dashboards showing user activity, popular pages, device/browser breakdowns, etc.
• Analyze trends, such as peak usage times or most active users.
• Troubleshoot issues by tracking user sessions and access patterns.

Where to find your data:
Go to SQL Workshop > Object Browser, select your table, and click “Data” to see the logs.

Extra Tips for Beginners

• If you’re new to Oracle APEX, explore the App Builder and SQL Workshop menus to get familiar with where to create tables, procedures, and processes.
• You can export your log data to Excel or use APEX’s built-in chart and report components to visualize your logs.
• For more advanced tracking, you can add custom event types (e.g., LOGIN, LOGOUT) by modifying the procedure.

• Test your logging: Visit different pages in your app and check if the logs are written correctly.
• Build your own dashboards: Use the logged data to create interactive reports and visualizations in APEX.
• Share your results: Show your team or stakeholders how users interact with your application.

With this approach, you’ll have a solid foundation for user analytics in any Oracle APEX project. Happy logging!

 {fullWidth}