Automated version numbers for your APEX apps

From:

Posting

Example:

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;

Example:

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;
/