Table of Contents

Introduction

Automatic Constraint Validation Pro is an APEX Dynamic Action plug-in for automatic database-driven constraint validations. This plug-in informs your users about data integrity problems with clear explanations in real-time at the input fields.

Features at 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.

Preconditions

Automatic Constraint Validation Pro will work in pages with APEX Form and Interactive Grid regions where the source type is a simple SQL Query or SQL Table (table, view, or synonym name) and where the source location is set to 'Local Database'. When the region's source type is SQL Query, the query may have only one FROM clause where the plug-in should detect the table name.

The plug-in will work best with forms on tables or views with well-defined data types and constraints. 

• Number, float, date, datetime, timestamp, and interval data types should limit the precision and scale where a valid range should be enforced.

• CHECK constraints on database tables should exist for columns where the list of values or a valid range should be enforced.

• NOT NULL constraints should exist for required columns.

• PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints on database tables and views should be implemented to effortlessly enforce data integrity.

• For multi-column constraints, all involved columns have to be selected in the query and must exist in the grid or form as visible or hidden items to perform the validations.

• For each UNIQUE index, there must exist a corresponding UNIQUE constraint when a duplicate key should be detected.

• For UNIQUE constraints, set the timing property to DEFERRABLE INITIALLY DEFERRED to prevent temporary duplicate key errors while processing the DML of Interactive Grids.

• Constraints may address single or multiple columns in the constraint definitions. Single column constraints are equivalent to APEX Item/column validation rules, while multi-column constraints are equivalent to APEX row validation rules.

Requirements

The Plug-in

Dynamic Action

Form Events

The plug-in can be bound with form inputs using standard events such as Lose Focus and Change. For Interactive Grids, the Lose Focus event must be used to properly display validation results.

• When the event is set to 'Change':

  • 'Value is required' will not be checked when a user activates and leaves a field empty (but will be checked later when the user tries to save the form).

  • Clicking on a quick pick value will trigger the plug-in. The demo app uses this event just to demonstrate incorrect input values.

  • Assignments done by other dynamic actions will trigger the plug-in.

• When the event is set to ‘Lose Focus’:

  • Clicking on a quick pick value will not trigger the plug-in.

Triggering Elements

Automatic Constraint Validation Pro uses triggering elements to evaluate a set of DML form items (or interactive grid columns) to be validated. The Selection Type of the dynamic action is typically the jQuery Selector :input to validate all input fields of the current page, or a Region to validate all input fields of a specific region.

• When the jQuery Selector :input is used, all input items including select lists, popup LOVs, and other item types will trigger the plug-in.

• When the jQuery Selector input is used, only text, number, and date items will trigger the plug-in.

Generic event handler	Region-specific event handler

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

Validation Flow

Validation Flow Diagram

The diagram below presents the plug-in validation flow. Each step of the plug-in validation flow is described in the sections below.

Validation Start

When a dynamic action implementing the Automatic Constraint Validation Pro is fired, the plug-in evaluates items/columns filtered by a combination of dynamic action Event Type and Selection Type. The plug-in supports Lose Focus and Change Events with Selection Type jQuery Selector and Region. The plug-in ignores items when the source is not a database table column. In the case that the plug-in is triggered by a Change event and by a Lose Focus event on the same page item input, then the second duplicate invocation is detected and will be skipped.

Before the plug-in validation begins the Validation start event is triggered on the document - dynamic actions listening to this event have access to special validation objects exposed via this.data. A validation start event can be used to display the loading indicator and the Validation end to remove it immediately after validation is complete.

Client Process

Automatic Constraint Validation is performed on every form item and IG column that triggers the dynamic action. 

• On page load, the plug-in identifies the supported regions and their associated database objects. It then stores information about the record ids and columns that are involved in composite constraint conditions in the client process.

• When the plug-in is triggered, it collects the item name, value, and the type of region of the triggering element, plus information about the record id and other involved item names and values. When the current item occurs in composite constraint conditions, then all involved item values are collected. In Interactive Grids, record ids of updated and deleted records/rows are collected. This information is sent to the server process.

