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!)
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.
very nice !
note that Oracle recommends that you use BLOB, not CLOB storage.for JSON data !
Fair point, and my presentation demos are all blob, but whilst I was digging around in the structure I wanted to easily *see* my data 🙂 But yes, if this was a genuine production application, it would be definitely be BLOB for most storage efficiency.
Did you validate dates and numbers before inserting the json into the CLOB?
First off I want to say great blog! I had a quick question in which I’d like to ask if you
do not mind. I was interested to know how you center yourself and clear your thoughts prior to writing.
I’ve had difficulty clearing my thoughts in getting my ideas out.
I truly do take pleasure in writing however it just seems
like the first 10 to 15 minutes are wasted simply just trying
to figure out how to begin. Any suggestions or hints?
Well…we are one and the same 🙂
I often know *exactly* the idea I want to convey…..and then ponder for a long time on the *how* to convey it.
Ultimately, I think the best way is stories, anecdotes etc….People respond best I think to “here is a (potential) pain point or obstacle, and here is how is we are going to solve it, or move forward”. People often identify with that.