Schema & Data Browser - Part 4

A new Beta Release Version 1.9.10 of the Schema & Data Browser APEX application has been released on https://github.com/dstrack/Schema_Data_Browser

here is are links to the demo 
https://yboieokpjit8ejk-strackdev02.adb.eu-frankfurt-1.oraclecloudapps.com/ords/f?p=2000:101
https://apex.oracle.com/pls/apex/f?p=48950:101

Many of the concepts of the data browser that were described in the previous blogpost are now implemented and can be demonstrated.

Data Browser Improvements:

• Improved query generation for import/export views and for history, counters, links, totals, and folder path LOVs.
• Better joins descriptions for Import / Export forms in the Actions / Columns modal dialog.
• Improved navigation in the Actions / Show Query modal dialog. The view mode can be chosen in a tab-pane.
• Improved trigger generation for updatable views. Added a key lookup for composite keys with a file path.
• Faster installation and refresh of materialized views.

• Improved page load performance for home page and data browser.
• Improved progress bar display for scheduler jobs.
• Improved query generation for LOVs. Up to 4 levels of recursion are now resolved to build hierarchical and path descriptions.
• Manage Schema Tasks: The processes to add primary and natural keys have been improved. New buttons enable you to generate and download updatable views and UI-defaults.
• Improved refresh of report regions.
• Improved Dialog for Sorting and Grouping
• Early validation of entered data against the schema constraints and size limits via AJAX calls is now enabled.
• On the home page, the list of My Databases is now displayed when the application is using APEX authentication. This list informed you about the accessible schemas and their space usage and it enables you to switch the active schema by clicking on an entry. Multiple schemas have to be assigned to the APEX workspace and the supporting objects of the application have to be installed in those schemas to make them accessible for this application.

Sources:

• The PL/SQL packages weco_mail, weco_auth_mgr, and table app_preferences have been removed.
• The Pre-Installation-Validations for the privileges to execute dbms_lock, dbms_tcp, and dbms_smtp have been removed.
• In the custom authorization scheme, the package apex_mail is used instead of weco_mail.
• All of the PL/SQL and javascript source files that are installed by the application are listed in the repository sources directory of the git repository.
• Removed references to apex_ajax and instead use apex.server.process and plugin apex-plugin-clob-load.
• the demo time period is now 2 months.

Setting up a new Schema

This application enables you and other users with an APEX developer account in your workspace to add new schemas at runtime. I believe that it is a got practice to create at least one new schema name for each independent application. When you have watched the video from the fabulous Mr. Connor McDonald 'From Doors Wide Open ... to By Invitation Only!' you may come to the same conclusion.

https://youtu.be/OF7qR5lAmiw

Besides the advantages of modularization and separation of concerns, you can get rid of prefixes for your table names and can use namespaces instead. For example, the table name oehr_Employees becomes oehr.Employees.

