# Apex Qa

> Oracle APEX application quality assurance — standards for page design, component naming, MVC enforcement, and automated checks. Use this skill whenever reviewing APEX applications for quality, creating or auditing APEX pages, checking APEX naming conventions, enforcing MVC separation, reviewing embedded code reports, or setting up APEX QA checklists. Triggers: APEX review, APEX QA, APEX naming, A…

- **Type:** Skill
- **Install:** `agentstack add skill-jkvetina-ai-skills-apex-qa`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [jkvetina](https://agentstack.voostack.com/s/jkvetina)
- **Installs:** 0
- **Category:** [Agent Skills](https://agentstack.voostack.com/c/agent-skills)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [jkvetina](https://github.com/jkvetina)
- **Source:** https://github.com/jkvetina/AI_SKILLS/tree/main/DEVOPS/apex-qa

## Install

```sh
agentstack add skill-jkvetina-ai-skills-apex-qa
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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

```sql
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:

```sql
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:

```sql
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](https://www.oneoracledeveloper.com/2023/03/the-magic-of-dynamic-views.html).

## 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:

```bash
/apex-qa
```

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

```bash
/apex-qa
```

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

```bash
/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](https://github.com/jkvetina)
- **Source:** [jkvetina/AI_SKILLS](https://github.com/jkvetina/AI_SKILLS)
- **License:** MIT

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

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-jkvetina-ai-skills-apex-qa
- Seller: https://agentstack.voostack.com/s/jkvetina
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.