• The server process performs constraint checks and sends back messages with the validation results.

• The client process will receive multiple result messages in the reply and will clear and display inline messages and page notifications.

• A second test for uniqueness is performed by probing new and changed records/rows in the Interactive Grid.

Server Process

The server process accesses the system catalog and the APEX page definition for information about the data types, formats, storage limits, and database constraints. The process then generates and executes probing queries to validate the constraints and sends back messages containing the validation results. 

Pre-Processing

Before the database constraints can be evaluated, input strings have to be converted to database data types. The plug-in identifies the database tables and columns and generates checks for conversion and storage limits of common data types:

• For numeric, date, and timestamp data types, the data must be convertible according to implicit or explicit format strings and NLS settings. Additional limits exist for the storage of columns with string and number data types.

• For numeric, 'year to month' interval, 'day to second' interval, date, and timestamp data types, the precision and scale set storage size limits for the number parts.

• For numeric, date, and timestamp data types, the minimum and maximum range is derived from the page item definitions.

  • The minimum and maximum date value from the form-item or grid-column settings can be an absolute value in the current item date format or 'YYYYMMDDHH24MI' format or a relative value with respect to today's date, such as +7d, or a dynamic value using substitution syntax, providing the substitution returns the correct date format or relative value.

• For date and timestamp data types, the permitted range for the calendar year can be restricted with optional plug-in settings.

• For varchar2 data types, the maximum length in combination with the character semantic (char or byte) defines a storage limit. The column/item setting 'Trim Spaces' is recognized in the length calculation.

Constraint Validation

For the detected table columns the plug-in identifies the constraint conditions and will perform the following checks and produce messages:

• Value required, e.g., not null constraints

• Data conversion with format mask.

• Range limits for numbers depend on precision and scale.

• Compare Text length with maximum length in char or byte semantic for character columns, after trimming leading and/or trailing blanks according to the item setting.

• 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. 

Validation End

When validation is finished the plug-in triggers the Validation End event on document and triggers an item/column event, Validation Passed or Validation Failed, depending on the validation result.

Attributes

Standard

Custom

Application Attributes

The plug-in exposes application attributes under Shared Components \ Component Settings \ UC - Automatic Constraint Validation Pro [Plug-in]

Component Attributes

These attributes are available in page designer for the dynamic action implementing the plug-in. Use these attributes to define the validation process.

Help in the Page Designer

The plug-in attributes are exposed along with help text built into the page designer in the Help tab.

An example of the help for the plug-in attribute Settings \ Validation Settings is presented below.

Events

The plug-in exposes 4 events that can be bound using Oracle APEX dynamic actions.

Event Data

Each validation event has access to data describing the validation state.

Validation Started

The variable this.data describes the item/column that triggered the event. 

Validation Result Object

The variable this.data describes validated items/columns with validation results. The variables type, location, pageItem, message, and unsafe are used to display messages with the Javascript-API apex.message.showErrors.

Validation Ended

An array of Validation Result Objects is returned for each involved item/column.

Validation Passed

A Validation Result Object with this.data.type = ‘valid’ is returned.

Validation Failed

A Validation Result Object with this.data.type = ‘error’ is returned.

Translations

The plug-in supports translations for validation messages. 

• By default, the plug-in produces validation messages in the English language.

• You can replace the provided default messages with specific Text Messages of your language. 

• When you have installed a Translated Version of Oracle APEX by running the load_lang.sql script, default messages for Text Messages beginning with ‘APEX.’ will be provided in many languages. 

• When your application uses multiple languages, you can add translated messages to the Shared Components / Translate Application / Text Messages page for the supported languages.

• To learn more about translations in Oracle APEX please refer to official documentation - Managing Application Globalization

Native DML Validations

To translate ORACLE Database validation messages the current session language has to be set. To translate Item/Column Labels the plug-in uses a translated application (Shadow Application).

Validation Messages

UC Automatic Constraint Validation Pro provides and uses the following Text Messages in 10 languages (English, German, Spanish, French, Italian, Japanese, Korean, Brazilian Portuguese, Simplified Chinese, and Traditional Chinese):