The following video demonstrates how to add a new (database) schema named 'HR Data'. the user has to enter a password for the database user, that can be used in SQL-Developer and a second password for the current user for the schema admin account (that is stored as a hash value in the table app_users in that schema.

The video demonstrated the Installation of a sample dataset and setting of the table prefixes to improve the displayed names. When that is done we click on the entry 'Hr Data' in the 'My Databases' list to activate the database as current. A background job will be started that refreshes the table and columns system statistics and builds a fast access cache from the data dictionary system views in a set of materialized views. 

The next video is about Manage Schema Task: Define recursive natural keys to produce detailed record descriptions and define mandatory key columns to improve the key lookup in the triggers of updateable views. Then see an example updatable view Voehr_Employees_Imp with a trigger to handle the DML operations. 

 CREATE OR REPLACE VIEW VOEHR_EMPLOYEES_IMP  
   ( LINK_ID$, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_JOB_ID, SALARY, COMMISSION_PCT, MANAGER_EMAIL, DEPARTMENT_DEPARTMENT_NAME, DEPARTMENT_LOC_STREET_ADDRESS, DEPARTMENT_LOCATI_POSTAL_CODE, DEPARTMENT_LOCATION_CITY, DEPARTMENT_LOC_STATE_PROVINCE, DEPARTMENT_LOCAT_COUNTRY_NAME, DEPARTMENT_LOCATI_REGION_NAME  
   , CONSTRAINT VOEHR_EMPLOYEES_IMP_PK PRIMARY KEY (LINK_ID$) RELY DISABLE )   
  AS   
 SELECT    
   A.EMPLOYEE_ID LINK_ID$,  
   A.FIRST_NAME,  
   A.LAST_NAME,  
   A.EMAIL,  
   A.PHONE_NUMBER,  
   A.HIRE_DATE,  
   B.JOB_ID JOB_JOB_ID,  
   A.SALARY,  
   A.COMMISSION_PCT,  
   C.EMAIL MANAGER_EMAIL,  
   D.DEPARTMENT_NAME DEPARTMENT_DEPARTMENT_NAME,  
   D_C.STREET_ADDRESS DEPARTMENT_LOC_STREET_ADDRESS,  
   D_C.POSTAL_CODE DEPARTMENT_LOCATI_POSTAL_CODE,  
   D_C.CITY DEPARTMENT_LOCATION_CITY,  
   D_C.STATE_PROVINCE DEPARTMENT_LOC_STATE_PROVINCE,  
   D_C_B.COUNTRY_NAME DEPARTMENT_LOCAT_COUNTRY_NAME,  
   D_C_B_B.REGION_NAME DEPARTMENT_LOCATI_REGION_NAME  
 FROM OEHR_EMPLOYEES A  
 JOIN OEHR_JOBS B ON B.JOB_ID = A.JOB_ID  
 LEFT OUTER JOIN OEHR_EMPLOYEES C ON C.EMPLOYEE_ID = A.MANAGER_ID  
 LEFT OUTER JOIN OEHR_DEPARTMENTS D ON D.DEPARTMENT_ID = A.DEPARTMENT_ID  
 LEFT OUTER JOIN OEHR_LOCATIONS D_C ON D_C.LOCATION_ID = D.LOCATION_ID  
 LEFT OUTER JOIN OEHR_COUNTRIES D_C_B ON D_C_B.COUNTRY_ID = D_C.COUNTRY_ID  
 LEFT OUTER JOIN OEHR_REGIONS D_C_B_B ON D_C_B_B.REGION_ID = D_C_B.REGION_ID;  
   
 CREATE OR REPLACE TRIGGER VOEHR_EMPLOYEES_IMP_TR INSTEAD OF INSERT OR UPDATE OR DELETE ON VOEHR_EMPLOYEES_IMP FOR EACH ROW   
 DECLARE   
   v_row OEHR_EMPLOYEES%ROWTYPE;  
   v_DEPARTMENT_LOCATION_ID OEHR_LOCATIONS.LOCATION_ID%TYPE;  
   v_DEPARTMENT_LOCATION_REGION_ID OEHR_REGIONS.REGION_ID%TYPE;  
   v_DEPARTMENT_LOCATIO_COUNTRY_ID OEHR_COUNTRIES.COUNTRY_ID%TYPE;  
 BEGIN  
   if DELETING then   
     DELETE FROM OEHR_EMPLOYEES A   
     WHERE A.EMPLOYEE_ID = :new.LINK_ID$;  
     return;  
   end if;  
   v_row.EMPLOYEE_ID           := :new.LINK_ID$;  
   v_row.FIRST_NAME            := :new.FIRST_NAME;  
   v_row.LAST_NAME            := :new.LAST_NAME;  
   v_row.EMAIL              := :new.EMAIL;  
   v_row.PHONE_NUMBER           := :new.PHONE_NUMBER;  
   v_row.HIRE_DATE            := :new.HIRE_DATE;  
   v_row.SALARY              := :new.SALARY;  
   v_row.COMMISSION_PCT          := :new.COMMISSION_PCT;  
   if :new.DEPARTMENT_LOCATI_REGION_NAME IS NOT NULL then   
    begin  
     SELECT D_C_B_B.REGION_ID INTO v_DEPARTMENT_LOCATION_REGION_ID  
     FROM OEHR_REGIONS D_C_B_B   
     WHERE D_C_B_B.REGION_NAME = :new.DEPARTMENT_LOCATI_REGION_NAME;  
    exception when NO_DATA_FOUND then  
     INSERT INTO OEHR_REGIONS(REGION_NAME)  
     VALUES (:new.DEPARTMENT_LOCATI_REGION_NAME)  
     RETURNING (REGION_ID) INTO v_DEPARTMENT_LOCATION_REGION_ID;  
    end;  
   end if;  
   if :new.MANAGER_EMAIL IS NOT NULL then   
     SELECT C.EMPLOYEE_ID INTO v_row.MANAGER_ID  
     FROM OEHR_EMPLOYEES C   
     WHERE C.EMAIL = :new.MANAGER_EMAIL;  
   end if;  
   if :new.JOB_JOB_ID IS NOT NULL then   
     SELECT B.JOB_ID INTO v_row.JOB_ID  
     FROM OEHR_JOBS B   
     WHERE B.JOB_ID = :new.JOB_JOB_ID;  
   end if;  
   if :new.DEPARTMENT_LOCAT_COUNTRY_NAME IS NOT NULL  
   or v_DEPARTMENT_LOCATION_REGION_ID IS NOT NULL then   
     SELECT D_C_B.COUNTRY_ID INTO v_DEPARTMENT_LOCATIO_COUNTRY_ID  
     FROM OEHR_COUNTRIES D_C_B   
     WHERE (D_C_B.COUNTRY_NAME = :new.DEPARTMENT_LOCAT_COUNTRY_NAME OR D_C_B.COUNTRY_NAME IS NULL AND :new.DEPARTMENT_LOCAT_COUNTRY_NAME IS NULL)  
     AND (D_C_B.REGION_ID = v_DEPARTMENT_LOCATION_REGION_ID OR D_C_B.REGION_ID IS NULL AND v_DEPARTMENT_LOCATION_REGION_ID IS NULL);  
   end if;  
   if :new.DEPARTMENT_LOC_STREET_ADDRESS IS NOT NULL  
   or :new.DEPARTMENT_LOCATI_POSTAL_CODE IS NOT NULL  
   or :new.DEPARTMENT_LOCATION_CITY IS NOT NULL  
   or :new.DEPARTMENT_LOC_STATE_PROVINCE IS NOT NULL  
   or v_DEPARTMENT_LOCATIO_COUNTRY_ID IS NOT NULL then   
    begin  
     SELECT D_C.LOCATION_ID INTO v_DEPARTMENT_LOCATION_ID  
     FROM OEHR_LOCATIONS D_C   
     WHERE (D_C.STREET_ADDRESS = :new.DEPARTMENT_LOC_STREET_ADDRESS OR D_C.STREET_ADDRESS IS NULL AND :new.DEPARTMENT_LOC_STREET_ADDRESS IS NULL)  
     AND (D_C.POSTAL_CODE = :new.DEPARTMENT_LOCATI_POSTAL_CODE OR D_C.POSTAL_CODE IS NULL AND :new.DEPARTMENT_LOCATI_POSTAL_CODE IS NULL)  
     AND (D_C.CITY = :new.DEPARTMENT_LOCATION_CITY OR :new.DEPARTMENT_LOCATION_CITY IS NULL)  
     AND (D_C.STATE_PROVINCE = :new.DEPARTMENT_LOC_STATE_PROVINCE OR D_C.STATE_PROVINCE IS NULL AND :new.DEPARTMENT_LOC_STATE_PROVINCE IS NULL)  
     AND (D_C.COUNTRY_ID = v_DEPARTMENT_LOCATIO_COUNTRY_ID OR D_C.COUNTRY_ID IS NULL AND v_DEPARTMENT_LOCATIO_COUNTRY_ID IS NULL);  
    exception when NO_DATA_FOUND then  
     INSERT INTO OEHR_LOCATIONS(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)  
     VALUES (:new.DEPARTMENT_LOC_STREET_ADDRESS, :new.DEPARTMENT_LOCATI_POSTAL_CODE, :new.DEPARTMENT_LOCATION_CITY, :new.DEPARTMENT_LOC_STATE_PROVINCE, v_DEPARTMENT_LOCATIO_COUNTRY_ID)  
     RETURNING (LOCATION_ID) INTO v_DEPARTMENT_LOCATION_ID;  
    end;  
   end if;  
   if :new.DEPARTMENT_DEPARTMENT_NAME IS NOT NULL  
   or v_DEPARTMENT_LOCATION_ID IS NOT NULL then   
    begin  
     SELECT D.DEPARTMENT_ID INTO v_row.DEPARTMENT_ID  
     FROM OEHR_DEPARTMENTS D   
     WHERE (D.DEPARTMENT_NAME = :new.DEPARTMENT_DEPARTMENT_NAME OR :new.DEPARTMENT_DEPARTMENT_NAME IS NULL)  
     AND (D.LOCATION_ID = v_DEPARTMENT_LOCATION_ID OR D.LOCATION_ID IS NULL AND v_DEPARTMENT_LOCATION_ID IS NULL);  
    exception when NO_DATA_FOUND then  
     INSERT INTO OEHR_DEPARTMENTS(DEPARTMENT_NAME, LOCATION_ID)  
     VALUES (:new.DEPARTMENT_DEPARTMENT_NAME, v_DEPARTMENT_LOCATION_ID)  
     RETURNING (DEPARTMENT_ID) INTO v_row.DEPARTMENT_ID;  
    end;  
   end if;  
   
   if INSERTING then   
     INSERT INTO OEHR_EMPLOYEES VALUES v_row;  
   else   
     UPDATE OEHR_EMPLOYEES SET   
       FIRST_NAME = v_row.FIRST_NAME,  
       LAST_NAME = v_row.LAST_NAME,  
       EMAIL = v_row.EMAIL,  
       PHONE_NUMBER = v_row.PHONE_NUMBER,  
       HIRE_DATE = v_row.HIRE_DATE,  
       JOB_ID = v_row.JOB_ID,  
       SALARY = v_row.SALARY,  
       COMMISSION_PCT = v_row.COMMISSION_PCT,  
       MANAGER_ID = v_row.MANAGER_ID,  
       DEPARTMENT_ID = v_row.DEPARTMENT_ID  
     WHERE EMPLOYEE_ID = :new.LINK_ID$;  
   end if;  
 END VOEHR_EMPLOYEES_IMP_TR;  
 /

When the columns of the natural keys are defined as mandatory (NOT NULL), then the lookup operations require fewer OR conditions to handle NULL cases.

   

CREATE OR REPLACE VIEW VOEHR_EMPLOYEES_IMP  
   ( LINK_ID$, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_JOB_ID, SALARY, COMMISSION_PCT, MANAGER_EMAIL, DEPARTMENT_DEPARTMENT_NAME, DEPARTMENT_LOC_STREET_ADDRESS, DEPARTMENT_LOCATI_POSTAL_CODE, DEPARTMENT_LOCATION_CITY, DEPARTMENT_LOC_STATE_PROVINCE, DEPARTMENT_LOCAT_COUNTRY_NAME, DEPARTMENT_LOCATI_REGION_NAME  
   , CONSTRAINT VOEHR_EMPLOYEES_IMP_PK PRIMARY KEY (LINK_ID$) RELY DISABLE )   
  AS   
 SELECT    
   A.EMPLOYEE_ID LINK_ID$,  
   A.FIRST_NAME,  
   A.LAST_NAME,  
   A.EMAIL,  
   A.PHONE_NUMBER,  
   A.HIRE_DATE,  
   B.JOB_ID JOB_JOB_ID,  
   A.SALARY,  
   A.COMMISSION_PCT,  
   C.EMAIL MANAGER_EMAIL,  
   D.DEPARTMENT_NAME DEPARTMENT_DEPARTMENT_NAME,  
   D_C.STREET_ADDRESS DEPARTMENT_LOC_STREET_ADDRESS,  
   D_C.POSTAL_CODE DEPARTMENT_LOCATI_POSTAL_CODE,  
   D_C.CITY DEPARTMENT_LOCATION_CITY,  
   D_C.STATE_PROVINCE DEPARTMENT_LOC_STATE_PROVINCE,  
   D_C_B.COUNTRY_NAME DEPARTMENT_LOCAT_COUNTRY_NAME,  
   D_C_B_B.REGION_NAME DEPARTMENT_LOCATI_REGION_NAME  
 FROM OEHR_EMPLOYEES A  
 JOIN OEHR_JOBS B ON B.JOB_ID = A.JOB_ID  
 LEFT OUTER JOIN OEHR_EMPLOYEES C ON C.EMPLOYEE_ID = A.MANAGER_ID  
 LEFT OUTER JOIN OEHR_DEPARTMENTS D ON D.DEPARTMENT_ID = A.DEPARTMENT_ID  
 LEFT OUTER JOIN OEHR_LOCATIONS D_C ON D_C.LOCATION_ID = D.LOCATION_ID  
 LEFT OUTER JOIN OEHR_COUNTRIES D_C_B ON D_C_B.COUNTRY_ID = D_C.COUNTRY_ID  
 LEFT OUTER JOIN OEHR_REGIONS D_C_B_B ON D_C_B_B.REGION_ID = D_C_B.REGION_ID;  
   
 CREATE OR REPLACE TRIGGER VOEHR_EMPLOYEES_IMP_TR INSTEAD OF INSERT OR UPDATE OR DELETE ON VOEHR_EMPLOYEES_IMP FOR EACH ROW   
 DECLARE   
   v_row OEHR_EMPLOYEES%ROWTYPE;  
   v_DEPARTMENT_LOCATION_ID OEHR_LOCATIONS.LOCATION_ID%TYPE;  
   v_DEPARTMENT_LOCATION_REGION_ID OEHR_REGIONS.REGION_ID%TYPE;  
   v_DEPARTMENT_LOCATIO_COUNTRY_ID OEHR_COUNTRIES.COUNTRY_ID%TYPE;  
 BEGIN  
   if DELETING then   
     DELETE FROM OEHR_EMPLOYEES A   
     WHERE A.EMPLOYEE_ID = :new.LINK_ID$;  
     return;  
   end if;  
   v_row.EMPLOYEE_ID           := :new.LINK_ID$;  
   v_row.FIRST_NAME            := :new.FIRST_NAME;  
   v_row.LAST_NAME            := :new.LAST_NAME;  
   v_row.EMAIL              := :new.EMAIL;  
   v_row.PHONE_NUMBER           := :new.PHONE_NUMBER;  
   v_row.HIRE_DATE            := :new.HIRE_DATE;  
   v_row.SALARY              := :new.SALARY;  
   v_row.COMMISSION_PCT          := :new.COMMISSION_PCT;  
   if :new.DEPARTMENT_LOCATI_REGION_NAME IS NOT NULL then   
    begin  
     SELECT D_C_B_B.REGION_ID INTO v_DEPARTMENT_LOCATION_REGION_ID  
     FROM OEHR_REGIONS D_C_B_B   
     WHERE D_C_B_B.REGION_NAME = :new.DEPARTMENT_LOCATI_REGION_NAME;  
    exception when NO_DATA_FOUND then  
     INSERT INTO OEHR_REGIONS(REGION_NAME)  
     VALUES (:new.DEPARTMENT_LOCATI_REGION_NAME)  
     RETURNING (REGION_ID) INTO v_DEPARTMENT_LOCATION_REGION_ID;  
    end;  
   end if;  
   if :new.MANAGER_EMAIL IS NOT NULL then   
     SELECT C.EMPLOYEE_ID INTO v_row.MANAGER_ID  
     FROM OEHR_EMPLOYEES C   
     WHERE C.EMAIL = :new.MANAGER_EMAIL;  
   end if;  
   if :new.JOB_JOB_ID IS NOT NULL then   
     SELECT B.JOB_ID INTO v_row.JOB_ID  
     FROM OEHR_JOBS B   
     WHERE B.JOB_ID = :new.JOB_JOB_ID;  
   end if;  
   if :new.DEPARTMENT_LOCAT_COUNTRY_NAME IS NOT NULL  
   and v_DEPARTMENT_LOCATION_REGION_ID IS NOT NULL then   
     SELECT D_C_B.COUNTRY_ID INTO v_DEPARTMENT_LOCATIO_COUNTRY_ID  
     FROM OEHR_COUNTRIES D_C_B   
     WHERE D_C_B.COUNTRY_NAME = :new.DEPARTMENT_LOCAT_COUNTRY_NAME  
     AND D_C_B.REGION_ID = v_DEPARTMENT_LOCATION_REGION_ID;  
   end if;  
   if :new.DEPARTMENT_LOC_STREET_ADDRESS IS NOT NULL  
   or :new.DEPARTMENT_LOCATI_POSTAL_CODE IS NOT NULL  
   or :new.DEPARTMENT_LOCATION_CITY IS NOT NULL  
   or :new.DEPARTMENT_LOC_STATE_PROVINCE IS NOT NULL  
   or v_DEPARTMENT_LOCATIO_COUNTRY_ID IS NOT NULL then   
    begin  
     SELECT D_C.LOCATION_ID INTO v_DEPARTMENT_LOCATION_ID  
     FROM OEHR_LOCATIONS D_C   
     WHERE (D_C.STREET_ADDRESS = :new.DEPARTMENT_LOC_STREET_ADDRESS OR :new.DEPARTMENT_LOC_STREET_ADDRESS IS NULL)  
     AND (D_C.POSTAL_CODE = :new.DEPARTMENT_LOCATI_POSTAL_CODE OR D_C.POSTAL_CODE IS NULL AND :new.DEPARTMENT_LOCATI_POSTAL_CODE IS NULL)  
     AND (D_C.CITY = :new.DEPARTMENT_LOCATION_CITY OR :new.DEPARTMENT_LOCATION_CITY IS NULL)  
     AND (D_C.STATE_PROVINCE = :new.DEPARTMENT_LOC_STATE_PROVINCE OR D_C.STATE_PROVINCE IS NULL AND :new.DEPARTMENT_LOC_STATE_PROVINCE IS NULL)  
     AND (D_C.COUNTRY_ID = v_DEPARTMENT_LOCATIO_COUNTRY_ID OR v_DEPARTMENT_LOCATIO_COUNTRY_ID IS NULL);  
    exception when NO_DATA_FOUND then  
     INSERT INTO OEHR_LOCATIONS(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)  
     VALUES (:new.DEPARTMENT_LOC_STREET_ADDRESS, :new.DEPARTMENT_LOCATI_POSTAL_CODE, :new.DEPARTMENT_LOCATION_CITY, :new.DEPARTMENT_LOC_STATE_PROVINCE, v_DEPARTMENT_LOCATIO_COUNTRY_ID)  
     RETURNING (LOCATION_ID) INTO v_DEPARTMENT_LOCATION_ID;  
    end;  
   end if;  
   if :new.DEPARTMENT_DEPARTMENT_NAME IS NOT NULL  
   and v_DEPARTMENT_LOCATION_ID IS NOT NULL then   
    begin  
     SELECT D.DEPARTMENT_ID INTO v_row.DEPARTMENT_ID  
     FROM OEHR_DEPARTMENTS D   
     WHERE D.DEPARTMENT_NAME = :new.DEPARTMENT_DEPARTMENT_NAME  
     AND D.LOCATION_ID = v_DEPARTMENT_LOCATION_ID;  
    exception when NO_DATA_FOUND then  
     INSERT INTO OEHR_DEPARTMENTS(DEPARTMENT_NAME, LOCATION_ID)  
     VALUES (:new.DEPARTMENT_DEPARTMENT_NAME, v_DEPARTMENT_LOCATION_ID)  
     RETURNING (DEPARTMENT_ID) INTO v_row.DEPARTMENT_ID;  
    end;  
   end if;  
   
   if INSERTING then   
     INSERT INTO OEHR_EMPLOYEES VALUES v_row;  
   else   
     UPDATE OEHR_EMPLOYEES SET   
       FIRST_NAME = v_row.FIRST_NAME,  
       LAST_NAME = v_row.LAST_NAME,  
       EMAIL = v_row.EMAIL,  
       PHONE_NUMBER = v_row.PHONE_NUMBER,  
       HIRE_DATE = v_row.HIRE_DATE,  
       JOB_ID = v_row.JOB_ID,  
       SALARY = v_row.SALARY,  
       COMMISSION_PCT = v_row.COMMISSION_PCT,  
       MANAGER_ID = v_row.MANAGER_ID,  
       DEPARTMENT_ID = v_row.DEPARTMENT_ID  
     WHERE EMPLOYEE_ID = :new.LINK_ID$;  
   end if;  
 END VOEHR_EMPLOYEES_IMP_TR;  
 /  

In the case that you want to use this kind of named views in your application, you can press the button 'Generate Updatable Views' to install a set of views with the name V<Table_Name>_IMP as a logical projection layer of your tables. In the next step, you can click the 'Export UI Defaults' button to export detailed properties for the GUI field for new forms on those tables or views. You then have to import the downloaded file hr_data_uidefaults.sql in your workspace. The Demo App EMPS demonstrates how the view Voehr_Employees_Imp can be used in a Facetted Search, edited in an Interactive Grid, Interactive Report, and Data Entry Form. When you use this view in a data loading process, you can not only load data into the table Oehr_Employees, but also load new entries into Oehr_Regions, Oehr_Locations, and Oehr_Departments. Not a single line of SQL code had to be written in this demo app.

Add Calculations: Add a calculated column Netto_Price and define summands to produce totals (Order Items - ... Netto Price, and Employee - ... Salary) in grouped reports of referenced tables.

Add Audit columns to tables with a name that starts with OEHR. The columns Created_At, Created_By,  Last_Modified_At, and Last_Modified_By are added to the tables. The values are managed by default on null function calls in the column definition and with BEFORE UPDATE triggers.

The DDL statements that were performed to alter your schema are stored in the table app_protocol and can be exported with this app.

I hope you find some inspiration and a use case with this app. I welcome contributions and other feedback on github.com like stars and issues. I have got interested in implementing a practical kind of updatable views that were explained by E.F.Codd in the book 'The Relational Model for Database Management Version 2', Page 299, Chapter 17.3 View-updatability Algorithms VU-1 and VU-2. 

The Relational Model for Database Management Version 2

When a database application has fast access to the data dictionary constraint definitions, it can provide much more automatic functionality than just expanding a table name to a report or form on the raw data structures.

Regards

Dirk Strack

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);

});

