Wednesday, 29 March 2017

Frequent APEX questions

I've put a call out on the OTN APEX forum asking for suggestions from the community on what they think are frequent APEX questions, be it within the forum or elsewhere.

In part, I'm chasing ideas for content in future presentations, or blog posts. I welcome others to do the same. I think if we share our perception of common questions, better resources will come.

So please, feel free to contribute in any way, shape, or form.

What questions do you see or hear all the time?

If for some bizarre reason you don't have an Oracle account for the OTN forum, just reply to this post, ... then register an account. It covers you for the forums,, the Dev Gym, entitles you to download stuff like SQL Developer, and no doubt more.

Though probably no steak knives.

Make Radio Group look like UT Buttons

It's pretty easy to convert links in APEX reports to use the Universal Theme button look & feel, as I described here

We can apply the same technique to radio groups, but it requires a little more work than just adding some classes, but nothing like jQuery mobile radio fiddles.

It took me a few goes to get the correct string in the correct APEX attribute to make the individual buttons look & behave the way I imagined, so I thought I'd share the journey. Show my working, so to speak.

First, create a radio item:

A humble radio group

Then set 'Option HTML attributes' to add template option classes, just like I did for the report action buttons. This applies the string to each option, rather than the entire item.

class="t-Button t-Button--simple"

If we check the page, the radio group should have button boxes surrounding the text labels.

Radio group with button class

To work out what CSS selector I needed to hide those radio buttons, I used Chrome's Inspect element tool (right click a radio button) so I can see how the HTML is constructed.

Inspect Element is the best browser tool. The best.

One shared property for each item is how the input tag has type=radio.
Further up you can see the entire fieldset as an id of P47_RADIO, our item.
Combine these to identify any radio buttons within this specific item.

Edit page 'Inline CSS' to using this combined CSS selector, with a property to hide any web component identified with that selector.

#P47_RADIO input[type=radio] {display:none;}

This tidies the button group, trouble is,  the current selection is unknown without the native radio button. This can be solved by creating a dynamic action that toggles button classes upon click of each radio item.

Create dynamic action on click of jQuery selector

#P47_RADIO span.t-Button

Add action to Execute JavaScript
// add simple class to all options
$('#P47_RADIO span.t-Button').addClass('t-Button--simple');
// remove from current selection
The result:

In my case the t-Button--simple template option is removed from the current selection, but you can play with these options to choose a combination that suits your desired contrast, using the various colours:


Or "hotness", a highlight:

Or contrast, well, not highlighting the entire button:
All demonstrated in the button builder reference in the Universal Theme app.

You can achieve consistent button width by adding this to your Inline CSS:
#P47_RADIO t-Button {width: 100px;}
After your page loads, you can use the inspect element tool and increase the width 'live', to find a figure/unit that works for you.

Now we need to ensure this selection is defaulted when the page opens. I prefer to execute the click() event as to simulate the actual action, as opposed to replicating the relevant addClass() function.

You can use a similar technique to conditionally hide a specific radio button in the group (though you still need to validate the availability to the user on the database end.)

Add this to your 'Execute when page loads' page attribute, instead of adding a Default Value the item itself.

$('#P47_RADIO input[value=P]').next().click()

Where the value=P represents the relevant radio item value. The .next() moves selection to the next sibling, which is the span element, where click is triggered.

Or you could consider this button group plugin.

Or in APEX 5.1 you can use the pill template option.

Or you could use some form of List template button group.

Or you can stick with tiny buttons only people using a mouse on a desktop PC can hope to aim for.

One of the beauties of Oracle APEX - so many options.

Happy APEXing!

Monday, 13 March 2017

Font APEX between versions

Sometimes, it's the little things in an application that make users happy.

Sometimes, it's just the icon on a darn button, card, or menu that makes all the difference.

Surely by now you've encountered using icons within Oracle APEX, and we've come a long way since Theme 25. To get anyone up to speed, APEX 5.0 saw the introduction of Font Awesome baked into the builder, which is a reputable CSS icon library for such things.

5.1 vs 5.0
As with other 'plugins', Font Awesome library releases new versions with new icons at a different schedule to APEX releases. It's easy to get your 5.0 instance up to speed with the latest library, thanks to Patrick's simple write-up.

Now the same goes for the 1000+ icons that come with Font APEX, some of them database specific which for some reason excites me a little. If you need to catch up, Dick Dral has a great low down on what makes Font APEX awesome, so to speak, and for those who are familiar with Dick's work, that's a double down on word play - see his URL of his sample application, which is growing to quite a utility.

But if you would like to use the latest library, or even use it within APEX 5.0, Max Tremblay gave us this great post. It's a little more involved than Patrick's, but things aren't as easy.

