PL/SQL

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;