Schema & Data Browser – Part 3

A new release of the Schema & Data Browser app has been published on GitHub.com 

https://github.com/dstrack/Schema_Data_Browser 

here is are links to the demo with an apex account
https://yboieokpjit8ejk-strackdev02.adb.eu-frankfurt-1.oraclecloudapps.com/ords/f?p=2000:101
https://apex.oracle.com/pls/apex/f?p=48950:101

Schema & Data Browser V 1.8.77 Release Note

New features 

on page Manage Schema

1. Export UI Defaults 

Enables you to download a UI Defaults definition PL/SQL script file for tables of the current schema with additional attributes like optimal width, max length, format masks, and LOVs for references, Yes/No fields, and check constraints with IN-Lists.

You can import this file with APEX to improve the default appearance of new forms on your tables.

Example: https://github.com/dstrack/Schema_Data_Browser/blob/master/Examples/hr_uidefaults.sql

2. Export Updatable Views - download a PL/SQL Script with Create View statements for updatable views.

The data browser displays this kind of view as report variant 'Import View'.

In the Dialog Window 'Show query' you can choose 'Create View' to see the Script for the currently selected table. The setting for 'Import Controls' (Compare Case Insensitive, Search keys unique, Search keys unique) and UNIQUE CONSTRAINTS for the display values of your LOVs will control the generation of these views.

