The local fixture chooses fixed SQL text from the validated mode, retains a bind placeholder, and uses the entered snapshot status to calculate expected row IDs. It compares expected row identities without presenting a native APEX execution.
Confirm the exact report context first
The confirmed reference context is Oracle APEX 26.1 using REST-Enabled SQL against a remote Oracle database. Oracle's REST-Enabled SQL service guide explicitly describes an Employees Interactive Report and says the source-type options include Function Body Returning SQL Query in that remote Oracle context.
That establishes the documented context, not the configuration of your installed site. Before implementation, confirm the installed APEX version, region type, source location, available source-type option, remote service setup, and permitted test environment.
Do not infer support for a local Interactive Report, another APEX version, or a non-Oracle remote database solely from this example. If the intended context differs, hold the blueprint until a directly applicable source and native configuration confirm it.
Oracle's 24.1 application concepts describe the general sequence: a function body returns SQL, then APEX executes that query. That is a mechanism reference; it is not separate proof of a specific 24.1 Interactive Report source option.
Use two fixed branches with the same column contract
The original fixture has these rows:
| TASK_ID | STATUS |
|---|---|
| 1 | OPEN |
| 2 | CLOSED |
TASK_ID is numeric; STATUS is textual. Both branches must expose these same aliases and intended types. There are no existing production tasks or table names to substitute.
For a later native test, the following two authored SQL texts describe the complete fixture without creating a table. They are unexecuted specifications requiring validation in the target APEX and remote Oracle context.
ALL branch:
WITH tasks AS (
SELECT CAST(1 AS NUMBER) AS TASK_ID,
CAST('OPEN' AS VARCHAR2(6)) AS STATUS FROM dual
UNION ALL
SELECT CAST(2 AS NUMBER) AS TASK_ID,
CAST('CLOSED' AS VARCHAR2(6)) AS STATUS FROM dual
)
SELECT TASK_ID, STATUS FROM tasks
FILTERED branch:
WITH tasks AS (
SELECT CAST(1 AS NUMBER) AS TASK_ID,
CAST('OPEN' AS VARCHAR2(6)) AS STATUS FROM dual
UNION ALL
SELECT CAST(2 AS NUMBER) AS TASK_ID,
CAST('CLOSED' AS VARCHAR2(6)) AS STATUS FROM dual
)
SELECT TASK_ID, STATUS FROM tasks
WHERE STATUS = :P1_STATUS
The ALL target is the set of row IDs 1 and 2. No order is specified. FILTERED uses a placeholder for a status value supplied separately at query execution.
The casts make the intended type contract explicit in the authored text. They do not prove that the region has accepted its metadata or that the remote query parsed. Inspect the actual column aliases and types later rather than treating a plausible SELECT as successful setup.
Let mode select text; keep status out of the text
Define P1_MODE with exactly two allowed values: ALL and FILTERED. Define P1_STATUS with exactly two allowed values: OPEN and CLOSED.
For this bounded application policy, require both items to have a known, valid server value before returning a branch, including in ALL mode. This is an authored validation rule, not a universal APEX requirement. ALL does not apply a status predicate even though the fixture's state validation requires a status value.
The function body should validate the current submitted snapshot, then return one of the two fixed SQL texts. It does not accept user-written SQL, concatenate a status into the query, or insert page text using substitution syntax.
Keep :P1_STATUS in the FILTERED text for both OPEN and CLOSED cases. Changing the status changes a bound value, not the SQL branch text. Record that distinction in the ledger.
Compare visible inputs with the submitted server snapshot
| Case | Visible P1_STATUS | Server status used for request | Expected row-ID set |
|---|---|---|---|
| S1 | OPEN | Confirmed OPEN | {1} |
| S2 | CLOSED after a local edit | Still confirmed OPEN; new value not submitted | {1} |
| S3 | CLOSED | Confirmed CLOSED after submission | {2} |
Try the worked example
Keep FILTERED/OPEN in the entered server snapshot and type CLOSED in visible-only status: expected row 1 remains. Change the snapshot to CLOSED and inspect for row 2. Confirm the real submit/AJAX settings in the native page before treating a browser selector as server evidence. Returned SQL text is not an executed row set.
This is a local fixed-fixture trace, not APEX, PL/SQL execution, server session inspection, or a remote Oracle connection.
Recreate this example
Build one complete self-contained HTML page for this task: build a fixed-branch SQL and entered-snapshot trace for the original APEX fixture.
Use these exact original practice data and worked expectations: The task fixture contains TASK_ID 1 with STATUS OPEN and TASK_ID 2 with STATUS CLOSED. Allowed P1_MODE values are ALL and FILTERED; P1_STATUS values are OPEN and CLOSED. ALL expects IDs 1 and 2; FILTERED/OPEN expects ID 1; FILTERED/CLOSED expects ID 2. The fixed SQL retains the bind placeholder :P1_STATUS instead of concatenating a status value.
Implement the following useful local interactions: Fields mode, entered server-snapshot status, and visible-only status. Inspect returns fixed SQL text, the entered snapshot status, and expected fixture rows. Visible-only edit cannot change server snapshot. Invalid enums hold; no database or server request. Preserve the supplied source text and distinguish an uncomputed state, a successful local result, and an invalid or incomplete input. Update output only from the current input snapshot. An input edit must retire stale feedback until the reader runs the relevant action again. Do not silently invent missing values or replace a failing case with a success.
Design the page as a compact source-and-result developer workbench with readable code, labeled inputs, and a separate explanation area. Put a compact task heading, actual sample data, the main result, and usable controls early enough to be useful on a narrow screen. Use readable text of at least 16px, generous paragraph spacing, strong contrast, native labels, accessible buttons, and visible keyboard focus. Stack long records at 360px and wrap code without page-level horizontal overflow.
Use type=button and explicit querySelector-based click handlers rather than relying on form submission. If Enter is useful, handle it only on the relevant input and ignore isComposing. Keep effects within this open page; no remote dependencies, network calls, persistence, fake downloads, or automated sending. This is a local fixed-fixture trace, not APEX, PL/SQL execution, server session inspection, or a remote Oracle connection. Place worked explanations beside their data and identify teacher or synthetic examples clearly. Output only the complete HTML document, with inline styles and script and LF line endings.
Review the SQL-branch and snapshot trace builder in NOS
Compare the trace with actual submitted item values and report rows in the permitted APEX environment. Use the review copy in NOS to compare the visible CLOSED choice with the submitted OPEN snapshot, and revise eligible static submission instructions in visual editing mode if that distinction is unclear.