Features at a Glance
Safe to Use
The plug-in overlays the built-in database constraint validations executed as part of DML processing. Database constraints are agnostic to data types and missing values and are applicable without exception. This simplifies the formulation of business rules.
Automatic Validations
The data types and constraints that you find in every good database model are validated by the plug-in without programming (APEX Validations). The conversions and limits are automatically checked for numbers, dates, and intervals. Constraints of type CHECK on your database tables and constraints of type REQUIRED (not null), PRIMARY KEY, FOREIGN KEY, and UNIQUE on database tables and views are fully supported and enforced.
Flexible Configuration
Automatic Constraint validations will execute in APEX Forms and Interactive Grids where the source is an SQL Table/View or SQL Query into the local database. The plug-in supports Lose-Focus and Change events for regions or jQuery Selectors as the triggering elements in the dynamic action. With optional plug-in settings, a Table Owner, Table Name, Minimum Year, Maximum Year, Refresh Cache, and page notifications for Forms, IG-Reports, and IG-Single Row Views can be configured.
Validation Process
All constraints are validated with a high-performance AJAX process and use standard API functions to display errors. The commercial version uses caching to speed up the processing. After warming up, the process executes with an average speed of 0.012 seconds (Mac Mini 2018, 3 GHz), and the client displays the results after ca. 0.03 seconds in a LAN (before the user has started typing in the next field). When an item/column is part of a composite constraint, all involved column values are sent to the process in one request, and multiple result messages will be received in the reply.
Interactive Grid
The plug-in can validate Interactive Grid columns in Report Views and Single Row Views when an item/column loses focus. The uniqueness test is done by probing the database for existing values where modified or deleted rows 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.
Validation flow
The event handler of the plug-in sends the last entered input text, together with related information about the item/column, to a server process. The process automatically verifies REQUIRED, datatype CONVERSIONS with format pattern and storage LIMIT checks before evaluating the database CONSTRAINTS associated with the last input field. COMPOSITE CONSTRAINTS that address multiple columns will be evaluated when all involved fields have an appropriate value. The error message will be displayed or cleared where the last input was entered, and other involved fields in the current record will be updated automatically. While errors are displayed, the user is prevented from saving the form.
Translations
You can translate the 19 provided English default messages into specific Text Messages of your language. The plug-in uses column/item labels for the messages in the current session language when the app has been translated with XLIFF translation files.
PL/SQL Library
The UC_CONSTRAINT_VALIDATION library provides functions to control the rendering of the dynamic action on page 0; control the rendering of a Constraints Check List, and procedures to perform generic row validations when a form or IG is saved or submitted.
Free & Commercial Release
The free release has the same functional features as the commercial release but executes slower. The plug-in component setting 'Refresh Cache' is ignored. In the commercial release, the validation AJAX process runs ca. 10 times faster by using caching tables. You can upgrade to the commercial version by replacing the PL/SQL library without changing the APEX applications.