ThatJeffSmith

Generating HTML Data Dictionary Reports with Oracle SQL Developer

I’m going to go out on a limb and guess that most people who use SQL Developer do not realize it has a fully-baked data modeling tool built into it. It’s not like we hide it, but we kind of hide it. To open the Data Modeler inside of SQL Developer, navigate to the View menu, and activate the Data Modeler Browser, like so -

Running the Data Modeler inside of SQL Developer

Once opened, you’ll see a new ‘Browser’ panel beneath your Connections. You can use the File – Data Modeler – Open dialog to open any of your models. Or you could create a new physical model by importing a ‘Data Dictionary.’ This is code for, ‘reverse engineer an existing Oracle database.’

Your model will then be displayed in SQL Developer.

Why ALT+TAB when you can do everything in your IDE?

If you’ve gone through all this and you still don’t see your model diagrams, right-mouse-click on the model in the browser tree, and select ‘Show Diagram.’ This will activate the diagram in the SQL Developer interface as a new document.

Reporting for duty!

At some point someone is going to ask your for a ‘data dictionary report.’ You might think you know what this is, or maybe you have seen one before. In my experience, it’s 100% necessary to ask and confirm with your end-user EXACTLY what they mean by a ‘data dictionary report’, because everyone seems to have their own variation of what that means.

According to Wikipedia, IBM defines a data dictionary as

A data dictionary, or metadata repository, as defined in the IBM Dictionary of Computing, is a “centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format.”[1] The term may have one of several closely related meanings pertaining to databases and database management systems (DBMS):

  • a document describing a database or collection of databases
  • an integral component of a DBMS that is required to determine its structure
  • a piece of middleware that extends or supplants the native data dictionary of a DBMS

Again, ask your requester exactly what they are expecting in their report. I’m guessing that most of the time SQL Developer will be able to deliver on most of your requirements, for free!

New for v3.1!
Version 3 has this feature, but it’s limited to XML reporting and doesn’t allow for SubView filtering. To download v3.1 and read about all the new features, please visit our OTN page.

Ok, with that disclaimer out of the way, let’s get to building our HTML report!

With your model open and active in SQL Developer, go back to the File menu, and navigate to the Data Modeler – Reports item.

Pick your poison, I'm going with HTML today

You have a few options here. I enjoy working with sub models (which are known as ‘SubViews’ in SQL Developer.) When I generate a report I have the option to only include objects that are in a particular SubView. Hint: Subviews are very handy when dealing with very large models. They allow you to effectively filter your models and reports.

Once you click the ‘Generate Report’ button it will build out the HTML files.

Here’s a quick preview of what that looks like

Share your models with folks who do not have SQLDev or access to the database

There’s a quick look-up filter box. Type in your table name and the list will auto-filter to that text. Most folks deal with very, very large models – we’re talking many hundreds of entities and tables. Clicking on the table name auto-navigates you to the appropriate spot in the dictionary report.

If your end users also want the actual diagram component included, use the File – Data Modeler – Print Diagram interface. You can then include the image or PDF file to your HTML landing page.

I’ve uploaded my HTML report for you to go play with.

Hehehehehehe, he said ‘duty’ :)