Ideas as a precursor to Oracle APEX #JoelKallmanDay

 It seems if I post only once a year, it's for Joel Kallman Day...

Last year I hinted of a small side story to a remediation project that was upgrade the entire stack supporting a (mostly) Oracle Forms application.

Along the way I also had to remediate a little one-Form tool Connor McDonald build for developers, and it had some features of some note.

It should be stated this was written at a time when flip phones were all the rage - and notably before Oracle APEX gave birth to Interactive Reports.

I won't include a screenshot (for privacy concerns), but it's basically a multi-row data block allowing queries and edits of selected records - an Editable Grid, back in the heyday of Forms.

Hmm, let's see a list of default display options, plus the ability to save my own current filter - which stores the values set in Enter-Query mode - remember that?!

Saved Reports

And let's see an ability to re-order the columns, and or even hide specific columns.

Re-order columns

Yes, Connor created a customisable editable grid, in Oracle Forms, a few years before smartphones were a thing.

Editable Grid in APEX

All these highlighted features that come straight of the Oracle APEX box.

An example of great ideas that pre-date the technology that can better support them.

Or is it the ideas that drive the development of better technology?

Or perhaps a bit of both...

Upgrading Forms compared to APEX #JoelKallmanDay

My current project has involved rebuilding the entire aging stack for an environment that's currently 95% Forms, with any new work developed in Oracle APEX. The upgrade covers all layers -  from OS, to DB, to browser, and to application builders - all heading to contemporary versions.

The general directive is like-for-like, so the applications should work the way they used to, if not a little better.

So to dumb down the process, we basically need to recompile everything on the new stack, fix the gaps, then test extensively.

***

It's been an interesting project to compare the type of effort required to uplift a large Forms application compared with a modest set of Oracle APEX applications. I do this with the awareness that it's like comparing apples and toaster ovens, but the outcomes have been interesting enough (to me, at least), that it's forming the basis of my next presentation.

In fairness, there wasn't too much in regard to general syntax used within Forms that needed addressing. Nothing has significantly changed in Forms functionality, and we just needed the app to function as it did previously. 

There were a few environment level settings we found to help with some backwards compatibility, thereby saving some rewrite. 

From an infrastructure perspective, however, there were a number of aspects we needed to tackle to keep up with the modernisation of the stack. This included the launcher (Applet->FSAL), the SSO mechanism, document (blob) handling, and guiding the application to a tidy close after a timeout occurred. Compared to Oracle APEX in a modern ecosystem, the sting of that last item will linger.

***

This application has a considerable amount of blob content stored in (now deprecated) OrdImage data types, so that one item took up a considerable portion of effort - from shifting the data; to modifying the UI to load and view the attachments.

Oracle APEX came to the rescue for this case, replacing an old mod_plsql interface that supplemented a Form, and an ancient looking webutil UI in another.

The saddest part of the Forms upgrade was the amount of time I needed to spend ensuring session timeout didn't get the user stuck in a dead session. Part of me kept thinking how much of that 95% we could have started to shift to APEX with that time... 

***

As for the APEX applications, we took advantage of the testing that was going to happen and updated the Universal Theme (UT) of the applications.

Prompt to upgrade the APEX UT

I'm working through the issues that came out of this for the presentation, but most of the issues we needed to remediate were due to the UT update - not the upgrade of the database and the Oracle APEX version. And even then, most of the issues are relatively benign UI differences which don't need attention. A good handful of which were on purpose, for the benefit of APEX users - just against the fine grain of "like for like".

We could have upgraded both the database and the APEX version, left the UT alone, and only had a few minor version related adjustments to take care of - then still have a nice new bag of tricks to explore.

This is a far cry from earlier days of APEX where a big hurdle to upgrades was contemplating the significate cost of migrating to a newer theme with improved considerations.

But again, this wasn't necessary. I recall Joel presenting a session where he took an APEX application built some time ago, spanning a gap of multiple APEX versions. He just imported the application, and it ran without change - even when APEX was still a comparative youngster.