The long snapshot of menu icons shows the difference between 5.1 on the left, and 5.0 on the right. They've cleaner, they scale better, and we can no doubt all thank Shakeeb.

Max provided some CSS that's required to suit adjust usage to Font APEX, but having an app with icons found all over the place, I encountered some other components that needed adjustment. With some further back & forth on the APEX slack channel, Max helped me come up with these.
/* Font APEX 
CSS required to make Font APEX from 5.1 work in 5.0
Headstart provided by Max Tremblay

/* Most icons around the place */
.t-Icon[class*=' fa-'],.t-Icon[class^=fa-]{
    font-family: font-apex!important;
    font-size: initial;

/* Left side menu */
.t-TreeNav .a-TreeView-node--topLevel>.a-TreeView-content .fa{
    font-size: initial;

/* Left side sub menu */
#t_TreeNav ul ul .a-TreeView-content .fa {
    font-size: 12px;
    padding: 10px 0;

/* Navbar dropdown */
.t-NavigationBar-menu .a-Menu-content .a-Menu-item .fa {
    font-size: 12px;

/* Navbar top row */
.t-NavigationBar-item span.t-Icon {
    padding-right: 5px;
    padding-top: 0px;
    font-size: 14px;

/* Slide tooltip plugin */
.a-DetailedContentList-icon {
    padding-top: 10px;

/* Custom icon usage within breadcrumb */
.t-Breadcrumb-label .fa {
  padding-top: 3px; 
I think this once again shows the versatility APEX has between versions, quite literally replacing one plugin with another, with a little help from the community.


Saturday, 25 February 2017

Understanding the User's Perspective

Recently I encountered what I thought was an interesting allegory in regard to software development and understanding the user experience.

My three year old approached me with her bottle of milk and requested more, insisting it was empty.

Exhibit A - One non-empty receptacle

I assessed what was presented to me:

  1. There was milk in the container
  2. The straw reached the current milk level
  3. The straw was not blocked (I didn't feel like milk. I did not enjoy this test)

It worked for me.

So I returned the bottle and went back to ... cooking dinner. Right, let's go with that.

The user child came back telling me the page bottle still wasn't working, so I asked for a demonstration.

Of course, the user was doing it wrong.

All programmers know this is the eventual outcome because the user is always clicking the wrong buttons at the wrong times. The real challenge is working out what to do next.

In this case, I filled up the bottle with more milk. Any other outcome with a boundary-pushing young child will not be acceptable.

However, once gratified, I also educated the user with not only the appropriate use but why that's the case. I think the reason "why" is as opposed to "just because" is partially what's wrong with society's ability to comprehend and assess new information.

Have you worked it out yet? 

She was turning it upside-down to have a drink. This meant thanks to gravity, the milk was no longer near the end of the straw, and it was deemed 'empty'.

I figured she turned it upside-down because that's how most of the other drink bottles work. Then I realised that's not accurate. Most of her bottles involve sucking liquid up a straw. I'm not sure why she often makes this mistake with this particular bottle, but I figure if I teach her some basic physics, not only can she potentially work out the problem herself next time, but maybe even apply the reasoning to other problems.

So perhaps two lessons

1) Watch your users. Don't be creepy about it, but somehow I'm always stunned by a number of notes I make when watching users operate software.

2) Educate your users. There are so many ways you can give your users cues as to how to use your software, but the best-designed tools don't need instructions. They're inherently intuitive.

When testing our own software, the more we can think like users instead of developers, the better our product will become.

Sounds like an old adage, but what do you think of the analogy? Have you encountered similar ways to relate to non-IT folk?

Tuesday, 7 February 2017

2016 Blog Review

It's February, so it must be time to do this. I explore what thoughts arise from looking back, and forward to the future. It helps me to remember stuff, decide what to do, and apparently some people find it interesting.


So many tools. And I arguably don't use enough of them.

APEX 5.1

It's out and I'm excited. Unfortunately, scheduling is super tight, so I'll be waiting at my current site until hopefully around June, but I've certainly been enjoying the play on I look forward in particular to the OracleJET built-in charts, we've got some great dashboarding ideas to explore.

I've seen plenty of questions come through the forums on the Interactive Grid, but I'm by-passing most of those until I get the chance to use it. I sure know where it belongs, and I look forward to understanding how it ticks, and the implications it brings.

JET plug-in - work in progress

OracleJET Visualisations

After an interesting overview in the depths of OracleJET from Chris Muir, I assigned myself homework that's seen me quietly working away on some plugins, bringing OracleJET visualisation (charts) for use in 5.0. I'm not sure how successful the cultivation will become, but I am documenting my journey. Expect a decent series of articles on my process, but be patient. I've had a hiccup with JSON and I'm giving it a rest for a little bit.


We have 12c at our main client site and I appreciate some of the features it brings, so much so I look forward to 12.2, particularly LISTAGG.

I particularly like Identity columns; the row limiting clause, though I probably abuse it sometimes; and I reckon I must spike page hits for Tim's post about lateral & outer apply joins.

I'm starting to explore performance benefits of PL/SQL in WITH clause, and the UDF pragma, among other features.


My colleague has done some interesting stuff with parsing incoming JSON using 12.1 SQL, and I'm slowly exploring with my plugin generation using APEX_JSON and LISTAGG. With made another step deeper into 12c by upping MAX_STRING_SIZE to allow for processing of larger JSON.

I'm quite glad it's no longer XML.

It seems 12.2 will be bringing the remainder of tools required to really get going with this. There's quite a lot of polish I've seen in this release that makes it an attractive upgrade.


After many, many years using Textpad, I'm giving other editors a go. Atom has shown good promise, but I'm still yet to get it compiling my packages on a Windows box, though I haven't persisted.
There are some other minor niggles, but it does bring great benefits. I might give Sublime a fair go this year, however. There's beer in it for anyone who can get me compiling from a text editor that pleases me. Sorry, I only use SQL Developer for ad-hoc queries, compiling from scripts, and some not enough built-in reports.

I am casually interested in how Atom was built. I see Android Instant apps as being something to keep an eye on with this amazing JavaScript tech.

JavaScript and node.js

I really haven't given node.js a fair go, and if there was anything other than OracleJET visualisations that I want to learn this year, it's node.js. Maybe my QNAP will help me learn after all.


As much as I see the benefits of running a linux based operating system, I think I just need to accept my fate as Windows proficient, using Virtualbox when I can. Too many hurdles to transition operating systems when there is a world of user interface development to keep up with.


I can't add much to this from this year, except we've had a steady keel for 12 months. I'd like to move to nearly whatever the current version is when we up to 5.1.
I did notice spikes in the connection pool that endangered other users when a page was opened containing an image gallery, particularly for a 'job' with a large number of associated images. We added pagination to that page to cap these spikes and keep them below our connection pool thresholds.


Thanks to all those who answer and ask questions online (and at conferences, of course).


Plenty of hub-hub this year about the Oracle ACE program, and the potential for a number of Ace Alumni to appear. It's going through a maturation phase. Let's wait for it to evolve and continue to recognise and potentially aid those people helping the community thrive and develop. Kudos to all those diligent experts out there who remain unrecognised within this particular program, but support their teams and community with their humble expertise.

I'll continue to output stuff that helps me remember stuff and benefits other people, regardless of which side of the line I'll fall. Whatever helps my abstracts get submitted when I occasionally venture out in this giant planet ;p


No real exciting stats to report this year. Growth minimal, but quite the regular heartbeat of visitors. Many of them me, looking up certain references. This is the very reason that tipped me over the edge when starting this blog, seeing this byline:
"Oracle Things I Got to Remember Not to Forget" - Alex
2016 Blog visitors - Lift your game, Greenland

For some reason, my reference to Carsten's LISTAGG function was the most popular this year. Perhaps because more people like us who are using this to generate larger JSON sets prior to 12.2?

Also up there for hits was about one of my favourite APEX development patterns.

It took until #22 in the most visits by page for a 2016 post, an important one on improving PL/SQL performance in APEX. Some of my favourites from this year include decommissioning triggers, and a debugging how-to that may be a useful reference in the forums (#40).

Spiking briefly was this Patterson-Gimlin style glance at APEX 5.2

APEX Sample Applications

If you haven't heard, Dick Dral possibly leads the pack at the moment in regard to sample applications, I need to have more of a play in there. He's been a busy boy!

While reviewing blog page hits I noticed activity this page where I started to catalogue my bookmarked list of community sample applications. Maybe I can finish modernising my sample app and make these more prominent, or find a better home elsewhere.


For anyone who purchased a copy of my jQuery in APEX book, thank you, and I would recommend you re-source chapter 9 electronically. Somehow an early draft made it in but it has since been replaced, using what seems like a logistically amazing process in the world of publishing.

If you like video format, I did this video series for 4.x, but many principles still apply.

While I've got a back-log to read, this Real World SQL and PL/SQL is hard to go past. Well done, gang.


Because I like science and technology.

Among other things, I predict forms of intelligent systems / AI will be a science we'll see more of in the world, solving problems that wouldn't immediately spring to mind. I also wonder if it will help clients become event thinner, so I can stop complaining about not having enough space for apps on my phone, even as a moderate user of apps.


Because I've liked this since I was single digits.

This year will mark the end of an amazing mission that I've been following for quite some time. Cassini has been exploring more than just Saturn as shown in this amazing timeline. It's already committed on its fateful descent into Saturn in September.

Saturn moon Iapetus

I know in next year's review I'll be talking about the amazing James Webb Space Telescope, scheduled for launch around a year after Cassini farewells. This thing is going to be the biggest thing for astronomy since Hubble. Literally. The few weeks between launch and confirmation of a functioning telescope will be the most thrilling moment that represents years of work for thousands of engineers. There's no second chance like Hubble had, nor any service missions, not while still humanity struggles to exceed low earth orbit.

Science, in general, is actually quite a big part of the current news cycles, but for all the wrong reasons. I wonder how this will change if China's Chang'e 5 makes it to the moon and back this year, making it the third country to achieve this amazing feat and the first in 40+ years. I wonder how POTUS will respond?

There is at least one reason to be in the land of the free this year, for on August 21 a total solar eclipse travels across the entire country, a rare event hopefully inspiring many budding young scientists.

Happy science, everyone.

Thursday, 2 February 2017

How did you get into programming in the first place?

If you want a sanity check on programming life, I recommend you follow @ThePracticalDev in some form.

Recently a question was posed regarding we all got into programming in the first place.
There are a few interesting posts, here is my submission.

The Vic-20 was the first introduction, using BASIC pokes recorded on tape, but I think it was seeing the use of a variable in a simple Pascal programme over the shoulder of another student in year 9 that sparked something for my career choice. Ada at university, then PL/SQL with Oracle databases.

I often forget those first years, in between playing some form of space invaders, where I could follow a spiral bound manual to write simple BASIC programs, recording them onto a magnetic tape recorder.

Vic-20 "Datasette"

Insert many years of playing with DOS and autoexec.bat; playing Dune, Civilisation, and any number of cold war games & flight simulators, I found myself in a computing class when I was early teens. In maybe our second workshop I noticed the nerd in front of me doing something interesting with a 'variable'. No doubt my memory has warped this moment over time, but I vividly recall contemplating the creative implications of this temporary memory storage.

This nerd & I became friends, we created a cool little character-based word-sleuth game using Pascal, and I think I was kidding myself thinking of a career in Architecture.

It helped that Ada was the language of choice at the time in university. Look familiar? We finished our project work using Oracle, and I found myself at an Oracle client site instead of building internal systems for Collins class submarines...

Now I blog stuff about a revolutionary front end IDE built on top of amazing database technology.

What will we all be programming in three more decades time?

Monday, 30 January 2017

APEX Survey Results: Addressing Performance

Yep, I'm still doing this. A bunch of questions to come, many worth the visit. Just a few weeks between drinks, so to speak. My annual review is a little late, too. Anyway...

Time for the performance questions in my 2015 survey. A favourite topic of mine, and my boss, Penny Cookson, lives for tuning.

Q9. How do you proactively address performance? (tick all that apply)

Tune SQL - I would be surprised if this wasn't the top result. Plenty of SQL used in APEX applications, and why not tune them?

Limit Interpreted PL/SQL - this is good practice in general, but in the world of APEX, this means moving inline code from application into packages. You can get pretty quick wins doing this with any plugins. Read here for details.

Materialised Views - a database construct that can aggregate complex information at regular intervals, to be queried many times with simpler SQL over fewer rows. I've seen interesting examples that obfuscate layers to external information using pipelined functions. Or you could just use it for your menu.

Care using v() - Particularly important when referencing page items, since this function would execute a query on the session state table. While values such as APP_ID come from a persistent package variable, it's still context switching between SQL and PL/SQL, so it would be even better to use bind variable.
where id = (select v('APP_ID') from dual)
I think any use of v() should be questioned. SQL queries should use bind variables (not substitution strings) and packages should be parameterised.

Page Workflow/Design - why refresh the entire page when a partial refresh would do? A well designed application will limit network traffic and the amount of queries necessary to serve the data. Refreshing regions on demand with dynamic actions is one of the most regular things I do. Declarative page modals in APEX 5.x have also made APEX life a lot more comfortable.

Global Page Modularisation - Performance also about the developer not repeating tasks over and over, causing future maintenance headaches.

JSS/CSS File Management - minifying code can reduce network traffic, as can well designed libraries; CDNs; and declarative options. I understand APEX Front End Boost can help.

Care with jQuery selectors - selectors can be abused just like table indexes. I have some commentary on this here.

Region Caching - possibly underutilised, but the ability to cache region on the global page in APEX 5.0 opens options, as do some new APIs.

data-attributes - jQuery related, what I meant by this was to offer more information during report generation, which can help interactivity and reduce AJAX activity. Though read consistency issues should be considered. See a basic example here.

Other - a few people suggested a well designed data model. Touche. Even more got stuck into it in the next question on performance return on investment.

The User Interface attributes are also an area worth being familiar with, particularly in regard to application level file management.

I don't need to worry about performance - yeah, right.