APEX

A Step-by-Step Implementation with Complete Scripts

In enterprise applications, data auditing is not a luxury — it’s a requirement. Understanding who changed what, when, and from where is essential for security, troubleshooting, and compliance.

Oracle APEX is a powerful low-code platform, but user activity logging and data change history are not enabled by default. In this article, I’ll walk through a complete, production-ready implementation for logging user activity and submitted form data in Oracle APEX — including all required scripts.

Step 1: Create the Log Table

We start with a centralized table called TAB_LOG. This table stores user context, request details, and a snapshot of submitted form data in JSON format.

CREATE TABLE tab_log (
    id              NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
                    PRIMARY KEY,
    username        VARCHAR2(100) NOT NULL,
    action_type     VARCHAR2(50),
    page_id         NUMBER,
    process_name    VARCHAR2(300),
    form_data_json  CLOB,
    ip_address      VARCHAR2(30),
    user_agent      VARCHAR2(2000),
    session_id      VARCHAR2(100),
    log_time        TIMESTAMP(6),
    app_id          NUMBER,
    page_title      VARCHAR2(2000),
    request_method  VARCHAR2(20)
); 

This structure is intentionally generic so it can be reused across the entire application.

Step 2: Create a Central Logging Procedure

All logging logic is encapsulated in a single stored procedure: prc_log_user_activity.

Key design principles:

• Uses an autonomous transaction
• Enriches logs with APEX metadata
• Fail-safe: logging must never break the application

CREATE OR REPLACE PROCEDURE prc_log_user_activity (
    p_username        IN VARCHAR2,
    p_action_type     IN VARCHAR2,
    p_page_id         IN NUMBER,
    p_process_name    IN VARCHAR2,
    p_form_data       IN CLOB,
    p_ip_address      IN VARCHAR2,
    p_user_agent      IN VARCHAR2,
    p_session_id      IN VARCHAR2,
    p_app_id          IN NUMBER,
    p_request_method  IN VARCHAR2
)
IS
    l_page_title VARCHAR2(2000);
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

    SELECT page_title
    INTO   l_page_title
    FROM   apex_application_pages
    WHERE  application_id = p_app_id
    AND    page_id        = p_page_id
    AND    ROWNUM = 1;

    INSERT INTO tab_log (
        username,
        action_type,
        page_id,
        process_name,
        form_data_json,
        ip_address,
        user_agent,
        session_id,
        app_id,
        log_time,
        page_title,
        request_method
    ) VALUES (
        p_username,
        p_action_type,
        p_page_id,
        p_process_name,
        p_form_data,
        p_ip_address,
        p_user_agent,
        p_session_id,
        p_app_id,
        SYSTIMESTAMP,
        l_page_title,
        p_request_method
    );

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        NULL;
END;
/ 

This procedure becomes the single entry point for logging activity.

Step 3: Create an Application Process (On Submit)

Next, create an Application Process with the following execution point:

On Submit – After Page Submission – After Computations and Validations

This ensures that:

• Only validated data is logged
• Business logic remains unaffected
• The final form state is captured

Step 4: Capture Form Data Dynamically (JSON)

The Application Process dynamically collects page item values, excludes password fields, and builds a JSON snapshot.

DECLARE
    l_json  CLOB := '{';
    l_value VARCHAR2(4000);
    l_count INTEGER := 0;
BEGIN

    FOR rec IN (
        SELECT item_name
        FROM apex_application_page_items
        WHERE application_id = :APP_ID
          AND page_id        = :APP_PAGE_ID
          AND display_as_code != 'NATIVE_PASSWORD'
    ) LOOP

        l_value := apex_util.get_session_state(rec.item_name);

        IF l_value IS NOT NULL THEN
            l_json := l_json
                      || '"'
                      || rec.item_name
                      || '":"'
                      || REPLACE(l_value, '"', '\"')
                      || '",';
            l_count := l_count + 1;
        END IF;
    END LOOP;

    IF l_count > 0 THEN
        l_json := RTRIM(l_json, ',') || '}';
    ELSE
        l_json := '{}';
    END IF;

    prc_log_user_activity (
        p_username       => :APP_USER,
        p_action_type    => :REQUEST,
        p_page_id        => :APP_PAGE_ID,
        p_process_name   => 'SUBMIT FORM',
        p_form_data      => l_json,
        p_ip_address     => owa_util.get_cgi_env('REMOTE_ADDR'),
        p_user_agent     => owa_util.get_cgi_env('HTTP_USER_AGENT'),
        p_session_id     => :APP_SESSION,
        p_app_id         => :APP_ID,
        p_request_method => owa_util.get_cgi_env('REQUEST_METHOD')
    );

END; 

This implementation:

• Automatically adapts to page changes
• Avoids hardcoding item names
• Excludes sensitive password fields
• Stores a full form snapshot as JSON

What This Approach Covers Well

Logs all form submissions Captures validated data only Excludes passwords Centralized and reusable Minimal performance impact Easy to report inside APEX

Known Limitation: Interactive Grid Editing

This solution does not capture row-level edits in Interactive Grids, because:

• IG does not use standard page items
• Changes are handled internally by APEX
• DML occurs outside the classic submit flow

Recommended Strategy for Interactive Grids

For Interactive Grids, the most reliable solution is:

• Database AFTER INSERT / UPDATE / DELETE triggers
• Capturing OLD and NEW values
• Logging changes at the database level

In real-world systems, a hybrid approach works best:

• Forms → Application Process logging
• Interactive Grids → Database triggers

Final Thoughts

This implementation provides a clean, scalable, and production-ready auditing pattern for Oracle APEX applications.

It balances flexibility, security, and performance — and most importantly, it creates trust in your data.

Because in enterprise systems, data without history is incomplete.

#orcl_apex #oracle_apex