Skip to main content

Investigating APEX apps and pages

E

Especially when you come to a new environment, you might want to check existing apps. What apps are mapped to what schema, how big they are, do they use authorization schemes? Which pages are the main, which pages are not used? Which pages has forms or grids? Which pages has dynamic actions or hardcoded JavaScript? Etc. Following scripts can help you to figure out the structure of your applications/pages.


Get list of applications with some details:

SELECT
    w.workspace,
    --w.workspace_id
    a.owner,
    a.application_group             AS app_group,
    a.application_id                AS app_id,
    a.alias                         AS app_alias,
    a.application_name              AS app_name,
    a.pages,
    a.application_items             AS items,
    a.application_processes         AS processes,
    a.application_computations      AS computations,
    a.application_settings          AS settings,
    a.lists,
    a.lists_of_values               AS lovs,
    a.web_services                  AS ws,
    a.translation_messages          AS translations,
    a.build_options,
    --
    a.authorization_schemes         AS authz_schemes,
    CASE WHEN a.authentication_scheme_type != 'No Authentication' THEN a.authentication_scheme END AS authn_scheme,
    --a.availability_status,
    CASE WHEN a.db_session_init_code IS NOT NULL THEN 'Y' END       AS has_init_code,
    CASE WHEN a.db_session_cleanup_code IS NOT NULL THEN 'Y' END    AS has_cleanup,
    CASE WHEN a.friendly_url = 'Yes' THEN 'Y' END                   AS has_friendly_url,
    CASE WHEN a.debugging = 'Allowed' THEN 'Y' END                  AS has_debugging,
    CASE WHEN a.error_handling_function IS NOT NULL THEN 'Y' END    AS has_error_fn,
    --
    a.compatibility_mode,
    a.created_on                    AS created_at,
    a.last_updated_on               AS changed_at
FROM apex_applications a
JOIN apex_workspace_schemas s
    ON s.workspace_id   = a.workspace_id
    AND s.schema        = a.owner
JOIN apex_workspaces w
    ON w.workspace_id   = a.workspace_id
WHERE a.owner           NOT LIKE 'APEX%'
--WHERE a.owner           = ''
ORDER BY
    a.application_id;


Get list of pages with some details (for specific application):

WITH d AS (
    SELECT
        d.application_id,
        d.page_id,
        COUNT(*) AS count_da
    FROM apex_application_page_da d
    GROUP BY
        d.application_id,
        d.page_id
),
r AS (
    SELECT
        r.application_id,
        r.page_id,
        SUM(CASE WHEN r.source_type_plugin_name = 'NATIVE_FORM' THEN 1 ELSE 0 END)                          AS count_forms,
        SUM(CASE WHEN r.source_type_plugin_name = 'NATIVE_IG' THEN 1 ELSE 0 END)                            AS count_grids,
        SUM(CASE WHEN r.source_type_plugin_name IN ('NATIVE_IR', 'NATIVE_SQL_REPORT') THEN 1 ELSE 0 END)    AS count_reports,
        SUM(CASE WHEN r.source_type_plugin_name = 'NATIVE_JET_CHART' THEN 1 ELSE 0 END)                     AS count_charts
    FROM apex_application_page_regions r
    GROUP BY
        r.application_id,
        r.page_id
)
SELECT
    --p.workspace,
    --p.application_id,
    p.page_group,
    p.page_id,
    p.page_alias,
    p.page_name,
    --p.page_title,
    --
    NULLIF(p.regions, 0)            AS regions,
    NULLIF(r.count_reports, 0)      AS reports,
    NULLIF(r.count_grids, 0)        AS grids,
    NULLIF(p.report_columns, 0)     AS columns,
    NULLIF(r.count_charts, 0)       AS charts,
    NULLIF(r.count_forms, 0)        AS forms,
    NULLIF(p.items, 0)              AS items,
    NULLIF(p.buttons, 0)            AS buttons,
    NULLIF(p.computations, 0)       AS computations,
    NULLIF(p.processes, 0)          AS processes,
    NULLIF(p.validations, 0)        AS validations,
    NULLIF(p.branches, 0)           AS branches,
    NULLIF(d.count_da, 0)           AS d_actions,
    --
    CASE WHEN (p.javascript_code IS NOT NULL OR p.javascript_code_onload IS NOT NULL) THEN 'Y' END AS has_javascript,
    CASE WHEN p.inline_css IS NOT NULL THEN 'Y' END AS has_css,
    --
    CASE WHEN p.page_requires_authentication = 'Yes' AND p.authorization_scheme IS NOT NULL THEN 'Y' END AS has_auth,
    CASE WHEN p.page_access_protection IN ('No URL Access', 'Arguments Must Have Checksum') THEN 'Y' END AS has_protection,
    --
    p.authorization_scheme,
    p.build_option,
    CASE WHEN p.page_mode != 'Normal' THEN 'Y' END AS is_modal,
    -- check dialog_attributes + dialog_chained for modal pages
    p.page_template,
    p.page_comment,
    p.created_on            AS created_at,
    p.last_updated_on       AS changed_at