This methodology and mindset hasn't changed. I've done a few APEX upgrades in my time, and my recollection of any issues were related to heavy customisation - rarely native usage. Any native changes were always well documented, with a guide to transition to what is an improved outcome.

***

So today I share this heavily abbreviated and filtered anecdote of my recent explorations with Oracle technology, and once again remember Joel as a guiding force to help transform Oracle APEX into a development tool that has longevity without heavy maintenance, while still making the developer's life easier each release. 

And also a feature haven for those Forms developers out there.

I can even draft next year's post with another small side story from this upgrade project, reaching deeper into the past of Oracle APEX.

Pressing Pause on APEX #JoelKallmanDay

To honour this year's #JoelKallmanDay, I'd like to use this as an opportunity to speak about mental health, rather than get technical like I have done in the past.

Yes, my blog posts have been particularly lean - this is my first post since the same event in 2021 - I completely missed last year. Not just this special event, but from any posts whatsoever.

It's not that I've moved away from Oracle, nor that I'm short on inspiration - I'm still emailing myself ideas on a regular basis, but I haven't been setting time aside to transform them into something to post.

And I haven't been totally silent - I did update my Presentations page after presenting at a couple of local meetups this year. And you'll still find me on the Oracle and Stack Overflow forums.

There have been a few reasons for the slow-down in activity, but in part this has been due to my front-end development time being far less than it has been in the past. The Forms->APEX migration project I joined has been paused, and in the meantime I find myself on a project remediating the environment to first upgrade the stack from fax machines and teletype.... well, things aren't quite that old, but there's no point giving the car a flash new paint job without first replacing the tired old engine, right? 

But when you're into painting, mechanical work is far less exciting, and it can take a toll on how one feels about their job.

I still get to paint the odd fender, but that's only part-time.

Sunset after Eclipse 2023

It can't always be about rainbows and lollipops, but after many years of working in the candy factory at the end of a rainbow, I have to wait patiently for the new factory to open under a new rainbow.

Perhaps that's enough mixed-metaphors for now. I'm still here, still passionate about Oracle and the APEX product, and I have exciting times in my relatively near future.

It's also not the first time I've had setbacks of sorts. There have been significant development works that ended up being permanently parked. These were amazing builds that I had fun being involved with, but for one reason or another they either never ended up in the user's laps, or didn't stay there for long. However, besides feeling like my time was wasted, there wasn't really a direct impact on me.

Stepping away from the front-end and spending far more time in the back-end - or even finding myself updating some Oracle Forms and tweaking their configuration, really makes me appreciate what I've found in the highly productive world of Oracle APEX development.

In the meantime, I find community events like this, and perhaps also the first local conference in some time next month will invigorate me enough to take the time to revive my blogging vibe, and maybe a side project or two that haven't gone completely dormant.

To the reader: how have you handled lulls in your career? Have you found periods of time that, while perhaps still being technically interesting, haven't completely satiated the programmer's need to create? What did you do to quench your thirst?

APEX in tense. #JoelKallmanDay

Now I feel extremely luck to live in a part of the world that has been relatively free of impact from COVID-19, certainly from a societal health perspective.

That said, I'm fairly certain there has been an indirect impact on my non-billable APEX activities - one only needs to look at my blog history over the past few years. Blogging is an outlet I've always enjoyed, and I miss doing regularly. 'Next week' I'll start doing it more regularly. Next week...

Blog frequency dips

But I was flicking through some tweets and saw Tim's announcement regarding #JoelKallmanDay, an initiative extending from past appreciation days, but now honouring the late great Joel Kallman, who sadly succumbed to COVID-19.

While I may be late to the event, I still feel compelled to contribute in some form. I only had the honour of meeting Joel a few times, but I know he's been an influence in my outreach. And I certainly feel his passion through the extended Oracle APEX team, whom I've also shared some wonderful moments with. I think about him frequently.

Like Tim, I thought I'd do an abridged history of my path to APEX. I had my first exposure to generating HTML from PL/SQL back in University, though too late in my final year project to do anything exciting with it - but I felt back then it seemed quite a powerful concept.

