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!

Schema & Data Browser Part 1 – An application of springy-forced diagrams for database design.

I have written a playful Oracle Cloud-ready APEX app for my own leisure, that shows you the BIG-picture of a DB schema, the DB object dependencies, and dynamic actions of an APEX app page. It helped me to understand the APEX sample applications and my customer's databases, by giving me an overview and also showing any detailed aspect with simple mouse movements.

Especially in the absence of documentation, this tool can illustrate relationships, modules, and methods. Information that I would otherwise have to procure with great difficulty.
So if you are as lazy and graphically minded as me, this might be something for you.

When you load a diagram the first time, it will take some time for the diagram to be arranged.
You can manually arrange nodes by dragging them. Sometimes you have to untangle modules to separate them. You can then save the picture for later use.
When the mouse is over the background canvas, you can zoom into the graphic with the
mouse scroll wheel and move the canvas with mouse drag and drop. You can zoom the font size of the nodes with the mouse wheel when the mouse hovers over a node. Click on a node, to show detailed Infos. The page 'Springy Diagrams' allows you, to upload your own networks to be rendered.

The diagrams are part of an integrated editor for the schema objects and for datasets.

As you see in this video, you can build your own dropbox for documents in 4 Minutes.


here is a link to the demo with a custom account
https://strack-software.oracleapexservices.com/apex/f?p=2000:1

here is a link to the demo with an apex account
https://apex.oracle.com/pls/apex/f?p=48950:1

an here is a link to an Installer for the app
https://github.com/dstrack/Schema_Data_Browser

The diagrams are based on the work of Dennis Hotson
http://getspringy.com

Regards
Dirk Strack

DML Support for APEX_COLLECTIONS

I frequently use apex_collections in forms and reports to enjoy there performance and to support special cases. Apex_Collections can be created and accessed using SQL Select statements. This is very nice, but until now I had to use the API calls to perform inserts, updates and deletes. In case where I use an Interactive Grid, i had to provide PL/SQL code to support SAVE operations in the Grid.

My first try to wrap apex_collections with a view, that supports an "instead of update" trigger failed with a runtime error message from oracle. "ORA-20987: APEX - ORA-01031: insufficient privileges"

Recently i listend to Steven Feuerstein lesson about pipelined table functions:
Getting Started with PL/SQL Table Functions: Pipelined Table Functions
Then I saw an opportunity to encapsulate the fact that I want to access these Apex_Collections for update. I wrote the package Pipe_Apex_Collections with the function pipe_rows. Finally, using the function as a source in the view definition, i created a view that supports DML operations.
Here is the source code for the view definition : v_apex_collections.sql
as part of this plugin and demo app : strack-software-upload-to-collection-plugin
and the live demonstration of this app: https://apex.oracle.com/pls/apex/f?p=103003:LOGIN_DESKTOP


Regards,
Dirk

Independent Application Schema for APEX Applications

If you have to assign an independent scheme to an APEX application, it would be advantageous if the application does not need to be changed.  If no fixed schema names were used in the application,
a generated schema can be easily assigned to the application.
The following query lists statements for objects of a schema that can be created as synonyms or views in a new schema.
Tables and views are mapped to views with identical names. This allows APEX programs to check the column names and types. The list must still be filtered to the actually required objects to keep the privileges minimal.

