What Happens When You Click Save
When a user clicks Save on an editable Interactive Grid, APEX performs a series of operations: it collects all changed, inserted, and deleted rows from the client-side model, sends them to the server in a single AJAX request, and processes each row through the IG’s DML process. Understanding this pipeline helps you debug save failures and implement custom processing logic.
The Automatic DML Process
APEX creates a DML process automatically when you create an editable IG. This process handles INSERT, UPDATE, and DELETE statements based on the grid’s source table. The process uses the ROWID (or primary key) to identify which row to update or delete. It processes rows in this order: updates first, then inserts, then deletes.
Custom DML With PL/SQL
When the automatic DML is not sufficient (complex validation, audit logging, or updating multiple tables), switch the process type to “PL/SQL Code.” APEX provides bind variables for each column’s old and new values:
-- Custom DML process
BEGIN
CASE :APEX$ROW_STATUS
WHEN 'C' THEN -- Created (Insert)
INSERT INTO orders (customer_id, order_date, status, notes)
VALUES (:CUSTOMER_ID, :ORDER_DATE, 'DRAFT', :NOTES);
WHEN 'U' THEN -- Updated
UPDATE orders
SET customer_id = :CUSTOMER_ID,
order_date = :ORDER_DATE,
notes = :NOTES,
updated_by = :APP_USER,
updated_dt = SYSDATE
WHERE order_id = :ORDER_ID;
WHEN 'D' THEN -- Deleted
UPDATE orders SET status = 'CANCELLED',
cancelled_by = :APP_USER,
cancelled_dt = SYSDATE
WHERE order_id = :ORDER_ID;
END CASE;
END;APEX$ROW_STATUS Values
The :APEX$ROW_STATUS substitution tells you what operation is being performed: ‘C’ for Create (new row), ‘U’ for Update (modified row), and ‘D’ for Delete. Use this in CASE statements to handle each operation differently. There is also :APEX$ROW_NUM which gives the sequence number of the current row being processed.
Returning Values After Insert
When inserting rows, you often need to return the generated primary key (from a sequence or identity column) back to the grid. Use the “Return” section of the DML process to map the generated key back to the IG column. This updates the grid’s client-side model with the real key value, which is essential for subsequent saves and for master-detail relationships.
Error Handling During Save
If a row fails validation or DML, APEX marks that row with an error indicator in the grid and displays the error message. Other rows that succeeded are saved normally. The user can correct the failed row and save again. For custom error messages, use RAISE_APPLICATION_ERROR or APEX_ERROR.ADD_ERROR in your PL/SQL process, and APEX will associate the error with the current row.