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