FROM apex_application_pages p
JOIN r
    ON r.application_id     = p.application_id
    AND r.page_id           = p.page_id
JOIN d
    ON d.application_id     = p.application_id
    AND d.page_id           = p.page_id
WHERE p.application_id      = &APP_ID
ORDER BY
    p.application_id,
    p.page_id;


And similar to Tracking developers activity you can check which pages are actually used by users or not:

WITH x AS (
    SELECT /*+ MATERIALIZE */
        &APP_ID AS app_id
    FROM DUAL
),
a AS (
    SELECT /*+ MATERIALIZE */
        a.application_id,
        a.page_id,
        TRUNC(a.view_date) AS view_date,
        a.page_view_type,
        a.elapsed_time,
        a.apex_user,
        a.apex_session_id
    FROM apex_workspace_activity_log a
    JOIN x
        ON x.app_id = a.application_id
),
s AS (
    SELECT /*+ MATERIALIZE */
        a.application_id,
        a.page_id,
        a.view_date,
        --
        NULLIF(SUM(CASE WHEN a.page_view_type = 'Rendering' THEN 1 ELSE 0 END), 0)      AS rendering,
        NULLIF(SUM(CASE WHEN a.page_view_type = 'Processing' THEN 1 ELSE 0 END), 0)     AS processing,
        NULLIF(SUM(CASE WHEN a.page_view_type = 'Ajax' THEN 1 ELSE 0 END), 0)           AS ajax,
        --
        COUNT(*)                            AS activity,
        COUNT(DISTINCT a.apex_user)         AS users,
        COUNT(DISTINCT a.apex_session_id)   AS sessions,
        --
        ROUND(AVG(a.elapsed_time),2)        AS avg_time,
        ROUND(MAX(a.elapsed_time), 2)       AS max_time
    FROM a
    GROUP BY
        a.application_id,
        a.page_id,
        a.view_date
    ORDER BY 1, 3 DESC
)
--SELECT * FROM s; -- check this out too
SELECT
    p.application_id,
    p.page_id,
    p.page_name,
    --
    CASE WHEN SUM(NVL(s.activity, 0)) > 0 THEN 'Y' END AS is_used,
    --
    NULLIF(SUM(NVL(s.activity, 0)), 0) AS total,
    --
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) -  0 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS today,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) -  1 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t1,       -- yesterday
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) -  2 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t2,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) -  3 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t3,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) -  4 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t4,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) -  5 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t5,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) -  6 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t6,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) -  7 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t7,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) -  8 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t8,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) -  9 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t9,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 10 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t10,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 11 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t11,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 12 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t12,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 13 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t13,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 14 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t14,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 15 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t15,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 16 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t16,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 17 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t17,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 18 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t18,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 19 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t19,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 20 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t20,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 21 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t21,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 22 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t22,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 23 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t23,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 24 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t24,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 25 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t25,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 26 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t26,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 27 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t27,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 28 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t28,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 29 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t29,
    NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 30 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t30
FROM apex_application_pages p
JOIN x
    ON x.app_id             = p.application_id
    AND p.page_id           NOT IN (0)
LEFT JOIN s
    ON s.application_id     = p.application_id
    AND s.page_id           = p.page_id
GROUP BY
    p.application_id,
    p.page_id,
    p.page_name
ORDER BY
    p.application_id,
    p.page_id;


And you can check which page is flooding your debug messages in past week:

SELECT
    d.page_id,
    --
    COUNT(DISTINCT(d.apex_user))    AS users,
    MAX(d.elapsed_time)             AS max_elapsed_time,
    MAX(d.execution_time)           AS max_execution_time,
    NULLIF(COUNT(*), 0)             AS total,               -- number of messages
    --
    NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) -  0 THEN 1 ELSE 0 END), 0) AS today,
    NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) -  1 THEN 1 ELSE 0 END), 0) AS t1,     -- yesterday
    NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) -  2 THEN 1 ELSE 0 END), 0) AS t2,
    NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) -  3 THEN 1 ELSE 0 END), 0) AS t3,
    NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) -  4 THEN 1 ELSE 0 END), 0) AS t4,
    NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) -  5 THEN 1 ELSE 0 END), 0) AS t5,
    NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) -  6 THEN 1 ELSE 0 END), 0) AS t6,
    NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) -  7 THEN 1 ELSE 0 END), 0) AS t7
FROM apex_debug_messages d
WHERE d.application_id  = &APP_ID
    AND d.page_id       IS NOT NULL
GROUP BY d.page_id
ORDER BY d.page_id;


Comments