The views will have INSTEAD OF INSERT OR UPDATE OR DELETE triggers that will manage the processing of DML statements on those views.

You can import and execute this file with the APEX "SQL Workshop / Scripts" page to install these views. You may use this view as a data source in Interactive Grid.

There you will be able to search in the display values based on LOV for references, 

and you will be able to edit data in each column.

Example: https://github.com/dstrack/Schema_Data_Browser/blob/master/Examples/hr_imp_views.sql

Bugfix: The generated query SQL code for rendering the form will call APEX_ESCAPE.HTML for character columns to prevent Cross-Site Scripting.

The bug was discovered by Ved Prabhu from Bangalore, India, and then reported by Joel R. Kallman from Oracle.

UI-Appearance Option: Added a global control to show or hide Breadcrumbs below the page title. The control can be set by the online user via the Navigation Bar menu.

Data Browser: The style of the View Mode Navigation List has been changed to Navigation Tabs to improve the highlighting of the current View Mode.

The entries for 'Entity Relationship Diagram' and 'Springy Diagram' have been removed from the View Mode Navigation List and have been added to the 'Actions' menu.

Improved descriptions and help text and improved german translation of the App UI.

Features of the application: Data Browser  

The key functionality of the SQL query and PL/SQL generators

1. Produce a description of any record 

Candidate keys are detected and serve as the unique display column set to describe any table record in LOVs, References, Record Labels, Link Labels.

2. Identify any record

Primary keys are detected and serve as internal identifiers that are hidden from the users. Functions to produce new identities(from sequence or sys_guid), references (columns Link_ID$, Row_Selector$),  and access methods (by column name or ROWID) are automatically applied.(Weak constraint definitions like unique index definition are also detected and considered)

2. Produce formatted descriptions

Any Number and Date field will be formatted with predefined or calculated format masks.

3. Produce recursive description

Any foreign key in the display column set is replaced by the reference target description.

4. Perform reverse key lookups

 This functionality is used to process imported data without any programming.

Any data that is entered or loaded in unique description fields from joined tables can be reduced to new or existing primary key values. This is possible when the tables have primary keys and candidate keys defined and the referenced rows exist or can be inserted because no constraint is violated.

Rows can be inserted when the table definition has default value or functions for all required columns that are not included in the view or that are empty in the import dataset. Required columns must have default values or can be initialized from the context (container references).

5. Produce a description of any relation 

For any foreign key constraint, a description of the reference source and target is composed to produce Report Labels, LOV queries, master-details reports. The relationship of tables is used to produce a navigation tree there tables are shows as childs of parent tables when they have foreign key references to that parent table.

6. Produce a description of any reference 

Any foreign key column can be replaced by a description of the reference target. The replacement can produce multiple formatted columns. Unique columns headers are produced by combining source column names and target table names.

7. Produce composed description 

The columns of a tables display column set can be concatenated with predefined delimiters to produce a composed description. The composed description is used to produce the display values required in 'Select List' und 'Popup List of Values' form items and in for link labels.

8. Produce human-readable and updatable views on any sufficiently defined table.

All available information from the data dictionary is aggregated in snapshot views for fast access. Functional programming is applied to enable spontaneous query and code generation. A query generator is invoked to produce new functions that render reports and forms. Code generators are invoked to produce new functions that perform controlled and correct data manipulations.

9. The system catalog is the single source for table and column names and their attributes.

With name pattern matching, functional properties are applied to matching columns. Any changes in the system catalog are reflected immediately in the rendered reports and forms. Spontaneous changes to the running applications are supported. Projects with an agile workflow and DevOp projects are possible. But projects with more conservative demands are also supported. It is possible to model forms and reports and then extract and copy the generated SQL Queries and PL/SQL Code. You can use it as UI Defaults, in static Apex pages, in the Data Reporter App, and PL/SQL libraries. The report queries can be extracted as updatable views that can be utilized in Apex Interactive Grids.

