Oracle APEX Validations
The plug-in derives the table and column from the context of the triggering Item/Grid cell. For the detected table columns the plug-in will perform automatic validations that prevent data integrity violations when the form is saved. Basic checks for data integrity will be performed automatically and 19 configurable Text Message templates are used to clear or display inline validation messages. This solves the following problems:
REQUIRED is the only validation of the Interactive Grid that is performed when a field loses focus.
When you SUBMIT an APEX form for DML processing, APEX will perform basic and predefined validations and report inline error messages only for empty fields that are required, minimum and maximum violations, and for data conversion errors.
All other constraint errors are reported in a cryptic page message without a reference to the form item or grid cell.
Database constraints for SQL tables are enforced in DML operations. Even when multiple constraints are violated or conversions fail, this operation returns only a single error.
When you program APEX pages for data entry into SQL tables or views, you have to consider programming many APEX validations for each input field to avoid end-user confusion unless you are prepared to give training or support when users don't understand the meaning of the messages.
If you want to show input errors to the app users as soon as the input is completed (Lose Focus), you must implement constraint evaluations in 2 languages (JavaScript and PL/SQL). You have to program client-side validations in JavaScript when a field loses focus or is changed, and you also have to program row or page validations in APEX for the processing phase that will be invoked when you save a form or IG.
Programming validations is difficult because you have to handle the cases where
- input data is missing
- or fails in conversion
- or is it out of range
for each addressed column before you can evaluate the actual constraint condition.
When you have public users that require comfort and good guidance in the event of input errors, you must invest a lot of time to program a case-specific solution.
Automatic Constraint Validation Pro automatically implements many validations that would have had to implement as client-side validations in JavaScript or as APEX Validations in the Page Designer.
Supported DML Validation
- Value required, e.g., not null constraints
- Data conversion with format mask.
- Range limits for numbers, date, timestamp, and intervals depend on precision and scale.
- Check text length with maximum length in char or byte semantic for character columns, after trimming leading and/or trailing blanks according to the item settings.
- Table check constraints like: income > 0, credit < credit_limit, channel in ('online','direct').
For multi-column constraints, all involved columns must be selected in the query and must exist in the grid or form as visible or hidden items to perform the validations. - Table/View Foreign Key Constraints with one or more referenced columns. The existence test is done by probing the database for existing values.
- Table/View Unique Key Constraints with one or more columns and with support for composite primary keys or ROWID in the uniqueness test.
The uniqueness test probes the database for existing values where rows that are modified or deleted in the Interactive Grid will be excluded from the probe.
A second test for uniqueness is performed by probing new and changed rows in the Interactive Grid.
Many of the built-in validation types have equivalent database constraints
APEX Validation | Table Constraint |
---|---|
Rows returned | |
No rows returned | |
SQL Expression | |
PL/SQL Expression | |
PL/SQL Error | |
PL/SQL Function Body (returning Boolean) | |
PL/SQL Function (returning Error Text) | |
Item is NOT NULL | |
Item is NOT NULL or zero | |
Item is NOT zero | |
Item contains no spaces | |
Item is alphanumeric | |
Item is numeric | |
Item is a valid date | |
Item is a valid timestamp | |
Item = Value | |
Item != Value | |
Item is contained in Value | |
Item is NOT contained in Value | |
Item contains only characters specified in Value | |
Item contains at least one of the characters in Value | |
Item does NOT contain any of the characters in Value | |
Item matches Regular Expression |