Requirements

To translate validations ensure that:

• application attribute Globalization \ Application Language Derived From is set and the application supports translations

• 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 and translated application (Shadow Application) has been published.

• You can add or modify translated validation messages in the installation script UC_Constraint_Validation_Messages.sql and then add this script to Shared Components / Supporting Objects / Installation Scripts of your application. The messages will be listed in the page Shared Components / Translate Application / Text Messages after installation.

• To translate ORACLE DML validation messages the current session language has to be set.

• Alternatively, you can translate the English Message Text for Translatable Messages that begin with ‘UC.’ as Shared Components / Translate Application / Dynamic Translations into your application language.

Usage Guide

This section describes three basic examples of implementing the plug-in to validate a simple form and interactive grid columns. The usage guide shows a basic implementation of the plug-in but does not describe the plug-in attributes or ideas on how the plug-in can be used.

Validation on Page 0

You can define a single Dynamic Action on page 0 that is triggered by the event Lose Focus and jQuery Selector :input . This plug-in will be invoked on any input field of your application. The Server-side Condition disables the plug-in on page 0 when you have defined other plug-in invocations for the current application page.

Implementing the Plug-in

To implement the plug-in, creating a new dynamic action is required. Follow the steps below:
Create a new dynamic action on page 0

• Name: Validate Constraints

• When

  • Event: Loose Focus

  • Selection Type : jQuery Selector

  • jQuery Selector : :input

• Create a new action

  • Action: UC - Automatic Constraint Validation

• Server-side Condition

  • Type: PL/SQL Expression

  • PL/SQL Expression : UC_Constraint_Validation.Current_Page_Has_No_Plugin_DA

Page 0 in page designer is presented below:

Add an informal list of rules for the current page to see the applied constraints on page 0

• Add a new region in template position 'Body 3'

• Title: Constraint Checks

• Type: Classic Report

• Source

  • Type: SQL Query

  • SQL Query:

  • select constraint_icon icon, label, message, errorClass, table_name, column_name, constraint_type, constraint_priority
    from table (UC_Constraint_Validation.validate_form_items_list);

• Appearance

  • Template : Collapsible

  • General : Remember Collapsible State

  • Default State: Collapsed

• Server-side Condition

  • Type : PL/SQL Expression

  • PL/SQL Expression : UC_Constraint_Validation.Current_Page_Has_Db_Items

• Attributes

  • Number of Rows : 50

• Columns

  • ICON

    • HTML Expression : <span class="t-Icon fa #ICON#"></span>

  • TABLE_NAME

    • Sorting / Default Sequence : 1

  • COLUMN_NAME

    • Sorting / Default Sequence : 2

  • CONSTRAINT_PRIORITY

    • Sorting / Default Sequence : 3

Page 0 in page designer is presented below:

Validation of specific DML Form or Interactive Grid region

Implementing the Plug-in

To implement the plug-in, creating a new dynamic action is required. Follow the steps below:
Create a new dynamic action on an application page with a DML Form or Interactive Grid

• Name: Validate Constraints

• When

  • Event : Loose Focus / Change

  • Selection Type : Region

  • Region Name : <choose a region>

• Create a new action

  • Action: UC - Automatic Constraint Validation

PL/SQL library

The library CONSTRAINT_VALIDATION_PLUGIN provides functions to control the rendering of the dynamic action on page 0; control the rendering of a Constraints Check List, and a procedure that can perform generic row validations when a form is saved.

Security Model

• On the frontend, the plug-in runs in the context of the APEX Session and used public APEX Javascript API functions to call an AJAX process for validations, display messages, display debug messages, trigger custom events and handle errors.

• On the backend, the plug-in runs in the context of the APEX Session and used public Views and API functions to handle the validation requests, and write trace information into the APEX Debug Log. All passed parameters of the process are handled carefully using DBMS_ASSERT.ENQUOTE_NAME, DBMS_ASSERT.ENQUOTE_LITERAL and bind variables to avoid potential SQL injections.

Data Types

