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	FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS
No rows returned	UNIQUE (EMAIL) DEFERRABLE INITIALLY DEFERRED
SQL Expression	CHECK (LIST_PRICE >= MIN_PRICE)
PL/SQL Expression
PL/SQL Error
PL/SQL Function Body (returning Boolean)
PL/SQL Function (returning Error Text)
Item is NOT NULL	CHECK col IS NOT NULL
Item is NOT NULL or zero
Item is NOT zero	CHECK col != 0
Item contains no spaces	CHECK col = translate(col, ' ', '.')
Item is alphanumeric	CHECK REGEXP_LIKE (col, '^[0-9a-zA-Z]+$')
Item is numeric	Automatically checked format conversion
Item is a valid date	Automatically checked format conversion
Item is a valid timestamp	Automatically checked format conversion
Item = Value	CHECK col = ‘Constant’
Item != Value	CHECK col != ‘Constant’
Item is contained in Value	CHECK col in ('alpha', 'beta', 'gamma')
Item is NOT contained in Value	CHECK col not in ('alpha', 'beta', 'gamma')
Item contains only characters specified in Value	CHECK translate(col, '#ABCDEF', '#') is null
Item contains at least one of the characters in Value	CHECK translate(col, '#ABCDEF', '#') != col
Item does NOT contain any of the characters in Value	CHECK translate(col, '#ABCDEF', '#') = col
Item matches Regular Expression	CHECK REGEXP_LIKE (col, '^[0-9a-zA-Z]+$')