Thursday, 10 October 2019

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)

Monday, 7 October 2019

So you want to learn Oracle APEX?

Are you involved with Oracle? Perhaps your a DBA, or a PL/SQL data master, possibly know a bit about Oracle Forms, and you want to learn what all the fuss is about regarding Oracle Application Express (APEX)?

My best elevator description? It's a low code development tool that lives in the browser. Yep, the IDE is a web page that allows you to add SQL, PL/SQL, modify a few attributes - and you've got a data driven web based application that runs on any device. Easy for any Oracle technologist to learn.

This is not a new tool, it's been around since 2004, conceptually even before then.
We've built applications that have lasted for years with minimal inteference. They've outlasted other popular frameworks, and they're still within touch of the present. Still doing the basics of the web, in a clever, interactive way.

AskTom has been using this technology before it was called HTML DB.

It's also being mentioned out there, in the world beyond our bubble.

And it's hitting the news for the right reasons - helping natural disasters.

And helping people take action regarding climate change.

This all started from Kscope19, and it appears momentum continues to grow.

So do you want to board this hardy vessel?
The community is strong & passionate.

Use this as your starting resource

Sign up for a (no obligation) workspace on

Your web page is your IDE now. Lucky, the modern browser is a nifty place to be.

That's APEX. You paste SQL queries / PL/SQL API calls in a box, change some attributes, and there you go. Simples. You have data driven web applications, ready to use on any device.

As for skillset, I have a few JavaScript one-liners I use regularly, sometimes I embed some simple HTML, and I have a mechanical understanding of CSS - which is just queries against your page content.
You know how a web page works? You know Oracle stuff (SQL, stored procedures)? You'll be fine.

If you have Oracle Forms experience, maybe check this out.

Empty the cup.

Try the documented tutorial
Install some packaged applications, check out how certain features tick.

Looking for your source code? It is just meta-data in some tables, check out
select * from apex_dictionary;
select * from apex_applications;
See how far the rabbit hole goes.
SQL developer has some dedicated tools for the job.

Try the Maze Runner presentation by Jorge & Jackie, to find your feet.
Want to know what components are available? go to
Charts are particularly awesome:
Got a question? Try the forums.
Like a conversation? Try Slack
Happy to chip in for some help? askMax.
Want a place to visit, learn & keep in touch for 5 minutes every day? Try
They have a page titled "How to start as a developer".
Also check out the plug-ins page, to fill any gaps in the builder.
Steady blog stream available here.
Do you like to listen? There are currently two active podcasts.
If you like books, I particularly recommend the collaborative efforts.
If you like videos, I have a series, and plan another. Caleb Curry also has our back.

Need to load spreadsheet data? This. And this.
Want to talk to web services? Find Carsten.
Do you really need to produce a PDF? AOP (APEX Office Print) is your best option.
Moet dingen vertalen? Translate APEX
Want to get a little funky with your page interactions? Check out Maxime's award winning demo app.
I recommend you learn not what you can do for Dynamic Actions, but what dynamic actions can do for you.

Want a second opinion on this list? Try this Github, because it adapts.

Want to get serious about delivering your app? Get a piece of an 'always free' tier, and check out Dimitri's guide.

Note, the preferred pronounciation is a-pex, not app-ex. I blame my accent for any mispronounciations ;p This guy knows.

It's also "APEX", not "Apex". Possibly as not to be confused with SaleForce 'Apex', which if I recall is something pretending to be Java. "ApEx" is as painful as camelCase.

You generally need to prefix any searches with "oracle apex"

Or just ask a question on Twitter with the hashtag #orclapex. Someone is bound to point you in the right direction.

Note that even those without a Twitter account can browser activity in this tag. I recommend getting involved, it's an active zone for the Oracle team.
You might see it as #orclAPEX, which not only helps get it right, but helps #a11y

Let me know if you think something else belongs here.


Thursday, 26 September 2019

Thursday Thought: Look all around you

A current pleasure of mine after getting home while the sun is still pleasant is to take my (currently) nine eighteen month 5 year old for a stroll in her pram. It's been a while since I drafted this, time to tidy up the backlog...

We were lucky enough to live opposite some bushland, big enough where you can do a 30 minute loop and enjoy a mostly native tree/scrub area. There are obvious trails that are no problem for a pram, but still give plenty of bumps for the young one to practice her vocals.

Through the right combination of events - since coincidence has such amazing potential, I decided to walk down a dead end trail I rarely walk through and enjoyed it so much I had to share.

We recently had some short but hard rain - a nearby corridor of suburbia experienced some freakish hail. This made the ground a little firmer, and soon after some maintenance vehicles travelled this particular trail. That left it ripe to preserve footprints in such a way that I'm sure left a tale that I'm far from being able to fully interpret.

It made me think back to our ancestors and their reliance on the ability to interpret such information in a way to live longer. To know how long since predators passed by, and how long ago; follow prey in the right direction; recognise footsteps from strangers.

All I could do was recognise big dogs, small dogs, kangaroos - though it's been a while since I've seen them. I saw smaller prints that could be rabbits, cats, or both. A few bike tracks, human shoe prints over the tyre tracks - all in a variety of orders.

The dog tracks were erratic, kangaroos had an obvious direction - and where very far apart! I also considered what I couldn't see - any snake trails. I know plenty of other reptiles live in there, there's no reason to think I couldn't find any dugites or maybe even tiger snakes if I know where to look.

