List of values (LOVs) for your APEX app from CHECK (COL IN (...)) constraints

Some time ago a customer asked me to build him an APEX app with a special feature. The CHECK constraints of the application's SQL tables can be altered in the future, and the APEX form pages should require no changes in that case. One of the problems I had to solve was to use dynamic LOV definitions instead of static LOV definitions since they will outdate. For columns with CHECK (COL IN (...) constraints, the view V_LOV_FROM_CHECK_IN_LIST  produces dynamic LOVs with values from the constraint definition. (You must be connected to at least an Oracle Database 18c to use column SEARCH_CONDITION_VC and APEX 5.1 to use the required apex_string.split function.)

 CREATE OR REPLACE VIEW V_LOV_FROM_CHECK_IN_LIST (Table_Name, Column_Name, Display_Value, Column_Value)   
 AS   
  SELECT Table_Name, Column_Name,  
   INITCAP(Column_Value) Display_Value,  
   Column_Value  
 FROM (SELECT Table_Name, Column_Name,   
     REGEXP_REPLACE(TRIM(Column_Value),                      -- remove quotes  
          '^''(.*)''$', '\1'  
     ) Column_Value  
   FROM ( -- convert values to rows  
     SELECT C.Table_Name, B.Column_Name,   
                REGEXP_REPLACE( C.SEARCH_CONDITION_VC,   
                     B.Column_Name || '\s+IN\s*\((.+)\)\s*$',   
                     '\1', 1, 1, 'i'  
       ) CHECK_IN_LIST                                    -- extract values list  
     FROM SYS.USER_CONSTRAINTS C  
     JOIN SYS.USER_CONS_COLUMNS B   
      ON C.Constraint_Name = B.Constraint_Name AND C.Table_Name = B.Table_Name  
     WHERE C.CONSTRAINT_TYPE = 'C'                         -- check constraint  
     AND C.Table_Name NOT LIKE 'BIN$%'                     -- this table is not in the recyclebin  
           AND REGEXP_INSTR( C.SEARCH_CONDITION_VC,           -- find CHECK (COL IN (...)) rules  
                B.Column_Name || '\s+IN\s*\(.+\)\s*$',   
       1, 1, 1, 'i'  
     ) > 0   
   ) S,  
   TABLE( apex_string.split(S.CHECK_IN_LIST, ',')) P     -- extract individual (quoted) values  
 );  
 COMMENT ON TABLE V_LOV_FROM_CHECK_IN_LIST IS 'List of values from simple CHECK (COL IN (...)) constraint for each table column.';  

Using the following example table:

 CREATE TABLE TEST_TABLE (   
      ID NUMBER CONSTRAINT TEST_TABLE_PK PRIMARY KEY,   
      ZOOM_FACTOR FLOAT DEFAULT 1 NOT NULL CONSTRAINT TEST_TABLE_ZOOM_FACTOR_CK CHECK ( ZOOM_FACTOR IN (1, 2, 4, 8) ),  
      EXCLUDE_SINGLES VARCHAR2(5) DEFAULT 'NO' NOT NULL CONSTRAINT TEST_TABLE_EXCL_SINGLE_CK CHECK ( Exclude_Singles IN ('YES','NO') ),  
      EDGE_LABELS VARCHAR2(5) DEFAULT 'YES' NOT NULL      CONSTRAINT TEST_TABLE_EDGE_LABELS_CK CHECK ( Edge_Labels IN ('YES','NO','BOXES') ),  
      EXCITE_METHOD VARCHAR2(50) DEFAULT 'none' NOT NULL CONSTRAINT TEST_TABLE_EXCITE_METHOD_CK   
           CHECK (EXCITE_METHOD IN ('none', 'selected', 'downstream', 'upstream', 'connected'))  
 );  

Now you can replace the static LOV for column EXCITE_METHOD for example with a dynamic LOV that is getting the values from your table definitions. You can do this replacement for all table-columns with CHECK (col IN (...)) constraints that are listed by the view.

 SELECT Display_Value d, Column_Value r  
 FROM V_LOV_FROM_CHECK_IN_LIST
 WHERE Table_Name = 'TEST_TABLE'  
 AND Column_Name = 'EXCITE_METHOD';  

Old static LOV definition

New dynamic LOV definition

Close a APEX modal dialog, by clicking on the main window

When your #orclAPEX app shows modal dialog windows, the app user has to click a button or the little X-icon in the upper right corner to close this dialog. Moving the mouse to these buttons takes your user's time.

You can enable the user to close a dialog window, by clicking or tapping on the main window. Put the following javascript code in the 'Execute when Page Loads' section of your APEX page:

$(document).on('click', 'div.ui-widget-overlay.ui-front', function(){

    apex.navigation.dialog.close(true, false);

});