• Data type details of the table columns used in an APEX form from SYS.ALL_TAB_COLUMNS.
  TYPE table_columns_rec IS RECORD (
OWNER ALL_TAB_COLUMNS.OWNER%TYPE,
TABLE_NAME ALL_TAB_COLUMNS.TABLE_NAME%TYPE,
COLUMN_NAME ALL_TAB_COLUMNS.COLUMN_NAME%TYPE,
DATA_TYPE ALL_TAB_COLUMNS.DATA_TYPE%TYPE,
CHAR_LENGTH ALL_TAB_COLUMNS.CHAR_LENGTH%TYPE,
CHAR_USED ALL_TAB_COLUMNS.CHAR_USED%TYPE,
DATA_PRECISION ALL_TAB_COLUMNS.DATA_PRECISION%TYPE,
DATA_SCALE ALL_TAB_COLUMNS.DATA_SCALE%TYPE,
NULLABLE ALL_TAB_COLUMNS.NULLABLE%TYPE,
DEFAULT_ON_NULL ALL_TAB_COLUMNS.DEFAULT_ON_NULL%TYPE,
DEFAULT_LENGTH ALL_TAB_COLUMNS.DEFAULT_LENGTH%TYPE
);
TYPE table_columns_tab IS TABLE OF table_columns_rec;

• Constraint details of the tables used in an APEX form from SYS.ALL_CONSTRAINTS.
  TYPE table_constraints_rec IS RECORD (
OWNER ALL_CONSTRAINTS.OWNER%TYPE,
TABLE_NAME ALL_CONSTRAINTS.TABLE_NAME%TYPE,
COLUMN_NAME ALL_CONSTRAINTS.OWNER%TYPE,
CONSTRAINT_NAME ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE,
COLUMN_NAMES VARCHAR2(4000),
COL_COUNT NUMBER,
CONSTRAINT_TYPE ALL_CONSTRAINTS.CONSTRAINT_TYPE%TYPE,
SEARCH_CONDITION VARCHAR2(4000),
R_OWNER ALL_CONSTRAINTS.OWNER%TYPE,
R_TABLE_NAME ALL_CONSTRAINTS.TABLE_NAME%TYPE,
R_COLUMN_NAMES VARCHAR2(4000)
);
TYPE table_constraints_tab IS TABLE OF table_constraints_rec;

• Validation result records describe the details of a passed or failed constraint.
  TYPE validation_result_rec is record (
PAGEITEM VARCHAR2(128),
MESSAGE VARCHAR2(2000),
ERRORCLASS VARCHAR2(12), -- required, format, length, range, item_range, uniqueness, foreign_key, rule, sqlerrm
CONSTRAINT_ICON VARCHAR2(32),
CONSTRAINT_PRIORITY NUMBER(2),
CONSTRAINT_NAME ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE,
CONSTRAINT_TYPE ALL_CONSTRAINTS.CONSTRAINT_TYPE%TYPE,
COLUMN_NAME ALL_TAB_COLUMNS.COLUMN_NAME%TYPE,
TABLE_NAME ALL_TAB_COLUMNS.TABLE_NAME%TYPE,
LABEL VARCHAR2(2000),
RECORD_ID VARCHAR2(2000),
PASSED VARCHAR2(1), -- Y or N
CONS_COLUMN_NAMES VARCHAR2(4000),
CONS_ITEM_NAMES VARCHAR2(4000)
);
TYPE validation_result_tab is table of validation_result_rec;

API Functions and Procedures

• Rendering function of the plug-in.
  FUNCTION Render_Validation (
p_dynamic_action IN apex_plugin.t_dynamic_action,
p_plugin IN apex_plugin.t_plugin
) return apex_plugin.t_dynamic_action_render_result;

• AJAX processing function of the plug-in.
  FUNCTION Ajax_Validation (
p_dynamic_action IN apex_plugin.t_dynamic_action,
p_plugin IN apex_plugin.t_plugin
) return apex_plugin.t_dynamic_action_ajax_result;
  

• The function returns true when this plug-in is used on the current page.
  Useful as a condition for enabling this plug-in on page 0.
  FUNCTION Current_Page_Has_No_Plugin_DA RETURN BOOLEAN;

