Application Express Views - Key discovery part 2

Yesterday, I showed how the new Foreign Key discovery works on a subset of dba views.  The only catch was that the end result had T_DBA_  prefixes for the names.  It's really easy to change that.

Once you import the views and convert them to tables, delete the views.  What you are left with is a bunch of tables prefixed with T_ .  Now right click on the relational model in the tree and choose Change Object Names Prefix.

This is a simple dialog, much like a search and replace.  Enter the old, the new, and what object types it applies to and everything is back to the base names you'd expect in a diagram.

The end result is a model of all the APEX_ views for 4.0.

Oracle Application Express Views

Data Dictionary Posters and Automatic FK discovery

We have all had posters on the wall at some point that are the data dictionary.  That is if you made it to the booths before they ran out at Oracle Open World or other events.  There's a few new features in the FREE Oracle SQL Developer Data Modeler that went production this week that can help avoid the need for the posters.  

First we have to import from the data dictionary, the data dictionary.

I'm filtering to just the DBA_T* views.  This filter is feature #1 that helps in prior releases it was individually selecting the objects to import, select all, and select none.  This helps to narrow down and then do a select all to the views you want to see.

Now that we have the views imported, the next step is to convert the views to tables, Feature #2.  Since views don't have FKs we swap them over to tables.  The end result of this wizard will create tables all prefixed with T_  .  This is done because the views remain in the model.  There's ways around this like bulk renaming the views from DBA_ to V_DBA_ then the tables could take the base name but that's for another day/blog.

Now that we have tables, we just have to put a primary key on the main driving table.  In this case, tablespace_name.

At this point we have a model which has a few tables and only one of which has a primary key.  This is the same place we could be if we import some tables from the first step in the import wizard.

Now for the main event , feature #3,  right click on the relational model in the menu and choose "Discover Foreign Keys"

You will get a list of the foreign keys that have been found. Click Ok.

The end result is a model of the data dictionary on the subset of views/tables with the proper lines connecting.