That said, I ended up cutting my programming teeth with Oracle Forms for a good number of years before my employer in ~2008 asked/directed/suggested I learn Oracle APEX while it was v3.2.

I was really given the opportunity at that point to jump in the deep end with APEX and explore what could really be done when it comes to designing applications that help the user get the job done. Not only that, I started the conferencing thing and got exposure to people like me from all over the world.

It wasn't long before I started training other people to use Oracle APEX, and if nothing else, that certainly helps you learn the ins, outs, and shortcomings of your development tool. Combined with regular involvment in the online forums, where even answering a question I thought I knew often comes with other people contributing their own perspectives, offering an enhanced method, or other consideration.

I've since had the chance to speak at two Kscopes, where I met Joel and quite a few people in the APEX team. They're all so passionate about the product, and offer their own special skills to help mature the product in the right direction - adding features faster than I can learn them. Not only that, they all know how to have fun outside the technology world. Something particularly important for our mental health in these challenging times.

I've now been on a few projects where we're transitioning Forms application to the web-based world that APEX lives, and now I have the pleasure of helping convert a large Forms application I had a decent part in developing - back before I knew what APEX was.

For me this is a particularly exciting time. It was somewhat of a plunge to leave my previous job during these testing times, but it's opened a raft of new opportunities - all the while I wish I had more time to tinker with my own little APEX side projects. 

OGB Appreciation Day : Oracle Reports Server Queue Monitoring

Today is a day to celebrate what's great about our Oracle 'Groundbreakers' community. Our favourite stuff, what we're learning, horror stories, or just a little thanks.

I missed the second year because of holiday prep - so I decided to beat that this year by writing this the day I saw Tim's call-out on Twitter. Especially since I'm flying out to Melbourne for AUSOUG Connect, plus a little R&R.

I knew I had a few worthy ideas queued up, but I pondered for a moment on the most recent idea I noted.

So this year I'm going a little meta, or recursive, if you will - I'm acknowledging one of the posts from the original year. Oracle Reports Server Job Queue Monitoring. Thanks, Garth.

I wouldn't call this a duplicate post, rather a re-inforcement of this blog-fest, and I add some APEX spice.

I'm not entirely sure how I came across Garth's post, but I'm certain if it wasn't there, I wouldn't have realised I could transform that trusty but transient report queue servlet into table data in 3 easy steps.

A reliable page, but a little dated

And once they're in the database, we can APEX the shit out of it.

Now we can fold this data into awesomness

As Garth details in a second post, and Doc ID 2098657.1, it's a relatively simple matter of

1. DCL to define table owner
2. DDL to define some objects
3. Minor Report Server configuration

Now we can query rw_server_queue to see how many of which reports were executed each day by who.

Why would we want to do this? It's 2019, why am I blogging about an Oracle Reports feature?
So we can analyse reports usage in the legacy system before migrating to AOP, of course!

You can also find the relevant SQL here, to make it easier for some of us to find & utilise.

So that's now two #ThanksOGB references in one, illustrating how worthy participation in such an even can be.

Past posts

2016 OTN - Dynamic Actions
2017 ODC - (dog at my homework)
2018 ODC - APEX Workspace Activity Logs
2019 OGB - Oracle Reports Server Queue Monitoring
2020 OGB - (quite possibly something that also shares a precursor to Oracle Reports)

ODC Appreciation Day : APEX Workspace Activity Logs

Everything gets a special day these days, and thanks to Tim Hall's encouragement, you can enjoy a vibrant display of appreciation for what the Oracle Developer Community embraces about the technology they use daily.

The first year I talked about Dynamic Actions.
Last year I missed the boat - too busy preparing for some holidays.
This year I'd like to lay down my love for a supply of data every APEX developer has access to: apex_workspace_activity_log.

Yup, I'm talking about a log table.

It's enabled in your Oracle APEX applications by default, and inserts a record in a log table every time APEX renders a page, or a dynamic action interacting with the database thanks to a PL/SQL or Refresh action.

Every time a user opens a page, you know when, who, what, why, and how long it took to generate.

select apex_user as who
  ,application_id||':'||page_id as what
  ,view_date as when
  ,elapsed_time as how_long
  ,page_view_type as why
  ,request_value as more_why
