Automated version numbers for your APEX apps

M

Most of the projects I've worked on have this in common: "Release 1.0" as a version number. Only some projects have incremented this number, while keeping the same format. I don't like these artificial numbers, their informative value is close to zero. What I prefer instead is using the date and time of the last change. That can be easily automated, and you instantly know from which date and time the app is.

For example, instead of "Release 1.0" you would have "2025-08-13 1.10.27", which consists of the date (YYYY-MM-DD) and time (HH24:MI), with "1." in the middle to make it look less like a time and more like an old-school version number. So, the app with the version number "2025-07-02 1.8.4" was changed on July 2nd at 08:04. Clear?

As a developer, you can simply run a script when creating your patch, or even after deploying the app to the target environment, or schedule it to run as a job every hour or so.

Since the "Updated" and "Updated By" columns in APEX are empty after deploying the app through a script, this version number is an easy way to verify which version you have in each environment and with minimal effort.

Here is the query to check version numbers before and after:

SELECT
    t.application_group   AS app_group,
    t.application_id      AS app_id,
    t.application_name    AS app_name,
    t.version,
    --t.files_version,
    --t.build_status,
    t.pages,
    t.last_updated_by               AS updated_by,
    t.last_updated_on               AS updated_at,
    t.last_dependency_analyzed_at   AS analyzed_at
FROM apex_applications t
WHERE t.is_working_copy = 'No'
ORDER BY
    1, 2;

Here is the magic script:

DECLARE
    in_master_app       CONSTANT PLS_INTEGER    := NULL;  -- if you have Master app, which can change other apps
    in_app_id           CONSTANT PLS_INTEGER    := NULL;  -- if you wan to
    in_proceed          CONSTANT BOOLEAN        := TRUE;
    --
    v_apps              apex_t_varchar2;
    v_version_new       apex_applications.version%TYPE;
    v_version_old       apex_applications.version%TYPE;
    v_version_tmp       apex_applications.version%TYPE;
BEGIN
    -- prepare apps list
    v_apps := apex_t_varchar2(in_app_id);  -- you can actually change multiple apps

    -- proceed with apps
    IF in_master_app IS NOT NULL THEN
        core.create_session(USER, in_master_app);
    END IF;
    --
    FOR app_id IN VALUES OF v_apps LOOP
        IF in_master_app IS NULL THEN
            core.create_session(USER, app_id);
        END IF;

        -- get current version
        SELECT
            t.version,
            '0',
            '0'
        INTO
            v_version_old,
            v_version_new,
            v_version_tmp
        FROM apex_applications t
        WHERE t.application_id = app_id;

        -- loop over all views with app_id and date column, find the maximum
        FOR c IN (
            SELECT
                c.owner,
                c.table_name,
                c.column_name
            FROM all_views t
            JOIN all_tab_cols c
                ON c.table_name     = t.view_name
                AND c.data_type     = 'DATE'
                AND c.column_name   IN ('CREATED_ON', 'LAST_UPDATED_ON', 'UPDATED_ON')
            JOIN all_tab_cols a
                ON a.table_name     = t.view_name
                AND a.column_name   = 'APPLICATION_ID'
            WHERE t.owner           LIKE 'APEX_2%'
                AND t.view_name     LIKE 'APEX_APPL%'
            ORDER BY
                1, 2, 3
        ) LOOP
            EXECUTE IMMEDIATE
                APEX_STRING.FORMAT (
                    q'!SELECT
                      !    MAX(TO_CHAR(t.%3, 'YYYY-MM-DD HH24:MI:SS'))
                      !FROM %1.%2 t
                      !WHERE t.application_id = %4
                      !',
                    --
                    p1 => c.owner,
                    p2 => c.table_name,
                    p3 => c.column_name,
                    p4 => app_id,
                    --
                    p_prefix        => '!',
                    p_max_length    => 32767
                )
                INTO v_version_tmp;
            --
            IF v_version_tmp IS NOT NULL THEN
                v_version_new := GREATEST(v_version_new, v_version_tmp);
                --
                IF v_version_tmp > v_version_old THEN
                    DBMS_OUTPUT.PUT_LINE('APP ' || LPAD(app_id, 4) || ': ' || RPAD(c.table_name || ' ', 48, '.') || ' ' || v_version_tmp);
                END IF;
            END IF;
        END LOOP;

        -- update version number for the app
        BEGIN
            v_version_new := REPLACE(TO_CHAR(TO_DATE(v_version_new, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD fmHH24.MI'), ' ', ' 1.');
            --
            IF v_version_new != v_version_old THEN
                DBMS_OUTPUT.PUT_LINE('APP ' || LPAD(app_id, 4) || ': ' || RPAD(v_version_old, 18) || ' -> ' || v_version_new);
                IF in_proceed THEN
                    APEX_APPLICATION_ADMIN.SET_APPLICATION_VERSION (
                        p_application_id    => app_id,
                        p_version           => v_version_new
                    );
                END IF;
            END IF;
        EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE = -20987 THEN
                DBMS_OUTPUT.PUT_LINE('APP ' || app_id || ' --> ENABLE RUNTIME_API_USAGE');
            END IF;
        END;
    END LOOP;
END;
/

Tweaked and improved version is part of the Core project as update_app_version procedure.

Another note, on your application security page under the Advanced tab, you have to enable "Runtime API Usage" for the app ("Modify This Application"). If you have a Master app, you can also select "Modify Other Applications" and then just create a session for this app first and you don't have to change the other apps.