Forms 

Dynamically produced tabular and single record forms in Readonly or Edit Mode for any table. 

Changes in the schema definition are reflected immediately in the rendered forms.

Produce Field labels

Detect and removes common column prefixes and postfixes.

Compose unique field names for columns of joined tables.

Column Infos 

Help Text with column comments, format mask, description of constraints, functional meaning, statistics: Min, Max, Count, Density.

Developers have options to alter the comments and constraints (range of permitted values).

Produce a diverse variety of input fields 

Including formatted numbers, date, date-time, timestamp, text, multiline textbox, office documents, images, boolean (Yes/No switch), 

Popup links and modal dialogs for 'Rich Text Editor', PDF preview, Office document preview, Image Preview, 'Select Lists' and 'Popup List of Values' (LOVs). LOVs are produced for foreign key columns and check constraints that define value lists. (Example constraints: col_x references table_y; check (col_x in (1, 2, 3)); check (col_y = 1 or col_y = 3) ).

Boolean fields are detected by analysing constraints, default, datatype, length, nullable, cardinality, contradictions and column name pattern.

Functional fields for row selection, link to single record views, ordering rows in a set, hashed passwords, encrypted passwords, file metadata, audit information.

Logical enclosed references are suppressed in the default display column sets. This is done to avoid the display of redundant information. These hidden columns are automatically initialized in key lookup and DML operations.

Automatic validations

When an input field is changed, validations are performed immediately. Any simple check, size limit, reference, or unique constraint defined in the data dictionary is evaluated. On submit of the form any complex check constraint and required constraint defined in the data dictionary is evaluated.

Navigation links

Produce links to parent, childrens, and sister records.

Report Variants 

• In 'Form View' reports all normal columns of the selected table are displayed. Serial primary key column and audit columns are hidden by default. In read-only mode, foreign key columns display the labels of the referenced rows. In edit mode, foreign key columns are LOV popup or select list fields with automatically composed labels. When available, additional functional columns can be displayed via the actions/columns menu option. In 'Form View', 'Navigation Counter' and 'Navigation Links' reports the height and width of large text blocks and images is constrained to keep the reports readable. When you click on images or document icon, a document preview modal dialog window is opened. In edit mode you can click on text cells with a pen icon to edit large text fields in an HTML-Editor modal dialog window. Column Limits:View up to 100 columns, Edit up to 60 columns.) 

• 'Navigation Counter' reports by default only display the natural key columns (labels), counters of references and totals of the active table. For each foreign key that references the current table, a column with count of references to a row is displayed. Depending on the setting of the 'Nested View' option, a click on a counter opens the references in a nested table view or on a separate report page. When available, additional functional columns can be displayed via the Actions/Columns menu option. When a number column of a child table is marked a 'Summand' via the Action/Alter Column Rules Dialog, totals for that columns will be displayed.

• 'Navigation Links' reports by default only display the natural key columns (labels) and totals for the current table. For each foreign key that references the current table, a column with a list of Links to child rows is displayed. A click on a Link opens the reference in a dialog page. When available, additional functional columns can be displayed via the actions/columns menu option. When a number column of a child table is marked a 'Summand' via the Action/Alter Column Rules Dialog, totals for that columns will be displayed.

•  'Raw Record' reports by default display all data columns of the selected table. Serial primary key column are hidden by default. Foreign key columns display the internal numeric values as stored in the database. 

• In 'Import View' reports all normal data columns of the selected table are displayed. Serial primary key column and audit columns are hidden by default. In read-only form, foreign key columns display the labels of the referenced rows, while in edit forms, foreign key columns are LOV popup or select list fields of individual label columns. In case of composite labels each component column is displayed as a separate column. The default report column list is suitable for data export and imports. The data import process can validate a convert CSV data files with columns that match the report columns layout. Then the lookup of foreign key references is automatically performed. Depending on the import setting, new lookup values for foreign keys are inserted in the referenced tables or error messages are produced when that is not permitted. When the natural key values (or chosen display columns values) of the imported data matches with existing rows in the current table, then the data is merged for that rows and the data is inserted in new rows when that keys don't match. 

• In 'Export View' reports by default all normal data columns of the current and the parent table are displayed by default. The serial primary key column and audit columns are hidden by default. Foreign key columns display the natural keys (labels) of the referenced rows. In the case of composite natural keys (labels) each component column is displayed as a separate column. In the modal dialog for Action/Columns Join Options are displayed. You can set the join options to systematically add columns of the referenced tables for each foreign key reference of the current table. After setting the 'All columns' Join option for a table, you can include further columns of the referenced table recursively up to the root of the keys. Independent of your settings, the report will produce one output row for each row in the current report table.

• In 'History View' reports old versions of the table data can be displayed. The normal data columns and audit information is displayed by default. For foreign key columns, the composed label values are displayed. When the historic data differs from the current row values, that values are highlighted with different table cell background colors. Row changes have to be recorded in a change log to support the History Views. You have to enable the option 'Enable Change Log Support' in the Settings/Edit History Settings dialog page to enable this recording. 

• In 'Calendar' views data from the current table is displayed in Calendar regions. The natural keys (labels) are displayed in calendar sheets when a calendar start-date and optional end-date column has been registered for the current table. 

• In 'Tree View' views data from the current table is displayed in a Tree.The natural keys (labels) are displayed as tree nodes when a parent reference column has been registered for the current table. A parent reference is a foreign key reference to the same table with empty parent references for the root nodes. 

Tabular form 

You can add multiple new rows with default values. At least one empty row will be displayed. Show protected rows as display-only in edit forms. The program supports ordering, text search, pagination

 

Single record view

The form can be rendered with 1,2 or 3 column layout.

Publish Reports

A connection to the Data Reporter App enables the publication of reports to other users. Application users and whitelisted tables are automatically synchronized into the Data Reporter App. Any report view can be exported as a data source for the Data Reporter App.

Export formats - Form, HTML, CSV, Native

Data can be rendered in 

• HTML read-only - with formatted data, scroll boxes for large text, and highlighted search matches.
• HTML input - with input fields for data entry
• Native - unformatted for Interactive Reports, 
• CSV - Unicode text files with comma-separated values for downloads.

Export / Import Cycle

1. Data from the Import Views can be exported in CSV files to the user's desktop. The columns template of the import view specifies a logical column set that contains all data columns and no serial primary or foreign keys and only display key columns of the referenced tables. The columns template can be downloaded and used as a specification for successful data transfer. On the desktop, the users can alter rows, or append new rows with other applications like MS Excel on Mac Numbers and produce a CSV file.
2. The user can import CSV files into an APEX collection. After the Import of the file, the data will be automatically validated and a first key lookup is performed. The data is loaded into a temporary collection and is displayed in an edit form. Validation messages are shown at grid cells with bad data. The user can manually fix the errors by entering new data. 
3. After the inspection of the imported file, the user can perform the import by pressing the 'process import' button. The program will perform the processing to update and insert rows in the database.

Import control parameter provides some flexibility to influence the key lookup processing:

• Compare Case Insensitive: Allow the keys lookup process to match text column values case insensitive.
• Search keys unique: Require unique constraints for the imported lookup column sets.
• Insert new foreign keys: Allow the process to insert new rows in lookup tables. When empty key columns are looked up, the program matches rows with empty columns in the lookup table. When other columns but the key columns are imported, then the table schema should be in the third normal form.  There should be no columns that are dependent on other columns but the primary or candidate keys.

Simple data manipulations 

• Perform validations, lookups, and DML processing for any table. 
• Sequences related to a table are detected and then used to produce new identities.
• Change checks are produced to process only relevant rows and to verify that updated rows have not been changed by other users.
• The processing of the insert, update, or delete operations produces confirmation messages.
• Success messages inform you about the number of affected rows.
• Error messages will report any validation or processing error.
• Validation errors will appear near the corresponding fields in the form.