this query on catalog views generates the required statements to publish a database schema in a second schema. This is the result of my own research:
--------------------------------------------------------------------------------

 WITH PA AS ( -- Parameters  
     SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') || '_APP' GRANTEE,  
         SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') OWNER  
     FROM DUAL   
 ),  
 PKEY_Q AS (   
     -- Add primary key constraints to views. This will enable to APEX Builder to detect the Primary key for forms and reports  
     SELECT   
         C.TABLE_NAME, C.OWNER TABLE_OWNER,   
         ', CONSTRAINT ' || Dbms_Assert.Enquote_Name(C.CONSTRAINT_NAME) || ' PRIMARY KEY ('  
         || LISTAGG(Dbms_Assert.Enquote_Name(C.COLUMN_NAME), ',') WITHIN GROUP (ORDER BY C.POSITION)   
         || ') RELY DISABLE' PKEY_CONS  
     FROM SYS.USER_CONSTRAINTS B  
     JOIN SYS.USER_CONS_COLUMNS C ON C.TABLE_NAME = B.TABLE_NAME AND C.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND C.OWNER = B.OWNER  
     WHERE B.CONSTRAINT_TYPE = 'P'  
     AND B.OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')  
     AND B.TABLE_NAME NOT LIKE 'DR$%$_' -- skip fulltext index  
     AND B.TABLE_NAME NOT LIKE 'BIN$%' -- this table is in the recyclebin  
     GROUP BY C.TABLE_NAME, C.OWNER, C.CONSTRAINT_NAME  
 ),  
 COLS_Q AS (  
     -- List of Column alias names for views   
     SELECT TABLE_NAME, LISTAGG(Dbms_Assert.Enquote_Name(COLUMN_NAME), ', ') WITHIN GROUP (ORDER BY COLUMN_ID) TAB_COLUMNS  
     FROM SYS.USER_TAB_COLUMNS  
     GROUP BY TABLE_NAME  
 ),  
 STATS_Q AS (  
     -- views  
     -------------------------  
     SELECT 'GRANT ' || PRIVS || ' ON ' || S.OWNER || '.' || S.VIEW_NAME || ' TO ' || PA.GRANTEE GRANT_STAT,  
         'CREATE OR REPLACE VIEW ' || PA.GRANTEE || '.' || S.VIEW_NAME   
         || ' (' || S.TAB_COLUMNS || S.PKEY_CONS || ')'  
         || ' AS SELECT * FROM ' || S.OWNER || '.' || S.VIEW_NAME CREATE_STAT,  
         'REVOKE ' || PRIVS || ' ON ' || S.OWNER || '.' || S.VIEW_NAME || ' FROM ' || PA.GRANTEE REVOKE_STAT,   
         'DROP VIEW ' || PA.GRANTEE || '.' || S.VIEW_NAME DROP_STAT,  
         S.OWNER, S.VIEW_NAME OBJECT_NAME,   
         'VIEW' OBJECT_TYPE,   
         'VIEW' DEST_OBJECT_TYPE,  
         ADMIN_GRANT_STAT  
     FROM (  
         SELECT CASE WHEN DG.FOREIGN_DEPS_CNT > 0   
             THEN NVL(DG.PRIVILEGE, 'READ')  
             ELSE   
                 'SELECT' ||  
                 CASE WHEN TR.PRIVS IS NOT NULL   
                     THEN ', ' || TR.PRIVS  
                     ELSE T.UPDATABLE || T.INSERTABLE || T.DELETABLE  
                 END  
             END PRIVS,  
             NG.ADMIN_GRANT_STAT,  
             T.VIEW_NAME, T.OWNER,   
             P.PKEY_CONS, C.TAB_COLUMNS  
         FROM (  
             SELECT V.VIEW_NAME, T.OWNER,  
                 MAX(CASE WHEN UPDATABLE = 'YES' THEN ', UPDATE' END) UPDATABLE,  
                 MAX(CASE WHEN INSERTABLE = 'YES' THEN ', INSERT' END) INSERTABLE,  
                 MAX(CASE WHEN DELETABLE = 'YES' THEN ', DELETE' END) DELETABLE  
             FROM SYS.USER_UPDATABLE_COLUMNS T  
             JOIN SYS.USER_VIEWS V ON V.VIEW_NAME = T.TABLE_NAME  
             GROUP BY V.VIEW_NAME, T.OWNER  
         ) T LEFT OUTER JOIN ( -- grantable dependent object privileges  
          -- when a view is accessing other foreign schema view, then is view has READ-only access  
           SELECT NAME, COUNT(*) FOREIGN_DEPS_CNT,  
                 LISTAGG(PRIVILEGE, ', ') WITHIN GROUP (ORDER BY PRIVILEGE) PRIVILEGE  
             FROM (  
                 SELECT DISTINCT D.NAME, PRI.PRIVILEGE  
                 FROM SYS.USER_DEPENDENCIES D  
                 JOIN SYS.ALL_TAB_PRIVS PRI   
                     ON PRI.table_Schema = D.REFERENCED_OWNER   
                     AND PRI.table_Name = D.REFERENCED_NAME  
                     AND PRI.grantee IN ('PUBLIC', SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'))  
                 WHERE D.TYPE = 'VIEW'  
                 AND D.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'SYNONYM')  
                 AND D.REFERENCED_OWNER NOT IN ('PUBLIC', SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')) -- dependent on object in foreign schema   
                 AND PRI.GRANTABLE = 'YES'  
             )  
             GROUP BY NAME  
         ) DG ON T.VIEW_NAME = DG.NAME  
         LEFT OUTER JOIN ( -- not grantable dependent object privileges  
             SELECT NAME, COUNT(*) FOREIGN_DEPS_CNT,  
                 LISTAGG(CASE WHEN NOT_GRANTABLE > 0  
                         THEN 'GRANT ' || PRIVILEGE || ' ON ' || REFERENCED_OWNER || '.' || REFERENCED_NAME   
                             || ' TO ' || SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') || ' WITH GRANT OPTION;'  
                         END, chr(10)) WITHIN GROUP (ORDER BY PRIVILEGE) ADMIN_GRANT_STAT  
             FROM (  
                 SELECT D.NAME, D.REFERENCED_OWNER, D.REFERENCED_NAME,  
                     LISTAGG(PRI.PRIVILEGE, ', ') WITHIN GROUP (ORDER BY PRIVILEGE) PRIVILEGE,  
                     COUNT(*) NOT_GRANTABLE  
                 FROM SYS.USER_DEPENDENCIES D  
                 JOIN SYS.ALL_TAB_PRIVS PRI   
                     ON PRI.table_Schema = D.REFERENCED_OWNER   
                     AND PRI.table_Name = D.REFERENCED_NAME  
                     AND PRI.grantee IN ('PUBLIC', SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'))  
                 WHERE D.TYPE = 'VIEW'  
                 AND D.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'SYNONYM')  
                 AND D.REFERENCED_OWNER NOT IN ('PUBLIC', SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')) -- dependent on object in foreign schema   
                 AND PRI.GRANTABLE = 'NO'  
                 GROUP BY D.NAME, D.REFERENCED_OWNER, D.REFERENCED_NAME  
             )  
             GROUP BY NAME  
         ) NG ON T.VIEW_NAME = NG.NAME  
         LEFT OUTER JOIN ( -- updatable views with INSTEAD OF trigger  
             SELECT REGEXP_REPLACE(  
                 LISTAGG(TRIGGERING_EVENT, ', ') WITHIN GROUP (ORDER BY TRIGGERING_EVENT),   
                 '\sOR\s', ', ') PRIVS,  
                 T.TABLE_NAME VIEW_NAME  
             FROM SYS.USER_TRIGGERS T  
             WHERE TRIGGER_TYPE = 'INSTEAD OF'  
             AND BASE_OBJECT_TYPE = 'VIEW'  
             GROUP BY TABLE_NAME  
         ) TR ON T.VIEW_NAME = TR.VIEW_NAME  
         LEFT OUTER JOIN PKEY_Q P ON T.VIEW_NAME = P.TABLE_NAME  
         LEFT OUTER JOIN COLS_Q C ON T.VIEW_NAME = C.TABLE_NAME  
     ) S, PA  
     WHERE PA.GRANTEE IS NOT NULL  
     UNION ALL    
     -- normal tables   
     ----------------  
     SELECT   
         'GRANT ' || PRIVS || ' ON '   
         || PA.OWNER || '.' || TABLE_NAME || ' TO ' || PA.GRANTEE GRANT_STAT,  
         'CREATE OR REPLACE VIEW ' || PA.GRANTEE || '.' || T.TABLE_NAME   
         || ' (' || T.TAB_COLUMNS || T.PKEY_CONS || ')'  
         || ' AS SELECT * FROM ' || PA.OWNER || '.' || T.TABLE_NAME CREATE_STAT,  
         'REVOKE ' || PRIVS || ' ON '   
          || PA.OWNER || '.' || TABLE_NAME || ' FROM ' || PA.GRANTEE REVOKE_STAT,   
         'DROP VIEW ' || PA.GRANTEE || '.' || T.TABLE_NAME DROP_STAT,  
         PA.OWNER, T.TABLE_NAME OBJECT_NAME,   
         'TABLE' OBJECT_TYPE,   
         'VIEW' DEST_OBJECT_TYPE,  
         '' ADMIN_GRANT_STAT  
     FROM (  
         SELECT T.TABLE_NAME,  
             CASE WHEN READ_ONLY = 'NO'  
                 AND NOT EXISTS (  -- when this table is part of materialized view, then only SELECT allowed   
                     SELECT 1  
                     FROM USER_OBJECTS MV  
                     WHERE MV.OBJECT_NAME = T.TABLE_NAME  
                     AND MV.OBJECT_TYPE = 'MATERIALIZED VIEW'  
                 ) THEN 'SELECT, UPDATE, INSERT, DELETE '   
                 ELSE 'SELECT '   
             END PRIVS,  
             P.PKEY_CONS, C.TAB_COLUMNS  
         FROM SYS.USER_TABLES T  
         LEFT OUTER JOIN PKEY_Q P ON T.TABLE_NAME = P.TABLE_NAME  
         LEFT OUTER JOIN COLS_Q C ON T.TABLE_NAME = C.TABLE_NAME  
         WHERE T.IOT_NAME IS NULL  -- skip overflow tables of index organized tables  
         AND T.TABLE_NAME NOT LIKE 'DR$%$_' -- skip fulltext index  
     ) T, PA  
     WHERE PA.GRANTEE IS NOT NULL  
     AND NOT EXISTS (  -- this table is not part of materialized view log   
         SELECT --+ NO_UNNEST  
             1  
         FROM SYS.ALL_MVIEW_LOGS MV  
         WHERE MV.LOG_TABLE = T.TABLE_NAME  
         AND MV.LOG_OWNER = PA.OWNER  
     )  
     UNION ALL   
     -- Function, Procedure, Package, Type  
     -------------------------------  
     SELECT 'GRANT EXECUTE ON ' || PA.OWNER || '.' || T.OBJECT_NAME || ' TO ' || PA.GRANTEE GRANT_STAT,  
         'CREATE OR REPLACE SYNONYM ' || PA.GRANTEE || '.' || OBJECT_NAME   
         || ' FOR ' || PA.OWNER || '.' || OBJECT_NAME CREATE_STAT,  
         'REVOKE EXECUTE ON ' || PA.OWNER || '.' || T.OBJECT_NAME || ' FROM ' || PA.GRANTEE REVOKE_STAT,   
         'DROP SYNONYM ' || PA.GRANTEE || '.' || OBJECT_NAME DROP_STAT,  
         PA.OWNER, T.OBJECT_NAME,   
         T.OBJECT_TYPE,   
         'SYNONYM' DEST_OBJECT_TYPE,  
         '' ADMIN_GRANT_STAT  
     FROM SYS.USER_OBJECTS T, PA  
     WHERE PA.GRANTEE IS NOT NULL  
     AND T.OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE','TYPE')  
     UNION ALL -- Sequences  
     SELECT 'GRANT SELECT ON ' || PA.OWNER || '.' || T.SEQUENCE_NAME || ' TO ' || PA.GRANTEE GRANT_STAT,  
         'CREATE OR REPLACE SYNONYM ' || PA.GRANTEE || '.' || SEQUENCE_NAME   
         || ' FOR ' || PA.OWNER || '.' || SEQUENCE_NAME CREATE_STAT,  
         'REVOKE SELECT ON ' || PA.OWNER || '.' || T.SEQUENCE_NAME || ' FROM ' || PA.GRANTEE REVOKE_STAT,   
         'DROP SYNONYM ' || PA.GRANTEE || '.' || SEQUENCE_NAME DROP_STAT,  
         PA.OWNER, T.SEQUENCE_NAME OBJECT_NAME,   
         'SEQUENCE' OBJECT_TYPE,   
         'SYNONYM' DEST_OBJECT_TYPE,  
         '' ADMIN_GRANT_STAT  
     FROM SYS.USER_SEQUENCES T, PA  
     WHERE PA.GRANTEE IS NOT NULL  
 )  
 SELECT S.OWNER, S.OBJECT_NAME, S.OBJECT_TYPE, S.DEST_OBJECT_TYPE,  
     S.ADMIN_GRANT_STAT,   
     case when EXISTS (  
             SELECT 1   
             FROM SYS.ALL_DEPENDENCIES D  
             WHERE D.OWNER = PA.GRANTEE  
             AND D.NAME = S.OBJECT_NAME  
             and D.REFERENCED_OWNER= S.OWNER  
             and D.REFERENCED_NAME = S.OBJECT_NAME  
             AND D.REFERENCED_TYPE = S.OBJECT_TYPE  
         ) or EXISTS (  
             SELECT 1   
             FROM SYS.ALL_OBJECTS OBJ   
             WHERE OBJ.OWNER = PA.GRANTEE  
             AND OBJ.OBJECT_NAME = S.OBJECT_NAME  
             AND OBJ.OBJECT_TYPE = S.DEST_OBJECT_TYPE  
         ) then 'Y' else 'N'  
     end DEST_OBJECT_EXISTS,  
     case when EXISTS (  
             SELECT 1   
             FROM SYS.ALL_SYNONYMS SYN  
             WHERE SYN.SYNONYM_NAME = S.OBJECT_NAME  
             AND SYN.OWNER = 'PUBLIC'  
         ) then 'Y' else 'N'  
     end CONFLICTING_OBJECT_EXISTS,  
     PA.GRANTEE DEST_SCHEMA,  
     S.GRANT_STAT, S.CREATE_STAT, S.REVOKE_STAT, S.DROP_STAT  
 FROM STATS_Q S, PA;  

Rich Hickeys - 'Database as a Value' concept & Oracle 12c 'Flashback Archive'

For a long time, I used triggers when creating Oracle applications with input protocols.
I often had the same performance problem as Barbara in the following article:
http://www.oracle.com/technetwork/issue-archive/2016/16-mar/o26performance-2925662.html
-----------
The better way leads use to use flashback archive as a table property.

The most important new features are:
* Own logging can be omitted because every change is available as a chronological log.
* A chronological protocol helps to identify unnecessary program steps (for example, delete and re-create rather than merge) and thus optimize.
* For debugging, the state of the database can be retrieved before a program crashes.
* Synchronization jobs that regularly process last updates only need to remember the last call time
to find the last updates on the next call.
* Accounting rules can be adhered to.
- The assignment of data entry to a responsible person and time.
- Entries are not overwritten but automatically canceled and entered again
and that is not the quality of any program. It can not be bypassed.
-----------
I'm glad that these flashback methods in Oracle 12c are now finally available and that they could easily be used. If the FLASHBACK ARCHIVE and ROW ARCHIVAL properties are set for all normal tables in a schema (for recording time) and dbms_flashback_archive.set_context_level has been set (for recording context inc. responsible persons), then an Immutable Database is created with completely new, really wonderful features. When points in time is available as an additional dimension to all objects, then objects become facts and the "Epochal Time Model" can be used.

Quick learning:
(Rich Hickey: Database as a Value)
https://youtu.be/EKdV1IgAaFc?t=12m13s
Minute 12 to 16

Famous applications that make use of this model are the Mac OS Time Machine from Apple and Source Tree for git.
----
and for Oracle see:

https://youtu.be/ZU9OnoHhbNM?t=26m15s
--- Flashback Data Archive ---
* Now available in all licensed editions
* Replaces the trigger approach to populate history tables
* Designate a tablespace to hold history data along with the retention period for the data
+ Specify FLASHBACK ARCHIVE for tables ore ALTER TABLE
* Flashback data can be used
* to look at historical data at a point in time
* to correct data committed to a base table
* Supported with most of the common DDL commands against the source table

--- In-Database Archiving ---
* Enhancement to Flashback Data Archive
* Mark table rows as invisible (logical delete)
* ALTER TABLE ... ROW ARCHIVAL

----
Relevant information from Connor McDonalds article in excerpts:

discover Oracle Flashback’s Oracle Flashback Data Archive feature:
“Flashback Data Archive provides the ability to track and store all transactional
changes to a table over its lifetime. It is no longer necessary to build
this intelligence into your application. A Flashback Data Archive is useful for
compliance with record stage policies and audit reports.”


... a better way.:
First she allocates some storage to hold her archive data:

SQL> create tablespace SPACE_FOR_ARCHIVE
  2  datafile 'C:\ORACLE\DB12\ARCH_SPACE.DBF'
  3  size 50G;

Tablespace created.

Next, she creates a new structure, called a flashback archive, which defines the retention duration of the row change history for any table placed into it.

SQL> CREATE FLASHBACK ARCHIVE longterm
  2  TABLESPACE space_for_archive
  3  RETENTION 1 YEAR;

Flashback archive created

And then she simply associates her HR tables with the flashback archive.

SQL> ALTER TABLE EMPLOYEES FLASHBACK ARCHIVE LONGTERM;

Table altered.

Code Listing 8: Execution plan for tables associated with Flashback Data Archive

SQL> set autotrace traceonly explain
SQL> select * from EMPLOYEES AS OF TIMESTAMP SYSDATE-3;

Code Listing 9: Flashback Version Query

SQL> select EMPLOYEE_ID, FIRST_NAME, JOB_ID, VACATION_BALANCE,
  2         VERSIONS_STARTTIME TS,
  3         nvl(VERSIONS_OPERATION,'I') OP
  4  from EMPLOYEES
  5  versions between timestamp  timestamp '2016-01-11 08:20:00' and systimestamp
  6  where EMPLOYEE_ID = 100
  7  order by EMPLOYEE_ID, ts;

Enable the capture of context-level information automatically and without changes to the base tables.
Barbara contacts her administrator and requests context-level tracking to be enabled for Flashback Data Archive:

SQL> exec dbms_flashback_archive.set_context_level('ALL');

Code Listing 10: Flashback Version Query with context information

SQL> select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
  2         VERSIONS_STARTTIME TS,
  3         dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS who,
  4         dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program
  5  from EMPLOYEES
  6  versions between timestamp  timestamp '2016-01-11 08:20:00' and systimestamp
  7  where EMPLOYEE_ID = 100
  8  order by EMPLOYEE_ID, ts;

-------------------

Oracle Flashback Version Query - retrieve metadata and historical data
for a specific time interval. You can view all the rows of a table that
ever existed during a given time interval. Metadata about the different
versions of rows includes start and end time, type of change operation,
and identity of the transaction that created the row version. You use
the VERSIONS BETWEEN clause of the SELECT statement to create a
Flashback Version Query.

https://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_fl.htm

------------------
I learned from Tom Kyte that in 12c many insert triggers (also for Apex 5 programs) are obsolete.
https://youtu.be/-mK75UyIblQ?t=1m
Minute 1.00 bis 2:14

Code Listing 11: A new template for common rows initialization
create sequence department_seq;

create table departments (
    department_id                  -- number generated by default on null as identity -- oder explizit
                                   number default on null department_seq.nextval
                                   constraint departments_id_pk primary key,
    name                           varchar2(255) not null,
    location                       varchar2(4000),
    country                        varchar2(4000),
    created                        date default on null sysdate not null,
    created_by                     varchar2(255) default on null nvl(sys_context('APEX$SESSION','APP_USER'),user) not null,
    updated                        date default on null sysdate not null,
    updated_by                     varchar2(255) default on null nvl(sys_context('APEX$SESSION','APP_USER'),user) not null
)
;

-- triggers
create or replace trigger tgr_departments_bu
    before update
    on departments
    for each row
begin
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end departments_biu;
/

------------------
Catch the Apex Session Context:

I have learned that with the option
exec dbms_flashback_archive.set_context_level ('ALL');
the entire context including custom context is recorded.
The following query returns in the Apex environment (z.B. Apex 5/ SQL Workshop / SQL Commands)
3 Session Variables that are probably recorded. (Namespace APEX$SESSION, Attribute: APP_SESSION, APP_USER, WORKSPACE_ID)


SELECT * FROM SESSION_CONTEXT;

-- Then there are also a bunch of variables in the namespace USERENV:
select
          sys_context ('userenv','ACTION') ACTION,
          sys_context ('userenv','AUDITED_CURSORID') AUDITED_CURSORID,
          sys_context ('userenv','AUTHENTICATED_IDENTITY') AUTHENTICATED_IDENTITY,
          sys_context ('userenv','AUTHENTICATION_DATA') AUTHENTICATION_DATA,
          sys_context ('userenv','AUTHENTICATION_METHOD') AUTHENTICATION_METHOD,
          sys_context ('userenv','BG_JOB_ID') BG_JOB_ID,
          sys_context ('userenv','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
          sys_context ('userenv','CLIENT_INFO') CLIENT_INFO,
          sys_context ('userenv','CURRENT_BIND') CURRENT_BIND,
          sys_context ('userenv','CURRENT_EDITION_ID') CURRENT_EDITION_ID,
          sys_context ('userenv','CURRENT_EDITION_NAME') CURRENT_EDITION_NAME,
          sys_context ('userenv','CURRENT_SCHEMA') CURRENT_SCHEMA,
          sys_context ('userenv','CURRENT_SCHEMAID') CURRENT_SCHEMAID,
          sys_context ('userenv','CURRENT_SQL') CURRENT_SQL,
          sys_context ('userenv','CURRENT_SQLn') CURRENT_SQLn,
          sys_context ('userenv','CURRENT_SQL_LENGTH') CURRENT_SQL_LENGTH,
          sys_context ('userenv','CURRENT_USER') CURRENT_USER,
          sys_context ('userenv','CURRENT_USERID') CURRENT_USERID,
          sys_context ('userenv','DATABASE_ROLE') DATABASE_ROLE,
          sys_context ('userenv','DB_DOMAIN') DB_DOMAIN,
          sys_context ('userenv','DB_NAME') DB_NAME,
          sys_context ('userenv','DB_UNIQUE_NAME') DB_UNIQUE_NAME,
          sys_context ('userenv','DBLINK_INFO') DBLINK_INFO,
          sys_context ('userenv','ENTRYID') ENTRYID,
          sys_context ('userenv','ENTERPRISE_IDENTITY') ENTERPRISE_IDENTITY,
          sys_context ('userenv','FG_JOB_ID') FG_JOB_ID,
          sys_context ('userenv','GLOBAL_CONTEXT_MEMORY') GLOBAL_CONTEXT_MEMORY,
          sys_context ('userenv','GLOBAL_UID') GLOBAL_UID,
          sys_context ('userenv','HOST') HOST,
          sys_context ('userenv','IDENTIFICATION_TYPE') IDENTIFICATION_TYPE,
          sys_context ('userenv','INSTANCE') INSTANCE,
          sys_context ('userenv','INSTANCE_NAME') INSTANCE_NAME,
          sys_context ('userenv','IP_ADDRESS') IP_ADDRESS,
          sys_context ('userenv','ISDBA') ISDBA,
          sys_context ('userenv','LANG') LANG,
          sys_context ('userenv','LANGUAGE') LANGUAGE,
          sys_context ('userenv','MODULE') MODULE,
          sys_context ('userenv','NETWORK_PROTOCOL') NETWORK_PROTOCOL,
          sys_context ('userenv','NLS_CALENDAR') NLS_CALENDAR,
          sys_context ('userenv','NLS_CURRENCY') NLS_CURRENCY,
          sys_context ('userenv','NLS_DATE_FORMAT') NLS_DATE_FORMAT,
          sys_context ('userenv','NLS_DATE_LANGUAGE') NLS_DATE_LANGUAGE,
          sys_context ('userenv','NLS_SORT') NLS_SORT,
          sys_context ('userenv','NLS_TERRITORY') NLS_TERRITORY,
          sys_context ('userenv','OS_USER') OS_USER,
          sys_context ('userenv','POLICY_INVOKER') POLICY_INVOKER,
          sys_context ('userenv','PROXY_ENTERPRISE_IDENTITY') PROXY_ENTERPRISE_IDENTITY,
          sys_context ('userenv','PROXY_USER') PROXY_USER,
          sys_context ('userenv','PROXY_USERID') PROXY_USERID,
          sys_context ('userenv','SERVER_HOST') SERVER_HOST,
          sys_context ('userenv','SERVICE_NAME') SERVICE_NAME,
          sys_context ('userenv','SESSION_EDITION_ID') SESSION_EDITION_ID,
          sys_context ('userenv','SESSION_EDITION_NAME') SESSION_EDITION_NAME,
          sys_context ('userenv','SESSION_USER') SESSION_USER,
          sys_context ('userenv','SESSION_USERID') SESSION_USERID,
          sys_context ('userenv','SESSIONID') SESSIONID,
          sys_context ('userenv','SID') SID,
          sys_context ('userenv','STATEMENTID') STATEMENTID,
          sys_context ('userenv','TERMINAL') TERMINAL
        from dual;

-- If I combine this correctly, the history query (Code Listing 10) would need to be expandable as follows:

SQL> select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
        VERSIONS_STARTTIME TS,
       nvl(dbms_flashback_archive.get_sys_context(versions_xid,('APEX$SESSION','APP_USER'),
        dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER')) AS who,
        dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program,
        dbms_flashback_archive.get_sys_context(versions_xid, 'APEX$SESSION​','APP_SESSION') AS app_session
        dbms_flashback_archive.get_sys_context(versions_xid, 'APEX$SESSION​','WORKSPACE_ID​​') AS workspace_id
  from EMPLOYEES
  versions between timestamp  timestamp '2016-01-11 08:20:00' and systimestamp
  where EMPLOYEE_ID = 100
  order by EMPLOYEE_ID, ts;

----
Here are some Screenshots from my Schema Riser Application that displays a Change Log Report.

Screenshot 1

Screenshot 2

----
Regards
Dirk Strack