Behold the full stack developer

Well…sort of.  Well…not really

This blog post is really about how with just a little bit of knowledge and patience you can bring several cool technologies and tools together to achieve what you want.

I’m off to OpenWorld at the start of October which will be an awesome event as usual, and in the last few days, the session catalog has been released, detailing the topics and the scheduling for events throughout the OpenWorld conference.

For the typical conference attendee, the schedule builder is perfect for finding the topics you’re interested in and planning out your week.  But for old dinosaurs like myself, my requirements are perhaps different from the norm.  Sure, there will be topics on functionality and features that I’m not familiar with, which I will try to attend so that I can broaden my skill set.  But also, there will be topics I’m already familiar with, but the speaker is a colleague I want to support, or one that I have a lot of respect for.  And sometimes the sessions that colleagues are attending will clash, or two sessions adjacent in time will not be adjacent in location! So for me, it is the combination of topic and speaker and location and scheduled time that drives my decision making when planning out the week (for those brief periods during the conference where as an employee I have time to visit a session!)

And combining speaker and topic into an “at a glance” view using the session catalog is not possible (because I doubt there is a demand for it).  You need to search on a speaker, and then explore topics.  Or you can search on topics and then expand each topic to see the speaker.  I’m simply too lazy for either approach . In past years, I would use a blank search criteria, then keep scrolling down until all results were shown, then use the “View Source” function in my browser to get all of the raw data, and then use some basic scripting (awk, sed, etc) to get a list of topics and speakers.  This year that technique did not work, because the content is all driven by Javascript.  So whatever session results are on the screen, the “View Source” simply shows a reference to a stack of Javascript code and no session content was available.  So I figured I was out of luck.

Then I stumbled upon this blog post by my friend Lucas Jellema from AMIS, where he had encountered the same issue.  Lucas is much more fluent with dealing with web site content than I, and described using node as a means of pseudo-automatically driving the site via API’s.  If you haven’t read the post, please do – it’s a cracking good read.  Lucas’s script could grab the session catalog detail and store it as a JSON file on a local drive. 

So …. task #1.  How to get node running on my laptop?  That was fairly easy – you just download the version you want, unzip it, and you’re good to go.  I then wrote my very first node program.

Woo hoo ! All systems go !  I figured at this point I was ready to be an enterprise node developer

I then made some small modifications to Lucas’s script for my own environment, and ran it on my laptop.

Hmmmm…Not so good.  Maybe I’m not quite ready for enterprise developer status yet   So with a little more reading about node modules, npm, and some fresh downloads, I was ready for a second attempt.

Jackpot !  I now had a JSON file that looked like it contained the details I needed.

I figured I should now be able to break out the sed, awk as per normal, write up a fresh set of parsing scripts and convert the JSON into a nice simple report for myself.  And that it dawned on me.  I’d spent a good chunk of time this and last year at events talking about all the cool JSON facilities in Oracle 12c, so surely it would make a lot more sense to load that JSON into my database, aka “Eat my own dog food” as the expression goes.  And that was remarkably simple to do.  I just created a table with a single CLOB to hold the JSON, and loaded it from the file with some PL/SQL.

 

Now that it was loaded into the database, I didn’t have to worry about manually examining the JSON to work out it’s structure (because this was a 400,000 line JSON document over 7 megabytes in size!).  I just threw the JSON_DATAGUIDE at it to give me a report on what the JSON structure looked like.

At that point it was time to head into SQL Developer so that I could do some query experimentation with the JSON_TABLE clause to convert my JSON structure into a simple report.  This took a few iterations because the session catalog JSON contains a lot of cross-references, presumably make the site navigation simpler.  So speakers had their sessions as “children”, but also sessions had speakers as children, and of course some sessions are actually hands-on labs which repeat over a number of days etc.  But ultimately, I managed to get the majority of what I needed with a little bit of JSON_TABLE and Analytic functions to weed out duplicates.

 

Now I had what I wanted – a simple relational view of the OpenWorld session catalog.  So I started adding some basic WHERE clauses to see what my friends Maria and Chris were doing at OpenWorld this year.  And then some predicates on days, times, etc.  And once again, there was a light bulb moment:

“Why not do this with Application Express?”

An interactive report takes about 30 seconds to create and gives me all the filtering facilities with no more queries to write.  Using the cool  /* INSERT */ hint in SQL Developer, it was trivial to create some DML to load my session catalog into a simple relational table.

Now all I needed was an Apex environment to get my little application created.  And as most people will know, there’s one out there on the internet for everyone to use, and it’s free.  So I requested a fresh workspace on apex.oracle.com and I was off and running.  A few clicks in the App Builder and in the SQL Workshop to load my data and my application was ready.

 

And there we have it.  My speaker/session cross reference application built with node, JSON, Oracle Database 12c Release 2, SQL Developer, Application Express and of course…the thing that glues it all together:  SQL

Obviously its accuracy is only as good as the time at which I grabbed the data (and we’re still a month away from OpenWorld) and only as good as my JSON parsing capabilities, which is perhaps the larger risk   But it will do for me and you’re welcome to explore it as well.

https://apex.oracle.com/pls/apex/f?p=OOW17