Oracle APEX Interactive Grid Validations

How to combine declarative rules, client-side checks, and server-side enforcement to keep grid data clean, without frustrating users.

Oracle APEX Interactive Grid (IG) is brilliant for rapid, spreadsheet-style data entry, but validation is where most IG builds start to feel “non‑declarative”. Required columns work out of the box, yet as soon as you need cross-column rules, cross-row checks, or friendly inline error feedback, you’ll likely reach for a blend of built-in validations, JavaScript model APIs, and server-side checks.

This blog explores the types of validation available in IG and how to get the best out of them using an IG based on an employee’s dataset.

Validation Types in Interactive Grid

• Declarative client-side validations (APEX): Instant feedback while the user edits based on column attributes.

• Declarative custom server-side validations: Provided by the developer, these are validations and constraints that run on submit/save to guarantee integrity. Could be checks on individual columns, across columns for each row or checks across the whole IG model.

• Client-side validation with JavaScript: Provides instant feedback while the user edits. Uses the IG model APIs and APEX messaging for page-level errors.

Declarative client-side validations (APEX)

There are a number of APEX-configurable attributes that can be applied to IG columns that enforce client-side validation.  Common examples include:

Value Required

For a given column, e.g. FIRST_NAME, set the Value Required Validation property to ‘On’.

On data entry, if the no value has been entered, on exiting the cell, an error is highlighted.

Date Format Mask

For a date column, e.g. HIRE_DATE, set the required Format Mask Appearance property, e.g. ‘DD-MON-YYYY’.

If an invalid date or a date in an incorrect format has been entered, on exiting the cell, an error is highlighted.

Numeric Range

For a number column, e.g. SALARY, specify Minimum Value and Maximum Value Settings, e.g. ‘2000’ to ‘40000’.

If a number outside the specified range has been entered, on exiting the cell, an error is highlighted.

Making use of the APEX’s configurable column attributes to enforce basic validations is the best way of implementing client-side validations.

Declarative custom server-side validations

Invariably, to ensure data integrity in your applications, developers will want to configure their own validations and constraints, tested on submit/save of the grid.

There are numerous declarative validation types associated with interactive grids, most listed below.

Most of these validation types test against individual columns; however, using the SQL or PL/SQL-based validation types (Rows returned, No Rows returned, Expression, PL/SQL Error, Function Body (returning Boolean), Function Body (returning Error Text)), we can test across multiple columns and rows.

I have created a row validation to check that employees in the Sales department must be assigned a Commission Pct value.

When I add a new employee row (Fred Bloggs) to the grid for an employee in Sales, leaving the Commission Pct null and Save, I get the expected error.

Notice that there is an existing row in the data (Kimberley Grant), who is also in Sales but without a commission, but for whom the validation hasn’t failed.

Execution Scope

Part of the explanation for this is that the existing row for Kimberly Grant has not been identified for validation – the execution scope for the validation has defaulted to ‘For Created and Modified Rows’ and Kimberley’s record has not been modified.

A word of caution, selecting ‘All Submitted Rows’ does not necessarily mean that her record would be brought into scope either. ‘All Submitted Rows’ does not mean all rows on the grid. Indeed, typically, to be submitted, APEX has to identify a row as changed. Therefore, often there is no practical difference between using ‘For Created and Modified Rows’ and ‘All Submitted Rows’.

All Submitted Rows means that validation runs for every row in the submitted model payload, regardless of row status. Potentially including:

• Created rows

• Updated rows

• Deleted rows

• Rows marked as ‘dirty’ indirectly e.g.

• Changing the parent row recalculates child rows

• Validation checks for duplicates or totals

• Rows submitted via custom JavaScript logic using model.setValue(...)

If I change the Validation > Always Execute attribute to ‘On’, bringing deleted rows into the equation for validation, and switch the Execution Scope to ‘All Submitted Rows’, then delete the row for Kimberley, then validation for her row comes into scope.

But this is just a single-row validation across columns. What if I want to validate across multiple rows, e.g. for duplicates or for totals?

Multi-Row Validation

My next validation checks that an entered email is unique, using a No Rows returned validation type, with SQL checking that nobody else has that email.

The validation triggers successfully when an email is duplicated from that of an existing record.

However, if I create two new rows and both rows have the same email, both pass the declared validation. At the point of save, that email address doesn’t exist on the database, so no rows are returned by the SQL, and validation passes.

Thankfully, I have a unique constraint defined on the email column in the database, so an error is still generated, but it’s not pretty. It’s good practice to enforce rules in the database where possible, for extra security.

Client-side validation with JavaScript

Client-side validation improves user experience, catching issues before page submit. But, with IG, it is especially useful for cross-row checks such as duplicates, aggregation and completeness checks (check every row has been completed).

In my application, I have hidden the vanilla IG Save button and replaced it with a custom Save button that invokes the grid save behaviour. You don’t have to place your custom replacement button here; any button position would be fine, but it’s good practice to only have one save button on the page, so you should still hide the vanilla IG Save button.

Then, I have created a validation JavaScript function, triggered on the click of the custom Save button, that checks for duplicates.

It’s a generic function that works for any IG and column, accepting the grid's static ID and column name to check as inputs.  Essentially, it reads through each record in the grid model, checking that the value doesn’t already exist for a record, and uses the setValidity method to set the validity and associated validation message of the record field.

Additionally, on return from the function call, if there are duplicates, I raise a page-level error using the apex.message.showErrors API. Note that one of the input parameters for the function is the static ID for the IG region being validated.

Now, when I attempt to add two new rows with the same email, where that email doesn’t currently exist in the database, a validation error is raised.

It’s worth noting that, where you make use of such client-side checks, it’s important to support that with server-side enforcement. Users can bypass JavaScript, data can change between edit and save, and concurrency can create duplicates. The simplest and strongest enforcement, as mentioned previously, is to employ database constraints (e.g. unique constraints, check constraints, foreign keys). Where rules can’t live in the database, add validations or PL/SQL logic during the save process.

Conclusion

Client-side validations can feel complex and JavaScript-heavy, but often, by reducing the burden of maintaining that code, they can be standardised and reused for multiple validations across multiple IGs. In fact, to get the best out of validations of your IGs, to improve user experience with immediate, friendly feedback, they form an essential part of the IG validation recipe alongside built-in APEX validations, custom declarative validations, server-side checks and database enforcement.

 For more information, check out our Oracle APEX Services, or contact us today, and one of our expert developers will be in touch.