Behold the full stack developer

Well…sort of.  Well…not really Smile

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 Smile. 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.

image

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

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

image

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

image

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

image

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.

image

 

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.

image

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.

image

 

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.

image

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.

image

 

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 Smile  But it will do for me and you’re welcome to explore it as well.

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

Apex Interactive Grid and IOT’s

I love the interactive grid in Application Express.  And here’s why… (Warning: Ranting mode is now on Smile)

You can tell people

  • here’s an application built with almost no code, in fact, you probably could have built it yourself
  • it’s multi-user, with optimistic locking built in for you
  • it’s secure
  • it’s backed up and recoverable,
  • it’s scales ridiculously well,
  • it doesn’t need any complicated middle tier, or software libraries,
  • it can be accessed anywhere you have a browser…which is…anywhere!
  • it has responsive look and feel,
  • it was built with software that doesn’t cost a single dollar,
  • it centralises the data so you have a single source of truth

and after you have told them all of that….do you know what they’ll say ?

“Yeah…but I like to double-click on a field to edit it…So I’ll just use Excel and store it on my hard drive”

AGGGGHHHHHHHH!!!!!!!!!!!!!  Somebody…..shoot….me……

Interactive grids blow that flaccid argument out of the water!  So get on board to Application Express 5.1+ for some interactive grid awesome-ness.

One discovery I did make with interactive grids, is that if you based the grid on an Index-Organized table, you will erroneously get a ROWID column in your grid

int_grid_iot_proj_short

 

This is not a major drama – just delete the item from the designer and it will still work just fine, and this minor detail is fixed in an upcoming release.

Apex patch for 5.1

If you are running Application Express, there is a new patch available.  Lots of fixes which you can read about here

http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-511-patch-set-notes-3661846.html

I just downloaded patch 25341386 and followed the installation instructions and it went through with no problems at all in just a few minutes.


Verifying Application Express version...
...have version 5.1.0.00.45

PL/SQL procedure successfully completed.

Verifying Application Express version...
...have version 5.1.0.00.45

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.

Stopping and disabling APEX jobs

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.


Grant succeeded.

Verifying Application Express version...
...have version 5.1.0.00.45

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.


...
...
...

Validating Application Express
(09:02:38) Starting validate_apex for APEX_050100
(09:02:41) Checking missing sys privileges
(09:02:41) Recompiling
(09:02:42) Checking for objects that are still invalid
(09:02:42) Key object existence check
(09:02:42) Setting DBMS Registry for APEX to valid
(09:02:42) Exiting validate_apex

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

timing for: Complete Patch
Elapsed: 00:02:33.76

Enjoy !

End of an era …

Four years ago I wrote about a little volunteer project that my partner did.  A small association that provided outdoor experiences and facilities for kids with physical impairments needed a system to record member and volunteer details, plus a few other bits and pieces.  We built an Apex solution running on XE.  This week, they became part of a larger government initiative, and thus their Apex application was no longer needed and the information migrated to a centralised service.  There was a tinge of sadness about that, but I also was pleased with the outcomes of this “project” namely:

  • It ran for 4 years with virtually never an outage besides those related to power etc.  (After all, their “server” was just a PC in the secretary’s office Smile)
  • Their PC’s etc went through several iterations of patching, upgrades, replacements etc and the system was unaffected because it was entirely run in the browser
  • We never had a single issue with the database
  • Minimal maintenance needed.  In fact, the only “serious” bit of work needed after go live was when we discovered that their external drive (where we stored our database backups) was from time to time removed to be used for offsite file transfers, and when it was re-attached they would assign it a new drive letter.  So we adjusted our backup script to cycle through drive letters to “find” the disk and adjust the RMAN backup details accordingly.

That’s one of the great things with Apex, and a database-centric model.  It is just so well insulated from all the things that change most frequently, that is, those elements closest to the client.

So yesterday I took a final datapump export of the system as a “just in case” measure, and uninstalled the application and its dependencies from the PC.  But for four years, they had a successful application that provided all of their data entry needs and all of their reporting needs, and besides checking an occasional email to ensure the backups were working ok, took very little of my time. And surely that’s what all IT applications “aspire” to be – stuff that just plain works.

It never let them down and never cost them a cent.  You can’t tick any more boxes than that Smile

Apex upgrade 4.2 to 5.0.2

Just a quick note for anyone upgrading Apex on their systems.

The installation (into a non-multitenant 12.1.0.2 instance) went through with no problems, but tracing the installation suggests it will flush the shared pool 6 times during installation/upgrade.

That might have some impact on other applications/sessions running on that database, so best to find a quiet time to do it.

You never stop learning

My mate Scott Wesley, whose specialty is Apex, is currently at Kscope having the time of his life (well, I hope so Smile).  He tweeted this picture last night of his conference badge, mainly about the “I love Apex” buttons, but something else struck me.

scott_CIHbrDpWIAA1-Cc

 

Take a look at the last two ribbons attached to his badge.  I’ve known Scott for many years, and was even fortunate enough be a  mentor of his for awhile.  He’s a sharp dude, and is probably one of the best Apex resources locally in Australia and abroad.  This is why you see the deserved Oracle Ace designation.  But its his first Kscope, so he also gets the “newbie” ribbon. That reinforces the fact that no matter how much experience you have, no matter how much knowledge you have, there are always new things to learn, new things to explore, to continually expand your horizons as an Oracle professional.

So … try to keep a virtual badge on a virtual lanyard around you’re neck as you go about your working day.  It should always contain a “newbie” ribbon – so you’re always finding new challenges.