Advanced data manipulations 

• Function to duplicate selected rows.
• In the context of a container reference, there are functions to copy, move, and merge sets of data.

Convenience 

• The application stores and remembers your settings for the last accessed table, view mode, column layout, and order between sessions. 
• Sessions can last up to 10 hours before a new login is required.
• When buttons and menus are invoked by the user, ca. 300 dynamic actions perform the processing and (partial screen updates). This enables fast processing and improved user experience.
• Field validation is performed when you leave a field.
• Required-checks are postponed until you submit the form, to avoid needless complaints.
• Light and Dark Display Themes. 
• German and English versions of the interface. 

Security 

• Custom Authorisation - Only registered users can access the Application.
• all generated links in the application are protected with APEX checksums.
• Manage User Accounts with access levels, hashed passwords, e-mail validation, request a new password, invite users via e-mail.
• Control visibility of tables with table name pattern for inclusion, exclusion, admin only.
• Control write access with table name pattern for edit, read-only.
• Control (default) visibility of columns with column name pattern for display labels (always), data deduction, hidden, ignored.
• Deny write access to rows with column name pattern for read-only access.

Developer tools 

• Query source code: Use a smart switchboard, to display the SQL query that renders the current report. The options are: 
• Data Source (Table, Memory, Collection)
• Data Format (Form, HTML, Native, CSV)
• Data Operations (Select, Insert/Update, Update, Delete, Duplicate, Move, Copy, Merge, Import View Definition, Download Files)
• Columns Limit, Show at least one row, Compact Queries, Parent Key Visible
• View and copy the SQL source queries of the current view. 
• Queries are rendered according to your currently displayed report 
• relation (master and detail table) 
• selected columns
• column ordering and control break
• the current Report View Mode (Form View, Import View, ...)
• the chosen Options and Settings for functional columns and other setting rules.
• View and copy the PL/SQL code for validations, key lookups, and DML processing. Learn, all necessary step to:
• detect changed rows
• validate all size limits and imposed constraints and produce human-readable error messages
• lookup foreign keys for the given situation.
• perform complicated data manipulations
• UI Defaults 

Optimize the column descriptions and export them as UI Defaults. UI Defaults will boost productivity when new forms and reports are produced with APEX. A complete set of column attributes is prepared. included attributes are: column label, datatype, input field length, max field length, required/mandatory,  format masks, static default value, static LOV definitions, dynamic LOV definitions, help text (with column comments, format mask, check constraints).

• Diagrams
• Entity Relationship Diagrams
• Database Object Dependencies
• Dynamic Actions of Apex Pages

The diagrams are rendered using a dynamic springy layout method that is self-optimized and interactive. The user, can zoom, resize, drag and pane to inspect complex relationships and find missing links.

• DDL tools
  The application derives all attributes of the tables and columns from the schema definitions and the configuration settings of ca. 50 name patterns. For most patterns, common default values exist. The tables that are designed with this application will match the pattern defaults. All data definition statements are written into the app_protocol table.

• Definition of new tables
  Choose from templates to add functional columns:

• Parent Tables
• Referenced tables (Container, Optional Container, Required, Optional)
• Natural Unique key description 
• File (Text Editor, HTML-Editor, File, Folder, None)
• Ordering, Active, Locked, Audit Infos.

• Add columns
  Define Column Name, datatype, Char length, Default, Required, Unique (Composed, Simple, Non)

• Alter column comments
  
  
• Alter column rules (constraints)
  Change the setting for Required, Unique, Values List / Range, Default Value / Default Reference.

• Drop tables.
  Remove table definition and data.
• Drop Column
  Remove a column from a table.

Show or hide Breadcrumbs in APEX Apps

You may want to add a global control to show or hide the breadcrumbs below the page title. This blog post explains how you can do that. Since users can always return to the home page of your app, by clicking on the Application title, it is OK to hide the breadcrumbs in some situations.

A Region can be set to be Customizable, but that affects only the current page.    

You may want to give the users control to hide and show the Breadcrumbs when they don't need it, with a global switch. The control can be set by the online user via the Navigation Bar menu with the following method.

Add a Application Item

Name : APP_DISPLAY_BREADCRUMBS 

Add an Application Computation

Computation Item : APP_DISPLAY_BREADCRUMBS

Computation Point : On New Instance 

Computation Type : PL/SQL Expression 

Computation  : NVL(APEX_UTIL.GET_PREFERENCE(:OWNER || ':DISPLAY_BREADCRUMBS'), 'YES')

Add an Application Process

Name : Set Display Breadcrumbs

Sequence : 100 

Process Point : On Load: Before Header 

PL/SQL Code:

:APP_DISPLAY_BREADCRUMBS := case when :REQUEST = 'DISPLAY_BREADCRUMBS_ON' then 'YES' else 'NO' end;

APEX_UTIL.SET_PREFERENCE(:OWNER || ':DISPLAY_BREADCRUMBS', :APP_DISPLAY_BREADCRUMBS);

Condition Type : PL/SQL Expression 

Expression 1 : 

:REQUEST IN ('DISPLAY_BREADCRUMBS_ON','DISPLAY_BREADCRUMBS_OFF')

Add Navigation Bar entries 

Edit list 'Navigation Bar' and add 2 entries. 

first entry

Parent List Entry &APP_LOGIN_NAME 

Sequence : 100 

Image/Class : fa-toggle-on 

List Entry Label : Hide Breadcrumbs

Target type : Page in this Application 

Page : &APP_PAGE_ID.

Request : DISPLAY_BREADCRUMBS_OFF

Condition type : Value of Item / Column in Expression 1 = Expression 2 

Expression 1 : APP_DISPLAY_BREADCRUMBS 

Expression 2 : YES 

second entry

Parent List Entry &APP_LOGIN_NAME 

Sequence : 110 

Image/Class : fa-toggle-off 

List Entry Label : Display Breadcrumbs

Target type : Page in this Application 

Page : &APP_PAGE_ID.

Request : DISPLAY_BREADCRUMBS_ON

Condition type : Value of Item / Column in Expression 1 = Expression 2 

Expression 1 : APP_DISPLAY_BREADCRUMBS 

Expression 2 : NO 

Load each page that has a Breadcrumbs region 

Click on the Region named Breadcrumbs

Change the Server-side Condition 

Type : Item = Value 

Item : APP_DISPLAY_BREADCRUMBS

Value : YES 

or when you also whant to hide the region in printer friendly mode

Type : PL/SQL Expression 

PL/SQL Expression :

:PRINTER_FRIENDLY = 'NO' AND :APP_DISPLAY_BREADCRUMBS = 'YES'

The method is demonstrated in a demo app. After login click on the username menu entry in the top menu.

https://apex.oracle.com/pls/apex/f?p=48950:101

Highlight current card

In the APEX universal theme, the is_current state is not visible in Cards regions.
Solution: Highlight active card in list regions with template Cards with some CSS.

 li.t-Cards-item.is-active div.t-Card {
    box-shadow: 0px 0px 6px 1px Yellowgreen;
}

Bug with Warn on Unsaved Changes

APEX version >= 5 supports a 'Warn on Unsaved Changes' functionally when the setting on page level is 'Yes'.
Problem: For item types 'Popup LOV' and 'Radio Group' the setting 'Ignore' is not applied and you get the warning message.
Solution: Use the following JS code in 'Execute when Page Loads' to fix this problem.

// for POPUP_LOV
$('input[id$=_HIDDENVALUE]').addClass('js-ignoreChange');

// for RADIO_GROUP
$('input[type=radio]').addClass('js-ignoreChange');

Cloud Visitors – APEX App

Application to monitor visitors of remote APEX sites

