Hi all,
I’m relatively new to Oracle APEX, SQL, and PL/SQL, and I’m currently stuck on a problem I just can’t seem to solve.
Scenario:
I’m developing an APEX application where teachers record pupil progress on a large set of skills. Here’s the setup:
- There are ~100 pupils and ~1,000 skills.
- Each pupil can be assessed on any skill, and teachers can log up to three pieces of evidence per skill.
- The goal is to display an interactive grid showing only the unachieved skills for a selected pupil, filtered by class, phase, subject, and standard.
- For each skill, there are three checkboxes corresponding to whether evidence 1, 2, or 3 has been submitted.
- When a box is ticked and the user clicks “Save”, I want a row to be added (or updated) in a separate EVIDENCE table.
My approach so far:
- I’ve created a view (VW_EVIDENCE_ENTRY) that combines data from pupils, skills, and existing evidence entries. It’s essentially a flattened version where each row represents a pupil-skill combo, along with indicators for evidence_1, evidence_2, and evidence_3 (0/1 or checkboxes).
- This view is used as the source of the interactive grid.
- I’ve written a custom DML process (type: PL/SQL) that is supposed to insert/update rows in the EVIDENCE table based on changes in the grid.
The issue:
Despite everything appearing correctly in the UI — filtering works, checkboxes toggle as expected — the interactive grid simply does not save any changes to the EVIDENCE table. No rows are inserted or updated. The custom DML process is not firing as I expect, or perhaps I’m missing key steps to make the view-based grid properly save to the underlying table.
What I’ve tried:
- Verified that the view itself is read-only (as expected), so I know I need custom DML.
- Created a PL/SQL process to handle the DML, but it’s not triggering.
- Checked that all session state values (pupil ID, skill ID, etc.) are available.
- Tried different configurations of the interactive grid (including enabling row processing manually).
- No errors are thrown — just no data being saved.
What I’m asking:
- Has anyone implemented a similar setup (interactive grid on a view, saving to a base table)?
- Is there a best-practice approach for handling checkboxes like this, particularly when the source is a view and you’re writing back to a separate table?
- What key steps might I be missing to get the custom DML to actually execute?
Any suggestions, examples, or explanations (even high-level) would be greatly appreciated. Thanks in advance for your time!
Let me know if you’d like to include:
- A sample of the view definition
- A snippet of the DML process
- Or the grid’s configuration
Regards