from apex_workspace_activity_log
where application_id >= 4000
and apex_user != 'nobody'

How APEX Builder dashboard knows who's been the busiest developer

We can combine this information to produce a wealth of information. Oracle provides some out of the box under Administration -> Monitor Activity.

I think the first report worth noting is By Weighted Page Performance.

Packaged Reports

This report multiplies how many times certain pages were loaded by how long it took to load, so ordering gives weight to those pages consuming overall time. Here I've also highlighted the Median column, since that too can be an indicator. A higher average that maintains a low median can indicate outliers, while a raised median really should be looked at.

Weighted Page Performance

Fundamentally, we're asking - should I choose to tune a 3 second page that runs once a day, or tune a 1 second page that runs thousands of times a day?

I've taken this information to produce a range of drillable reports, starting with a beautiful OracleJET chart. This starts by day - guess where the weekend is?

OracleJET Plots Pretty Popular Page Performance

Another favourite of mine plots performance over time, allowing me to target specific pages.

It all reminds me of the first time I remember implementing such a practice in Oracle Forms. We had a pre-form trigger that would log who opened which Form when.

Using this information, we could communicate with the business about how often how many people really are using those 'super important' Forms.

Now APEX gives me this information out-of-the-box, and also tells me how long it spent working on each result. Awesome!

It's proof.
Proof that a user has (or hasn't) opened a particular page.
Proof that the application 'isn't running slow', well, the database is certainly ok.
Proof that people are in fact using your application.

From 5.x it also logs the Request value, allowing more granularity than ever before. With this I can do things like measure how often a page 3 is opened from page 1 vs page 2. Where are users clicking to navigate?

So thank you, Oracle APEX team, for continuing to baking such a resourceful feature into the product.

I use it every day.

#ThanksODC

Further reading:
- Start reading related documentation here.
- Martin shows us how to get started with archiving this data. It is possible to change the interval between the background table switching.
- Jeff offers an example showing recent users of APEX.
- I removed outliers in this post.
- I pasted a few other SQL examples in this forum question.

OTN Appreciation Day : APEX Dynamic Actions

I'm going to take advantage of the fact I live in a city so remote to many others in this amazing community, and schedule this post for my local 8am time. It's seems my schedule didn't work. Not the first fail from blogger... This might get me as one of the first posts in what's hopefully an interesting day amongst Oracle bloggers.

Tim Hall, a great producer of resources for Oracle technologists, suggested bloggers new and old post about their favourite feature, in appreciation for the professional network that is OTN.
https://oracle-base.com/blog/2016/09/28/otn-appreciation-day

I nominated APEX Dynamic Actions for sake of brevity in the subject line, and while they are pretty awesome as a whole, it does encompasses a range of sub-component features.

So I want to be more specific than that, I nominate the jQuery Selector attribute.

One of my favourite features

I could have selected one of a bunch of SQL functions (listagg, nvl2) or some PL/SQL features (%TYPE), or maybe something rarer like Oracle Text, but this one is a little more important to me.

For me it's a bridge between the Oracle world and the native web page we are generating.

With this attribute specifically, we can nominate, with precision, a component on the page and attach some event handler. We can make the web page communicate with the database.

Understanding how this feature operates I found like a gateway to building more interactive applications.

I looked back and it started with a post like this in 2012:
http://www.grassroots-oracle.com/2012/09/using-jquery-selector-in-apex.html
And took me so far as to write a book combining jQuery with APEX:
http://goo.gl/ZzQ0RP
(apparently chapter 9 has finally been corrected, more detail later)

Learning about this feature and further steering my career in an interesting direction required a community that blogged, participated in forums, tweeted, slacked, attended user group events, presented, networked, authored, and so on.

Giving also helps you learn. I do a little bit every now and then on the forums, and now I'm deemed a "legend". I read, watch interactions from interesting questions, and occasionally contribute. It helps in both directions, it's awesome, and it adds up. Same goes for presenting. A topic choice for next year is helping me learn what I need to keep my skills up to date.

#ThanksOTN