• The function returns true when the current page has input fields for database columns.
  Useful as a condition for displaying a report with validate_form_items_list
  FUNCTION Current_Page_Has_Db_Items RETURN BOOLEAN;

• The function returns a list of validations for the current page forms or IGs
  FUNCTION validate_form_items_list (
p_Application_ID IN NUMBER DEFAULT NV('APP_ID'),
p_Page_ID IN NUMBER DEFAULT NV('APP_PAGE_ID'),
p_Item_Name IN VARCHAR2 DEFAULT NULL, -- page item name
p_Item_Value IN VARCHAR2 DEFAULT NULL, -- page item value
p_Source_Type IN VARCHAR2 DEFAULT NULL, -- NATIVE_FORM or NATIVE_IG
p_Table_Owner IN VARCHAR2 DEFAULT NULL, -- base table owner
p_Table_Name IN VARCHAR2 DEFAULT NULL, -- base table name
p_Check_Col_Types IN VARCHAR2 DEFAULT 'N', -- check data types of base table columns
p_Minimum_Year IN NUMBER DEFAULT NULL, -- minimum year permitted in calendar dates.
p_Maximum_Year IN NUMBER DEFAULT NULL, -- maximum year permitted in calendar dates.
p_Call_Modus IN VARCHAR2 DEFAULT 'LIST' -- AJAX or SUBMIT or LIST
) RETURN validation_result_tab PIPELINED;

• Procedure to add Constraints for Unique Indexes without matching Constraint.
  The statements are listed with dbms_output and are executed when p_Do_Execute = Y.
  PROCEDURE Add_Constraints_for_Unique_Indexes (
p_Table_Name IN VARCHAR2 DEFAULT NULL,
p_Do_Execute IN VARCHAR2 DEFAULT 'Y'
);

• Procedure to validate a submitted form page.
  Error messages will be displayed via the apex_error.add_error API.
  PROCEDURE validate_form_submit_process (
p_Table_Owner IN VARCHAR2 DEFAULT NULL, -- base table owner
p_Table_Name IN VARCHAR2 DEFAULT NULL, -- base table name
p_Check_Col_Types IN VARCHAR2 DEFAULT 'N', -- check data types of base table columns
p_Minimum_Year IN NUMBER DEFAULT NULL, -- minimum year permitted in calendar dates.
p_Maximum_Year IN NUMBER DEFAULT NULL, -- maximum year permitted in calendar dates.
p_display_location IN VARCHAR2 DEFAULT apex_error.c_inline_with_field_and_notif
);

• Refresh columns and constraints cache for all tables or for a specific table name of the table owner.
  PROCEDURE Cache_Table_Columns ( p_Table_Owner IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'), p_Table_Name IN VARCHAR2 DEFAULT NULL );

• Run scheduler job to refresh columns and constraints cache for all tables of the current schema.
  PROCEDURE Refresh_After_DDL_Job;

Sample Application

The plug-in is delivered with a sample application that describes all of the features of UC - Constraint Validation Pro with example implementations. The sample application describes and implements the following topics:

The Basics

The basics describe how the plug-in works, how the plug-in events can be used, and allow live testing of the plug-in’s core functionality - evaluating native Oracle DML validations and validation conditions for database constraints with a client-server-process.

• Flow

• Validations

• Conditions

• Events

• Translations

Plug-in Attributes

The plug-in attributes pages describes and shows example implementations of each of the plug-in attributes.

• Table Owner & Name

• Check data types of base table columns

• Minimum & Maximum Year

• Notification

• Refresh Cache

• Wait for Results

• Stop on Error

Triggering Element

The triggering element pages describe the supported combinations of form events and triggering elements.

• Event

• Selection Type

Validation on Submit

The plug-ins PL/SQL library provides the procedure UC_Constraint_Validation.validate_form_submit_process for validation on submit of a DML-Form. This method produces no extra network traffic but provides a simple interface for whole form validation with inline messages for associated items. Learn about

• Procedure Prototype

• Implementation