The editable Interactive Grid is one of the most useful components in Oracle APEX. It lets users enter invoice lines, purchase order items, attendance adjustments or price lists in a spreadsheet-like screen. It is also where many APEX applications develop data-quality problems, because validation and save logic are often left at the defaults.
This guide covers the settings and patterns we use when an Interactive Grid has to handle real business data.
1. Start with a proper primary key
An editable grid must be able to identify each row. Use the table’s primary key column and mark it as Primary Key in the column attributes. Hide it from users, but keep it in the grid. If the table has no primary key, APEX can use ROWID, but a real key is safer, especially for tables that are also updated by other programs or APIs.
For new rows, let the database generate the key with an identity column or a trigger and sequence. Do not ask users to type IDs.
2. Enable only the operations users need
Under Attributes → Edit, enable editing and choose the allowed operations: Add Row, Update Row, Delete Row. Many business screens should not allow delete at all, or only for certain roles. Use the Server-side Condition and Authorization on these operations rather than hiding toolbar buttons with CSS.
You can also make individual rows read-only with the Allowed Row Operations Column setting. Return a value such as 'UD', 'U' or NULL from a column in the SQL query — for example, lines on a posted invoice return NULL so they cannot be changed.
3. Column-level validation
Set Value Required, maximum length, number format and minimum/maximum values on the columns themselves. These run in the browser for quick feedback and again on the server.
For business rules, create a normal page validation and set its Editable Region to the grid. APEX then runs it once for every changed row, and you can reference the grid columns as bind variables:
-- Validation type: Function Body (returning Boolean)
-- Editable Region: Order Lines grid
-- Associated Column: QTY
RETURN :QTY > 0 AND :QTY <= 10000;Set Associated Column so the error appears on the exact cell the user needs to fix.
4. Skip validation for deleted rows
A common annoyance: a user deletes an incomplete row, but the save still fails because the deleted row breaks a “value required” rule. In grid validations, check the row status first:
RETURN :APEX$ROW_STATUS = 'D'
OR (:PRICE IS NOT NULL AND :PRICE >= 0);:APEX$ROW_STATUS is 'C' for created rows, 'U' for updated rows and 'D' for deleted rows.
5. Custom PL/SQL instead of automatic DML
The default Interactive Grid – Automatic Row Processing (DML) process works well for simple tables. When saving requires extra logic — writing to several tables, calling a package, setting audit columns or recalculating totals — change the process Target Type to PL/SQL Code:
BEGIN
CASE :APEX$ROW_STATUS
WHEN 'C' THEN
INSERT INTO order_lines (order_id, item_id, qty, unit_price)
VALUES (:P20_ORDER_ID, :ITEM_ID, :QTY, :UNIT_PRICE)
RETURNING line_id INTO :LINE_ID;
WHEN 'U' THEN
UPDATE order_lines
SET item_id = :ITEM_ID,
qty = :QTY,
unit_price = :UNIT_PRICE
WHERE line_id = :LINE_ID;
WHEN 'D' THEN
DELETE FROM order_lines
WHERE line_id = :LINE_ID;
END CASE;
END;Returning the new key into :LINE_ID lets APEX refresh the row correctly after save. Better still, move the logic into a package procedure and call it from the process so it can be reused by APIs and other pages.
6. Recalculate header totals after the grid saves
When an order header shows a total of its lines, add a separate page process after the grid’s save process that recalculates the total from the table:
UPDATE orders o
SET o.total_amount = (SELECT NVL(SUM(qty * unit_price), 0)
FROM order_lines l
WHERE l.order_id = o.order_id)
WHERE o.order_id = :P20_ORDER_ID;Do not trust a total calculated in JavaScript and submitted from the browser. Always calculate stored amounts on the server.
7. Cross-row rules belong in the database
Grid validations run row by row, so they cannot reliably check rules across rows, such as “the same item cannot appear twice on an order”. Enforce these with database constraints — for example a unique constraint on (order_id, item_id) — and use an APEX Error Handling Function to convert the constraint error into a friendly message.
8. Lost update protection
Keep Lost Update Detection enabled. It prevents one user’s save from silently overwriting changes another user made after the first user opened the page. If a user gets a “current version of data in database has changed” message, the correct action is to refresh and re-apply their change.
9. Master-detail grids
For an order header with lines, use a form region for the header and a grid for the lines, with the grid’s Master Region set to the form or a parent grid. When users add lines to a new header, make sure the header’s save process runs first so the generated header ID is available to the line inserts.
10. Performance tips
- Use Lazy Loading and pagination for large grids.
- Avoid scalar subqueries per row in the grid SQL; use joins or views.
- Use popup LOVs instead of select lists with thousands of values.
- Index the columns used to filter the grid, such as
order_id.
Checklist before go-live
- Primary key defined and hidden
- Only required operations enabled and protected by authorization
- Column-level and row-level validations with associated columns
- Validations skip deleted rows
- Totals recalculated on the server
- Cross-row rules enforced by constraints
- Lost update detection enabled
- Tested with realistic data volumes and multiple users
Building data-entry screens in Oracle APEX?
AbairTech builds Oracle APEX applications for ERP, POS, HR and finance teams, including Oracle Forms modernization where multi-record blocks become Interactive Grids. See Oracle APEX Development Services.