I have published several demo applications for open-source oracle APEX plugins on apex.oracle.com and other hosting locations. My joy and payback are, to watch the web traffic on the demo site. To do that, go to the Administration\Monitor Activity\Login Attempts report, there you can list visitors with there IP address. I then used a web site like iplocation.net to find out from where in the world the visitors came. I feel a bit connected to the world out there and I believe that it was worth sharing with the community.

I found the lessons on apex.oracle.com about REST data sources and SQL REST functions.
REST Enabling Database Objects 
Defining Web Sources

Then I took a chance to automate the reporting and location lookup for all my sites in one application that I run in my local development environment.
You can download this application from github.com
https://github.com/dstrack/Cloud_Visitors

You can see a demo of this app here:
https://apex.oracle.com/pls/apex/f?p=143496:LOGIN_DESKTOP

The PL/SQL script file  cloud_visitors_utl.sql creates the view CLOUD_VISITORS_V, the tables CLOUD_VISITORS and CLOUD_VISITORS_IP_BLACK_LIST and the package CLOUD_VISITORS_UTL.

At the demo sites:
Execute the script file cloud_visitors_utl.sql
The packages procedure cloud_visitors_utl.Define_RESTful_Service will be executed to install the REST endpoint.
In the view CLOUD_VISITORS_V, the visitor activity is selected from APEX_WORKSPACE_ACTIVITY_LOG, it is then grouped by session login_date, ip_address, application, and page. The performed requests and time spend is aggregated.

In your localhost:
Install the application in your local APEX development environment. Then go the Shared Components\Web Source Modules to edit the modules. The module IP_Geolocation is used to lookup the geolocations of your visitor's IP addresses and should not be removed. Remove the example modules and add modules for your demo sites.
The app enables you to choose the name of a web source module for a visitor's report.

Launch the app and enter your apex credentials.
The page 'Cloud Visitors'
Here you can display recent visitor's activities in an interactive Report from a local table.
After choosing a web source, you can control the scheduler job for the web source.
Click the 'Update now' button to load the recent information from the data source into the local table could_visitors.
Click the 'Launch refresh job' button to enable the frequent refresh of the report data.
A scheduler job will load the recent information with an SQL REST function from the data source joins it with the IP geolocation data and merges the result into the table cloud_visitors. Your own public IP address is added to the table cloud_visitors_ip_black_list and will be excluded from the reports.

Now you can choose a web source to be displayed and you don't have to wait for the display of the result. The data is updated every 4 hours.

And another problem is solved: The regular call of a web service at xyz.oraclecloudapps.com will also prevent the shutdown of an always free ATP site caused by developer inactivity!

The page 'Visitors - Web Source' shows an Interactive Report from location 'Web Source'. Only one web source (that you have to choose in the program editor) can be displayed and you have to wait up to 30 seconds for the result.

The page 'Visitors - SQL REST function' shows an Interactive Report from an SQL REST function. You can choose a web source to be displayed but you have to wait up to 30 seconds for the result.

The page 'IP Location - SQL REST function' allows you to Lookup the geo-location for an IP-Adress. Your own current public IP address and an IP Black List is shows. Here you can add IP addresses of robots that are excluded from the Cloud Visitors reports.

You may have to increase the Limit of 1000 web service requests that are permitted per day for your workspace, to avoid errors in the web service calls. Log in instance administration and go to page Manage Instance \ Security. In the region 'Workspace Isolation' you can change the 'Maximum Web Service Requests' to a higher value.

Package IR_Zip_Download

# APEX-IR-Zip-Download
pl/sql code for downloading of large APEX Interactive Reports as ZIP files.
In case you have to download very large reports from an APEX Instance that is running behind a web proxy server or in cases where you have to download reports with more rows than the technical limit allows you to use, you can use the method to successfully download these reports. The Sample App can be installed in an APEX 5 Workspace.


Usage:
1. Install the package IR_Zip_Download in your application schema.
2. add a Button to your Report region.
3. set 'Reload on Submit' to 'Allways' in an APEX 18 and higher.
4. add a 'Branch' of type 'PL/SQL Procedure' or 'Process' of type 'PL/SQL Code' with the following code:

IR_Zip_Download.Download_Zip(
    p_Region_Name => 'Sample Report',  -- Enter your IR region title here
    p_Application_ID => :APP_ID,
    p_App_Page_ID => :APP_PAGE_ID
);

The procedure performs the following steps:
1. extract the query with APEX_IR.GET_REPORT
2. bind the variables
3. Open cursor for query
4. convert to csv.
5. Convert to zip with APEX_ZIP.ADD_FIle
6. start the download to the browser

the code and a sample app can be downloaded here:

https://github.com/dstrack/APEX-IR-Zip-Download

The demo app is running here:

https://apex.oracle.com/pls/apex/f?p=39653:LOGIN_DESKTOP

Package Numbers_Utl for simple, flexible and functional number conversions and validation.

In an international APEX app, the NLS settings influence the behavior of TO_NUMBER and TO_CHAR calls and can cause conversion errors.
When the app passes floating-point numbers via (hidden) APEX Items to and from javascript functions or to web-services, the decimal and grouping characters are constants!
These constants have to be passed to the function to_char and to_number as the second parameter (nlsparam).
That can be problematic,
1. because the precision and scale have to be fixed when formats with the grouping characters are needed. Since there is no combination of the FM9 mask with the G, I had to find a more flexible solution for number conversions.
2. javascript floating-point numbers can be surprisingly long before or after the decimal point.

I found a simple method that forms a fitting format mask on the fly.
The TRANSLATE function call maps the digits and signs to 9 and removes blank and currency characters. The REGEXP_REPLACE function call detects the exponent part and prepares the format string. I explored and expanded the method into the package numbers_utl.

https://livesql.oracle.com/apex/livesql/file/content_JTVYCKDJQF8MA8ZB5MM48XVC5.html

Link to the source file:

https://github.com/dstrack/Schema_Data_Browser/blob/master/Sources/data_browser1/numbers_utl.sql

Schema & Data Browser – Part 2

Schema & Data Browser – Part 2

A new release of the Schema & Data Browser app has been published on GitHub.com 

https://github.com/dstrack/Schema_Data_Browser 

here is are links to the demo with an apex account
https://yboieokpjit8ejk-strackdev02.adb.eu-frankfurt-1.oraclecloudapps.com/ords/f?p=2000:101
https://apex.oracle.com/pls/apex/f?p=48950:101

A large number of visitors to the springy diagram demo and the peasant feedback and constructive critics encouraged me to put more effort into this project.

