Oracle APEX Interactive Grid – How to perform row-level validation with async server call before save?

by

Summary

An Interactive Grid (IG) with automatic row processing fails to perform reliable, asynchronous row-level validation before data is committed. The core issue is that the IG’s built-in DML process occurs after the data is posted to the server, but before the has_overlap PL/SQL function can be called synchronously. Furthermore, checking for overlaps across newly added rows within the same transaction is notoriously difficult using standard APEX methods.

Root Cause

The root cause lies in the execution order of APEX architecture and the limitations of the asynchronous request model.

  1. Asynchronous Delay: Client-side JavaScript validation is instant, but server-side PL/SQL validation requires a round-trip. If you trigger an AJAX call on cell change, the IG does not wait for that response before allowing the user to move to the next row or click “Save.”
  2. Transaction State: The IG processes rows individually. The has_overlap function queries the database, but it cannot “see” other rows being added or modified in the current IG session until the “Save” button triggers the actual DML process.
  3. Missing Native Support: APEX 22 does not have a built-in event hook that allows you to inject an await command into the native “Save” process to run a custom PL/SQL check for every modified row before the DML executes.

Why This Happens in Real Systems

This is a classic impedance mismatch between UI responsiveness and Data Integrity.

  • User Expectation: Users expect to know immediately if a date overlap exists, usually when they tab out of the END_DATE field.
  • System Reality: APEX treats data entry as a “draft” state until the “Save” button is clicked. Developers try to bridge this gap by using Dynamic Actions (DA) on cell change to call a PL/SQL process via apex.server.process. However, this creates a “Race Condition”:
    • The user edits Row 1 -> DA fires -> AJAX request sent.
    • The user edits Row 2 -> DA fires -> AJAX request sent.
    • The user clicks “Save.”
    • The AJAX responses for Rows 1 and 2 return after the Save request has already been sent, or they return too late to stop the transaction.

Real-World Impact

  • Data Corruption: Even if the user sees a “Success” message in the IG, the database constraints might trigger a ORA-00001 (Unique Constraint) or ORA-02290 (Check Constraint) if the overlap check was missed due to timing.
  • Poor User Experience: Users see red highlights (or no highlights) and then get a generic “Error occurred during saving” message, which doesn’t explain why the save failed or which row caused it.
  • Stale Data: If the validation logic relies on data that was queried 5 seconds ago (during the async call), that data might be stale by the time the user actually saves the grid.

Example or Code

To fix this, we must stop trying to validate during typing and instead perform a Batch Validation strictly before the standard DML process runs. We use the “Before Commit” dynamic action.

/* PL/SQL Function used as the logic engine */
function has_overlap ( 
    p_item_id number, 
    p_start_date date, 
    p_end_date date 
) return boolean 
is
    l_count number;
begin
    select count(*) 
    into l_count
    from my_table
    where item_id = p_item_id
      and status = 'ACTIVE'
      -- Overlap logic: (StartA = StartB)
      and (p_start_date = start_date);

    return l_count > 0;
end;

How Senior Engineers Fix It

Senior engineers decouple validation from data entry and rely on the Transaction scope.

  1. Disable Auto Save: Set the IG attribute “Automatic Row Processing (DML)” to No. This prevents the DML from running on every cell change or navigation change.
  2. Use the “Before Commit” Event:
    • Create a Dynamic Action on the IG with the event “Before Commit”.
    • This event fires when the user clicks “Save,” before the IG data is sent to the database.
    • Inside this DA, use apex.server.process to send the entire modified dataset (using apex.gvJSON) to a custom PL/SQL process.
  3. Server-Side Aggregation:
    • The custom PL/SQL process receives the JSON array of modified rows.
    • It iterates through the array. For every row, it checks the has_overlap function.
    • Crucially, it must check overlaps against the database AND against other rows in the incoming JSON payload (handling the “newly added rows” scenario).
  4. Abort Transaction:
    • If any overlap is found, the PL/SQL process raises an application error (apex_application.raise_error).
    • The “Before Commit” DA catches this error and sets apex.event.preventDefault(), which aborts the save.
  5. Visual Feedback: Use the IG API (apex.region("GRID_ID").widget().interactiveGrid("getViews", "grid").model) to mark the specific rows as invalid and display the error message inline.

Why Juniors Miss It

  1. Over-reliance on “Execute Server-Side Code”: Juniors often drag a “Execute PL/SQL Code” DA onto a Change event on a specific column. They don’t realize that this creates dozens of pending requests that fire out of order and don’t block the final save.
  2. Ignoring the “Commit” Lifecycle: They don’t understand that APEX has a distinct lifecycle: Page Submit -> Validation -> DML Process. They try to jam validation logic into the DML Process step (via Row Processing) rather than the Validation step (via Before Commit).
  3. Data Scope Blindness: They forget that a PL/SQL function running on the server cannot see the 5 rows the user just added to the grid but hasn’t saved yet. Without explicitly passing that JSON data to the server, the query will return false negatives (allowing overlaps within the batch).