Earlier today as I left work the first thing I saw in the sky was a contrail that split the sky. The tropospheric winds must have been relatively still because the start of the trail was still quite defined. I enjoyed a little giggle at the chemtrail conspiracy theorists that think it must be poisoning day - they don't happen very often in Perth thanks to local conditions.

As I arrived home the perspective had changed, and by the time I was on my walk the vapour dissipated into such an amazing pattern, covering the setting sun.

I thought it best to try describe the nature I enjoyed this afternoon rather than post a few cool pictures. Partially because I thought my writing skills might benefit, and more profoundly - I think contemporary society needs to remember to take time out from technology and let the brain do it's work behind the scenes. Process the information of the day, enjoy the current surroundings, think about the most bizarre succession of thoughts that take you some place far from the present - or one of the favourite things I heard a former martial arts teacher say: find your thoughts, then lose them.

If you enjoy occasional ramblings like this that might provoke a few related thoughts in your own noggin, you might also enjoy Steven Feuerstein's personal blog ->, though he seems to have another major project on the go since I drafted this post.
You can also try using the Thursday Thought, Science and/or the Friday Fun labels on my blog.

Saturday, 21 September 2019

Change label dynamically in Oracle APEX

I was developing with Oracle Forms for an awesome project when I first heard about Twitter, and it was described to me by Jeff as a 'micro-blogging' site.

I think I did what could be my smallest blog post, as a tweet. A micro-blog, if you will.

Here is the snippet that dynamically updates a label, in this case, a floating label.
$("label[for='P1_NOTE']").text('Happy Friday!');

Which could also be written as
$("P1_NOTE_LABEL").text('Happy Friday!');

This could be executed within a dynamic action, as a result of a change to some field on your page, perhaps to help instruct the user.

Happy APEXing!

Wednesday, 18 September 2019

APEX 19.x Dark Mode CSS Extension

There have already been some insightful posts about APEX 19.2 Early Adopter, plus an interesting feed of features on Twitter, as they're discovered.

Sven has a great summary.
Adrian comments on markdown.
Mike is really plugging faceted search on twitter. I particularly look forward to playing with that one.

I couldn't resist giving the dark mode a test spin.

I immediately experienced the same contrasting issues from the original clean skin that 5.0 gave us, plus a few minor adjustments.

Spot the difference

Tweaks so far include

  • Region header
  • Delta icon
  • Text field background
  • Scrollbar width
  • Save message location (not seen)

I'm still trying to decide what the best contrast is to break through the darkness on the vertical scroll, but it's a good start.

As Mike Hichwa suggested, the experts may give this a twirl in 20.1.

You can find the CSS I use in this Github respository, but switching between the two modes will require manual interaction with the style extender, since there are no dark-mode classes to utilise.

I'm trying to lift my game a little, get more organised, and improve my markdown game.

Edit: Since I'm currently doing most of my work in 18.1, I forgot 19.1 introduced dark mode, not 19.2.

That said, I think Vita - Dark style added to the 19.2 Universal Theme offers better contrast than the page builder itself.

19.2 Dark UT style

Why do the users get the good stuff? ;p

Thursday, 5 September 2019

Paste from clipboard in Oracle APEX

Since APEX makes it so nifty, I've got a few pages simply used as query tools - handy to verify data during development.

I also use these pages to experiment with UX, and recently I thought I could save myself pressing ctrl+v to paste my ID into a field for lookup.

I knew we had done something recently for adding content to the clipboard, so I figured there would be a way to paste clipboard content into a field.

Sure enough, Dr Google had the answers, though it's bleeding edge. Good enough to experiment with.

For me, adapting this response to APEX meant setting the provided field name.

I added this to my JS global declaration.
async function paste(input) {
  const text = await navigator.clipboard.readText();

  $s(input, text);
And this when I clicked the button to action the paste, I nominate the item I want the clipboard data to be copied into.

An on-change dynamic action on this field would add a member to the collection, and refresh a number of regions that query the collection.

Don't forget to add Page Items to Submit

I have an example here.

I thought I noticed a potential odd behaviour with this, possibly due to the async action.
Therefore I've created an example with a few regions, and I'll try throttling the connection to see if that highlights the behaviour.

And so now I have this as a handy reference, I'll demonstrate some APEX collections and regular expression behaviour while I'm at it.

Monday, 26 August 2019

Oracle APEX Radio Group null option fix

APEX 5.1 introduced a template option that transformed radio groups into pill buttons.

I love this concept, it makes for a pleasant UI - an easier target for mouse clicks and finger taps.
I liked it so much I tried to do this myself prior to 5.1.

In 18.2 (fixed in 19.1), there's an issue with the way the null option presents for these radio groups - it's not in the same row.

Marko mentions a CSS fix for this problem in his post, but I find it has some side effects.
Stretched items are no longer stretched, and it ignores the Number of Columns attribute.

I went to inspect the generated HTML, and compare it to the 19.1 working solution, expecting to find some difference in CSS, but I quickly realised the null options is just generated in the wrong level on the tree.

You can also test this manually by using the in the browser tool to drag/drop that element to its siblings in the grid-row.

This means the problem can be solved with JavaScript when the page loads.
$('.apex-item-grid > .apex-item-option').each(function(){
This JavaScript identifies any radio options that are immediately below the grid, instead of being within the grid-row. Then for each instance, it moves that element to the next sibling, which is the grid-row.

Using appendTo() will push it to the end of the list.

And this solution honours the stretch option.

Is there a better way?
Other than upgrade to 19.x ;p


jQuery Cheatsheet