5 in 5 Vizualization 2 - Generating a d3.js visualization from a SQL Developer HTML report...

This second vizualization is more of a workflow proof of concept compared to some novel visualization but I can guarantee you haven't seen the SCOTT.EMP table like this before. I have wanted to combine my two favorite tools for a while. Here is how you can drive a d3.js visualization directly from a SQL Developer custom report.
d3.js is a powerful graphing tool library. d3.js's superpower is that it can bind data elements behind the scenes to graphical elements in a canvas. Familiarity with HTML, JavaScript, and CSS is helpful but not required. Before I started working with d3 my JavaScript knowledge consisted of how to set focus on a page and simple text field validations. Check out Scott Murray's fantastic book and website for a gentle introduction to getting things done with d3.
SQL Developer is a leading free database query and administration tool from Oracle.
Why would you want to combine them? Two great tastes that taste great together? Well here is my dilemma. I'm a DBA. I connect to databases hundreds of times in the course of a month to check on things. Traditionally serving up fancy graphs using HTML pages and JavaScript libraries is domain of the server side programmer. While I have plenty of experience playing that role, it doesn't fit with my current workflow. Instead of a server somewhere else being the 'hub' with all the server side smarts, I need to be able to pivot between different environments in a lightweight fashion - I can't install nor would I want to install a perl/python/php environment on every DB server where i wanted to produce some graphs.
So here is a way to leverage SQL Developer's features to grab data for us. We will create a shell of a HTML doc which can be used to create a custom graphic in d3.js. And we can share this with our friends pretty easily - no server side knowledge or admin privileges required.
Research
Looking at data for this POC I zeroed in on one of my favorite relationships - hierarchical relationships. These are all over the place in the data we work with day to do. There are some convenient ways to work with the data like Parent/Child or Drill Down reports. The problem is these often break down after your data is one or two levels deep. So this is great for say showing records on each side of an FK relationship, but when about when you want to see the SCOTT.TMP.MGR hierarchy from King all the way down to every employee you have to do some CONNECT BY LEVEL magic and you still have to deal with some tabular output oddness.
Data Organization

select (SELECT ENAME FROM EMP WHERE EMPNO=E.MGR) AS MGR_ENAME, 
       ENAME 
  FROM EMP E 
 WHERE MGR IS NOT NULL;

This returns 13 rows. Order is not important for this. The code that will accept this data will we will feed the data to make connections as they are introduced to the graphic.

Prototypes
I had already done a lot of work with d3 node graphs earlier this year so I started with some of my earlier prototypes and simplified them to the bare minimums.
Pop-Up Schemio V1
Pop-Up Schemio V5 - Click "Add Next Event" button to add objects to graph.
Construction
I usually prototype d3.js using tributary.io. It is a site where you can browse visualization and build your own. Tributary has a secret superpower of its own - it is a live coding environment. Great for people like me who casually know 20 languages and the one I know best is pseudocode. While changing code in that environment you can easily see the effects of you changes without the normal edit / refresh cycle associated with web development.
The Difficult part
Here is the difficult part. Determining where all the code lives. SQL Developer can output raw HTML so I can go wild with DBMS_OUTPUT statements. I wanted to avoid embedding all of my custom javascript inside of DBMS_OUTPUT statements as it would be cumbersome to write and maintain. I decided I needed the following 4 things:

• A HTML page generated from SQL Developer which contained a JSON representation of the data I wanted to use with d3.js
• A reference to the d3.js library
• A reference to a Style Sheet so I could make things pretty
• A reference to my custom .JS code that I will keep on a web server accessible to me. This can either be internet or intranet availability, just depends on what controls you want to take with your code. Technically you could keep them on your local PC as well but sharing would be harder. If the external files are available on the net you can export your report definition and share with friends. If on your local PC you would need to make them a bundle and make sure the file paths in the parent HTML document are available on their systems as well.
  Building this yourself
  1) Put the mynodecode.css and mynodecode.js on a web server accessible to you and your users.
  2) Create a User Defined Report of type "PL/SQL DBMS_OUTPUT". Use the reportcode.sql source. Modify the paths to the .css and .js files as needed.
  3) Right click on the UDR and select "HTML..."
  4) Set DB connection, select "Open When Complete" and click Apply.
  5) SQL Developer will render the report to a local HTML file and fire up a browser to view it.

Refinement
The biggest challenge here was organization of the code. I wanted SQL Developer to be responsible for pushing the data required for the visualization and wanted to avoid wrapping any heavy logic into the SQL Developer User Defined report itself. Review reportcode.sql for the trim amount of work I have SQL Developer contribute to this process.
v1 Result
Here is an example of the report running after everything is in place.