`Cyberspace. A consensual hallucination experienced daily by billions of legitimate operators, in every nation, by children being taught mathematical concepts... A graphic representation of data abstracted from the banks of every computer in the human system. Unthinkable complexity. Lines of light ranged in the nonspace of the mind, clusters, and constellations of data. Like city lights, receding...' –William Gibson; Neuromancer (Chapter 3); 1984

After two decades of collecting experiences in financial and process controlling apps in the Oracle domain, I like to break out of the box. I want to produce some art that visualizes what's in my mind. I like the idea behind the springy.js diagram renderer because, with just 3 natural forces (Stiffness, Repulsion, Gravitation), the automatic layout of complex networks can be calculated with pleasant results.

Gravitation – pulls all objects to the center and is the cause that the silhouette of the diagram is an oval shape.

In a social context – let's stay together and build a group.

Stiffness – defines the force that pulls two nodes together when they are connected via an edge.

You could also, name that force attraction or empathy.

Repulsion – defines the force that is repelling other nodes nearby, like the electric negative charge.

You could also, name that force Respect – don't step on me.

Directed Graphs

• are used to model all kinds of relationships in social graphs.
• Dependencies in database object constructs.
• Foreign key relationships and dimensions in business models.
• Object enclosure / encapsulation via parent/child relationships.
• Module separation – recognize independent groups of interconnected objects.
• Signal paths of events on a web page – what happens, when you click that button.
• Cascading or Domino effects – what will break, if you change that object.

The diagram renderer

• Besides the diagrams a control panel for all diagram parameters. The adjustments are performed with immediate visual feedback.
• Zoom: lets you fluently zoom the canvas viewport in a factor range from 1 to 12 X.
• Font size: lets you adjust the size of the nodes relative to the canvas and edges. 
• Initial font size is calculated for new diagrams based on the square root of the count of nodes.
• You can adjust the font size, to either put the focus on the nodes or on the edges. With a very small font size, the automatic layout is performed at the highest frame rate.
• min. Energy: defines a threshold level where the program stops the calculation of updates for node positions.
• Set 'min. Energy' to 0 or greater, when you want to stop the processing-intensive calculations.
• max. Speed: defines the maximal speed of the moving nodes during automatic layout.
• Stiffness: defines the force that pulls two nodes together when they are connected via an edge – Rubber band effect.
• Repulsion: defines the force that is repelling other nodes nearby.
• Damping: defines a factor that is reducing the applied forces, to slow down the movements.
• The performance of the algorithms to calculate the forces are improved by a factor of 10 so that you can now render diagram of up to 2000 nodes and edges fluently.
• In order to avoid shaking nodes when the stiffness is cranked up, a counterweight algorithm has been added. The algorithm automatically adjusts the weight of the node depending on the degree of connections.

The diagram editor

• On the page 'Springy Diagram' you can alter individual node properties (Shape, Text, Color, Active) with immediate visual feedback.
• Node colors can be chosen from a popup dialog window of HTML color names.
• There are now buttons to control the zoom factor and font size when no mouse wheel is available.
• There is now an auto-focus button to give you an optimal view of a selected node.
• Resizing of the diagram canvas and multiple canvases are now supported. 
• The legends of the database diagrams are now active springy diagrams.
• Many graphical and processing errors have been fixed.
• Support for touchpad: select node, double tab, move node, move the canvas, zoom canvas, zoom font size
• A smart selection method enables you, to logically select related nodes via their directed graph's edges using one of the methods: 

• Selected highlights just the clicked node and its edges.
• Downstream highlights all nodes that a connected via edges in arrow direction; useful for following a signal in an APEX Dynamic Actions diagram and to select child nodes in a dependencies diagram.
• Upstream highlights all nodes that a connected via edges in the reverse arrow direction; useful to select parent nodes in a dependencies diagram.
• Connected highlights all nodes that a connected via edges in both arrows directions; useful to select independent modules in the pool of objects in a database schema. You can crank up the stiffness parameter to make the module separation more obvious. 

• Pin Button – a flip state button

• Active: sets a heavy weight in touched nodes, with the effect that the nodes stick where they are. (weight=10000)

• Inactive: sets a light weight in touched nodes, with the effect that the nodes float to their optimal position. (weight=10)

• The Actions Menu has now function to
• Save as:, for schema diagrams this action enables you the save the current diagram as a named springy diagram for later use.
• Exempt Selected: hide all nodes that are not selected. (active=N)
• Hide Selected: hide all nodes that are selected. (active=N)
• Show All: sets all nodes to visible (active=Y)
• Freeze / Lock All: set a heavy weight in all nodes, with the effect that the nodes stick where they are. (weight=10000)
• Melt / Unlock All: sets a light weight in all nodes, with the effect that the nodes float to their optimal position. (weight=10)
• Edit Diagram properties: lets you edit the numerical diagram parameter and lets you delete a diagram.

Data Browser – Import View

used to import Database Object Dependencies and other diagrams as CSV files.

• the software package includes the file Object_Dependencies_Export.sql Install the views on an oracle instance via the SQL developer application and then execute the following statements:
• select * from APP_OBJECT_DIAGRAM_EDGES_V;
• select * from APP_OBJECT_DIAGRAM_NODES_V;
• Save the output of the query results as two CSV files on your desktop.
• Navigate in this app to the data browser (page 30),
• Click on the table name 'Diagram Nodes' or 'Diagram Edges' below the table 'Springy Diagrams',
• Click on the Import View tab in the report-modes navigation bar and then begin the import by clicking on the Import button.

• The import processing has been improved. When the importer displays data validation errors, You can now rearrange the columns in the proper order. That will trigger a re-validation and you can continue the import of nodes and edges then no validation errors when found.
• A Navigation bar with view-modes simplify the Navigation to the Springy Diagram – editor page.

Entity Relations Diagram

The kind of foreign key relation is distinguished  by edge colors and labels.

• The Actions Menu has now function to
• Edit Table Data (link to the Data Browser)
• Alter Schema Object (Add a table, column, reference; Alter Rules & Constraints, Comments)
• Edit Schema Object (in SQL Workshop)
• A double click on an object node opens the data browser for that table.

Object Dependencies Diagram

• The object-types-checklist contains separate checkmarks for Key Constraint, Ref Constraint, Check Constraint, Not Null Constraint
• A single click or touch on an object node updates the object info side panel and popup LOV fields for the current object type and name.
• A link to display the SQL Text of the selected object is displayed on the right side panel.
• A click on that link will open a popup dialog to display the SQL text. (Be patient, because that can take a while).
• when you want to find an individual node by type and name, you can use the LOV fields to enter a search term and choose a name from the list.
• After choosing a name, the diagram is auto-focused on the selected node with that name.
• A double click on an object node opens the SQL Workshop page with that object when you have access privileges the APEX workspace.
• The legend is now a springy diagram with interactive functionality for zooming the canvas or font size.

Dynamic Actions Diagram

 The diagram renders nodes for all

• Dynamic actions: with nodes for event source, action name, true and false branches, code steps, request name, affected items, regions, buttons.
• Menus: (shown on the diagrams on pages 0 and 1),
• Lists: with nodes for request names and Link target page.
• Processing points: with nodes for request name, page process names, page branch names and target page #no.
• Buttons: with nodes for request names and links to the target page.
• Classic Report links
• Interactive Report links
• Interactive Grids links

Usage

• A single click or touch on an object node updates the DA info side panel and a popup LOV field for the current DA name.
• For selected links or branches, a link to load the diagram of the target page is displayed on the right-side panel.
• A click on that link will save the current diagram and then open the diagram of the link target page.
• To find an individual dynamic action by name, you can use the LOV field to choose a name from the list.
• After choosing a name, the diagram is auto-focused on the selected node with that name.
• A double click on a dynamic action node opens the APEX page designer with that object, when you have access privileges the APEX workspace.
• The legend is now a springy diagram with interactive functionality for zooming the canvas or font size.
• When the downstream selection method is active, nodes for buttons, regions stop the propagation of the signal, because they are passive objects.

Data Browser

• improved page load performance
• improved rendering of nested reports with pagination.
• improved formatting and processing for numbers and boolean fields.
• new functions for import views: copy to the collection, paste from the collection

Home Page

• new functions to: Add a Schema, Remove Schema, Duplicate Schema, Upgrade supporting Objects

Manage Schema

• new wizards to: Add Serial Primary Keys, Add Natural Keys, Set Mandatory Key Columns

Print pages as HTML

• A new top menu icon allows you to produce a printable version of the current page than can produce the whole report without empty pages and store the output as a PDF document.

This app is for Oracle APEX developers, a collection of tools to accelerate your work.
I request you to install the trial version in your oracle cloud instance or
your local oracle virtual box to explore features that can't be shown online.

- the methods to add, duplicate, remove, and switch schemas at runtime.
- the methods to add tables and columns, alter columns and constraints, drop tables at runtime.
- the wizards to improve your unique key definitions.
- the option to update the APEX 'UI defaults' with Heading, LOVs, number & date formatting, field length, max length, item help.
- the usage of the schema diagrams to find objects and code modules and then edit them with a double click in APEX.
- the Show Query Dialog with tons of options and then copy useful code for form rendering, change detection, early validation, key lookup, saving in DB, and lost update detection.
- see the source of page 30 of the APEX app with 94 dynamic actions as an extreme example.
- see the self-test module that automatically tests the data browser.
- see what well-prepared relations can do for you!