Apex Qa

Oracle APEX Quality Assurance

This skill defines quality standards for Oracle APEX applications. It covers the MVC pattern, page design rules, component naming conventions, shared component usage, and automated checks. It does not cover PL/SQL formatting or code quality — those belong to the plsql-formatter and plsql-code-quality skills respectively.

The goal is to keep APEX applications maintainable, consistent, and debuggable. Most of the rules below exist because violating them creates problems that are invisible until production — missing auth schemes, scattered inline SQL, unnamed components that nobody can trace.

Stamp

On success, run: python3 /Users/dobby/Library/CloudStorage/Dropbox/BRAIN/AI/SCRIPTS/skills_log.py stamp apex-qa

MVC Pattern in APEX

The Model-View-Controller pattern is the foundation of a clean APEX application. It promotes separation between business logic and the UI layer.

• Model = tables and views. They hold and expose your data.
• View = APEX pages, JavaScript, CSS. The UI layer renders data and captures user input, but contains no business logic.
• Controller = packages, triggers, jobs. They hold all business logic.

The rule is simple: no logic on pages. Queries belong in views. Processes belong in package procedures. Validations and conditions belong in package functions. When logic lives in the database, it is governed, versioned, testable, reusable, and visible in dependency trees.

Why this matters:

• Catch bugs early. Database objects produce compilation warnings and show invalidated dependencies. Inline SQL on a page fails silently until a user hits it in production.
• Readable diffs. APEX exports are cluttered. A view or package diff is clean and reviewable.
• Reusable code. You can reuse a view or a function across pages. You cannot reuse a query hardcoded on a region.
• Easier impact analysis. Database dependencies tell you what breaks when a table or column changes. Inline SQL on pages is invisible to the dependency graph.
• Expert access. A DBA can tune a view. Asking them to find and fix a query buried on page 347 region 2 is a different story.
• Testable. You can write unit tests for packages and validate views with queries. You cannot unit-test inline code on a page.

Dynamic Views for APEX Regions

The most common objection to using views on APEX regions is that views cannot reference bind variables (:P100_MONTH). This is solved by accessing page items via APEX_UTIL.GET_SESSION_STATE inside a materialized WITH clause.

The Pattern

CREATE OR REPLACE VIEW p100_calendar_v AS
WITH x AS (
    SELECT /*+ MATERIALIZE */
        APEX_UTIL.GET_SESSION_STATE('P100_MONTH')   AS month
    FROM DUAL
)
SELECT
    d.month,
    d.week,
    ...
FROM days d
JOIN x
    ON x.month = d.month
ORDER BY 1, 2;

The /*+ MATERIALIZE */ hint is critical. Without it, APEX_UTIL.GET_SESSION_STATE is called for every row in the query, causing serious performance issues. With the hint, the WITH clause is evaluated once and the result is reused.

The APEX region source then becomes just a view reference — no inline SQL, no WHERE filters split between the view and the region.

Reusable Base Views

When multiple pages share similar filters (e.g. the same date range, customer, or status filter on pages 100, 200, and 300), create a single base view that resolves the correct page item dynamically based on the calling page:

CREATE OR REPLACE VIEW report_base_v AS
WITH x AS (
    SELECT /*+ MATERIALIZE */
        core.app.get_item('$MONTH')     AS month,
        core.app.get_item('$STATUS')    AS status
    FROM DUAL
)
SELECT
    ...
FROM orders o
JOIN x
    ON (x.month   = o.order_month   OR x.month  IS NULL)
    AND (x.status  = o.order_status  OR x.status IS NULL)
ORDER BY 1;

The get_item('$MONTH') function replaces $ with the current page prefix (P100_, P200_, etc.) by reading APEX_APPLICATION.G_FLOW_STEP_ID. This means report_base_v works on any page — adding a new filter means changing one view, not three region queries.

Testing Dynamic Views

You can test these views from PL/SQL outside of APEX by setting session state first:

APEX_UTIL.SET_SESSION_STATE('P100_MONTH', '03/2024');
SELECT * FROM p100_calendar_v;

This makes dynamic views fully testable — something inline region queries can never be.

When to Use Dynamic Views

• Page-specific views (p100_orders_v) — when the view is used on one page and references that page's items directly.
• Base views (report_base_v) — when multiple pages share the same filters and the get_item('$NAME') pattern avoids duplication.
• Static views (active_orders_v) — when no page item filtering is needed. Use these for LOV sources, lookups, and data that does not depend on user context.

For further reading: The magic of dynamic views in APEX.

Page Design Rules

Page Properties

Every page must have:

• Page alias. Use a meaningful alias (e.g. orders, customer-detail). This enables Friendly URLs and lets you change page numbers without breaking bookmarks.
• Page group. Group related pages together (e.g. Orders, Admin, Reports). This is essential for navigation in the APEX Builder.
• Authorization scheme. Set on every page, no exceptions. Use authorization schemes backed by a package function, not by application item checks.

Page Numbering

Use page numbers >= 100 and Embedded Code) to find all SQL and PL/SQL hardcoded on pages. The goal is to minimize this list over time. Every entry is a candidate for extraction into a view or package.

What to look for:

• SQL queries on regions — move to views.
• PL/SQL in processes — move to package procedures.
• PL/SQL in validations — move to package functions.
• PL/SQL in computations — move to package functions.
• PL/SQL in dynamic actions — move to package procedures called via CALL_ AJAX callback.

APEX Advisor

Run the APEX Advisor (Utilities > Advisor) to catch common issues. Review every finding. Key checks:

• Pages without authorization schemes.
• Deprecated component usage.
• Items with encryption enabled unnecessarily.
• Missing page aliases.
• Security vulnerabilities flagged by the advisor.

Manual Review Checklist

When reviewing an APEX application or a pull request that includes APEX changes, verify:

• Every page has a page alias, page group, and authorization scheme.
• No inline SQL on page regions — all queries come from views or table/view references.
• No inline PL/SQL in processes — all logic calls package procedures via Invoke API or PL/SQL call.
• Process names follow the prefix convention (INIT_, SET_, SAVE_, etc.).
• Page item names match underlying column names where applicable.
• LOVs are defined in Shared Components with backing views.
• JavaScript and CSS are in application/workspace files, not inline.
• Static IDs use the correct prefix (REGION_, BUTTON_) where referenced.
• No hardcoded strings that should be Substitution Strings.
• Embedded Code Report has been reviewed and no new inline code was introduced.
• APEX Advisor has been run and findings addressed.
• All relevant APEX exports are present in the commit: split export, readable export, and embedded code report.

Examples

Audit an APEX application against the QA standards before a pull request:

/apex-qa

Review a single page's design for naming, MVC separation, and authorization:

/apex-qa

Run the manual review checklist after importing the Embedded Code Report:

/apex-qa

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: jkvetina
• Source: jkvetina/AI_SKILLS
• License: MIT

Install and usage instructions live in the source repository linked above.