Tuesday, 19 January 2016

2015 Blog Review

Time for my look back on 2015 and what I might aim for in the coming orbit around the sun. The highlighted word is dedicated to the #flatearth people I've been conversing with on Twitter recently. Wow.

Like many people, part of the reason for doing these sorts of things really what every person should do semi-regularly -> write down your aspirations. I remember being taught goal setting from a young age and I think it's a life skill that many leave behind.

Same topics as last year, with a different order by, but they're all tightly coupled anyway ;p


Ahh, so many things to play with, so little time.


There will be a lot going on for APEX again this year. No doubt you've already read Joel's summary of 2016. Oh to live in the US/Europe (or have fast, cheap, efficient modes of transport).

Content for APEX at Kscope16 looks amazing, it's a shame it's a touch early to see stuff about OracleJET and the Interactive Grid in 5.1. That's OK, I'm still learning the full impact of life in 5.0.

I'll continue to work on Universal Theme projects, which pleases me. Plenty still to learn and absorb, adjusting to our evolving development framework. Thanks again to the development team for the Page Designer, it has vastly improved productivity.

I would like to see Share Components integrated into the Page Designer, certainly for viewing but I'm sure even editing could be cleverly integrated with modal dialog pages. I often operate with two browsers running the builder, Chrome for Page Designer; Firefox for Shared Components.

I'd like to hear more about how the Universal Theme will change, what sort of life cycle it will have, and how information about change is relayed. I know it's changed since inception, and no doubt within patches. I wonder if any tweaks fix the little issues we've been finding. On my list is to copy an app, verify the them subscription, and try it out. Maybe export the themes and do a diff?

Anyway, I've found CSS use has cut greatly since running UT, I've cut plenty from converted applications. I've even modified the balance I'm looking for between Dynamic Actions and jQuery.

I truly could rattle on for ages. My three developer buddies at work will testify to that.


Personally I would  like to see more on ORDS. I feel related documentation is still maturing and there only seem to be a small handful of experts sharing their experiences & techniques.

As someone with a prominent development hat, I'm thankful to have a colleague that understands that middle tier better than I. Unfortunately we're a small team and setting up JMeter for load testing is still on the list.

I saw a bit of chatter online regarding mixed content issues on version 3.0.3, and performance issues after 3.0.1, both of which we experienced empirically. The ORDS team must feel what Android software authors face were there's a zillion combinations of infrastructure.

Even though ORDS is sprouting it's own wings, I think the APEX and ORDS team need to stay tightly coupled. At this point I'd guess most ORDS deployments at least serve APEX. I've already overheard the observation that most people see it as a just that, and clients don't split hairs when something goes wrong.

"APEX is down", and nothing worse than middle tier experts saying "not us" when it's certainly outside the database layer. We can't wreck this together unless the arm supporting ORDS gets more support.

That's my plea for the (fantastic) Oracle developer advocate group in 2016 - more aid for ORDS. Maybe a hashtag would help? ;p #ORDShelp


Our current client upgraded to 12c over the festive period, so no doubt I'll have a few development experiences to share with this newer environment. Probably on topics related to JSON and SQL syntax. Look out for #db12c

Text Editor

For a long time I've used Textpad for my text editing needs, but I'm tinkering on the edge of leaping to either Sublime or Atom. This needs to happen this year. Eddie, get me on it!


Last year I said to Juergen that I'd help out with apex.world. So far I've written the intro post for Slack, but I still haven't honoured the other task he sent my way. I could blame the book, tendinitis, the holiday period, but I know there's procrastination in there, too. Sorry mate, soon.

I doubt I'll get to any international conferences this year, but I'm going to start paying more attention to the Asia-Pacific region. I'm on the lookout for communities I'm yet to discover. That sounds like a tautology, something self evident, but let's call it a known unknown.

I'll certainly hang around on Twitter and Slack, and I might get back onto the PL/SQL Challenge. I gave it a break for a while, but I need some 12c tips. I asked Tom Connor a question recently, I might spend some time perusing the headline questions, too.


I intend to spend a bit of time finishing all sorts of semi-drafted posts, technical and otherwise. I'm not longer writing a book and this yearn to write has to go somewhere. I still have plenty of notes from my experience at Kscope15, which was amazing. I look forward to the time where I can travel more.

I have fun when analysing the statistics from the previous year. I think many of us unknowingly love assessing numbers and statistics, which is my hypothesis as to why cricket and baseball are so popular.

Readership growth has tapered, which I find interesting. Does that mean I've hit most people out there, looking for stuff, or have I just filled up my current channels?

I must say it was fascinating at Kscope to hear about people who've read my blog or heard I was there and sought me out. Rambling on the keyboard to a faceless audience is weird sometimes, so I was thankful for the positive feedback. People compliment on my writing style, and I humbly accept and strive to improve.

Back to the stats, the most telling of which is browser usage

  • Chrome up 16%
  • Firefox down 11%
  • IE down 23%
  • Safari up 27%
  • Opera down 18%

Big differences, no real surprises, is there?

Mostly read on desktop, but mobile up 64%, but that's relative, not absolute. 3k mobile readers vs 53k on the desktop. Similar relative growth for social media vs organic searches. Twitter certainly is a growing factor in disseminating (and finding) technical content.

Plenty of interest in APEX 5, 1500 visits alone from Patrick's meta post aggregating APEX 5 articles

I find this curious every time but only two articles from 2015 made it into top 10 most visited. One was Sample community applications, which makes me want to progress this idea further with the gang at apex.world. Second was my reference to Carsten's listagg clob.

To add perspective, the reigning top page represents 4.7% of visits. If I interpret this correctly, the top 10 pages represent over a quarter of the year's page visits, which is quite the heuristic.

Strangest search term?
apex 3.x dynamic actions


My first book is complete. What a journey that was, with Kscope and APEX 5 thrown in between. A little shame about chapter 9, more on that to come, likely in some form of errata. I'll hopefully learn from that lesson.

Anyone who has read it, thank you and I hope I can get the updated chapter to you soon. I hope any reviewers hold out until they've had a chance to read through ;p

I bought a few myself with the Cyber Monday sales that I need to start reading. A have a couple on node.js but I look forward to learning heaps in the revised Expert Oracle APEX.

I need to fit a novel or two in as well. We're have a short holiday in Bali soon, I no doubt have a good sci-fi ready to go.


As I suggested in Monty's post about my APEX game, I've considered recording little videos showing I build the game, in addition to the presentation I did for the local user group. Nothing fancy, certainly not like Connor and Tim's videos, just little tutorials that watch me work. Even little things like rebuilding my sample application in the Universal Theme.

I've also got a lot of video watching I want to do. I have a few set to go from ODTUG already, such as webinars I've missed because they've been on at 1am. I've barely seen many of Connor & Tim's yet, I just don't watch many videos, I prefer to read my content. So I've got to schedule some video watching time, certainly before Kscope16 videos flood in. They're my best alternative to being there, that and participating in the Twitter stream during the conference.


As with any year, 2016 has plenty of interesting science to look out for, and I love it. I will continue to also tweet science stuff I find interesting, in addition to the usual #orclapex #db12c #jQuery hashtags.

I've been planning to post a list of my favourite podcasts for a while. For those interested I have plenty to recommend. They make great listening during transit to work. Exercise that brain.


Having a toddler has introduced a lot of viruses and some bacterial infections to the house. This made exercise a little difficult, but I have managed to swim regularly with a small group of guys at work. We do a steady 1km at lunch, aiming for once twice a week.

That's awesome, but I have equipment at home I need to use more often in the morning, particularly with the amount of chocolate I eat, which has to come down!

But first, I have to wait for what's apparently tendinitis in my right hand to calm down. I've done a decent job at resting it, but maybe that leaves room to help improve mental health.

Take a walk, enjoy the sunset. Rest your eyes and let your brain do some awesome stuff in the background while you're admiring the world.

On that note, enjoy your fresh start!

Sunday, 20 December 2015

Calling PL/SQL from JavaScript in APEX

When I first wrote Chapter 9 of my book, Choosing Processing Options, I knew I still had a lot of research and experimentation to do, particularly regarding the async parameter. Below is a summary of the outcome of this work and drove a full revision of my original chapter.

Last week I received a printed copy of my book and noticed that unfortunately the first submission of chapter 9 made it into print. Apress are looking into this for me, but please consider the concepts in this post refactored into what you read. It all relates to the following message you may have noticed in your browser console.

The following post is slightly modified from an email conversation considering workflow from a browser based system. There was a more detailed discussion in a forum post that I'd need to dig up.

Imagine a button on a screen to add a record that may already exist. How do you interact with the user?

On 8 October 2015 at 09:05, Scott Wesley wrote:

Hi gang,

I've played with this, thought about it, and I think we're already on a good wicket with another pattern.

Option A)

On click btn ->
PL/SQL: try insert, set result to page item (eg: P0_SIGNAL)
Dynamic action on P0_SIGNAL ->
Notification: Display message based on item.

We need to shake it up a little to provide different colour message - success/failure etc.

Tom Petrus suggested a more elegant alternative (with one less step and/or didn't involve a page item?), but it was in transit time at kscope and it was a hard topic to explore without code. In revising this post, I think perhaps he set the message to some JavaScript container, which in turn is sent to the database using APP_AJAX_X01.

Option B)

On click -> test javascript condition, which actually calls a synchronous function to test existence in database
True action - PL/SQL to insert
False action - alert user

This is what we were attempting this afternoon, and sounded good in my head but there are a few things wrong.

  1. We're calling the db twice, when we just need to do it once. We're in an optimistic world now: try it, work it out once it's done.
  2. The web isn't built for it, and the specifications are making it harder for us to do so. A common sentiment on from MDN.

Below are the examples we attempted for the JavaScript to return true or false, hence triggering the appropriate true/false dynamic actions. Function b() represents better practice. CB_AJAX is an AJAX callback (PL/SQL) defined on the page and returns a number as a text string.

This works, is completely synchronous, but will only work in browsers for a finite time thanks to the bit in red (async:false).
function a() {
  ,{pageItems : '#P9_EMPNO'}
   ,success:function(pData) {
       console.log('Result a:'+pData);
       console.log('success:'+ (result.length));
       result = pData;
return result.length == 2;
The next example uses 'deferred objects', which is jQuery's updated method for handling "success", but we can't access the callback values outside that scope, so we can't return true or false. The 'callback' is the done() section.

"javascript return result from callback" is an obscenely googled phrase, with a fair response of: "This is impossible as you cannot use an asynchronous call inside a synchronous method."
If that hurts your head a little, welcome to JavaScript mechanics.
function b() {
  ("CB_AJAX" // this is just => htp.p('X');
  ,{pageItems : '#P9_EMPNO'}
).done(function(pData) {
   console.log('Result b:'+pData);
   result = pData;
   console.log('success:'+ (result.length));
   return result.length == 2;
// anything here will run before plsql finished
Eddie suggested using "promises", which sounded promising. ha.
But they are just the native implementation of deferred objects. And extremely new, unsupported by even IE11.
I also think this quote is telling: "This is extremely useful for async success/failure, because you're less interested in the exact time something became available, and more interested in reacting to the outcome."
function c() {
  return new Promise(function(resolve, reject) {
    var jqxhr = apex.server.process
 ("CB_AJAX" // this is just => htp.p('X');
 ,{pageItems : '#P9_EMPNO'}

c().then(function(result) {
  // code depending on result
  return result.length == 2;
}).catch(function() {
  // an error occurred
Then I tried letting functions accept callbacks, which really defines all logical sense I learnt in programming 101. If this doesn't screw your brain, google "doSynchronousLoop.js". I'm sure it's 21st century spaghetti code, with the added dimension of time.
function myCallback(pData) {
   console.log('Result b:'+pData);
   result = pData;
   console.log('success:'+ (result.length));
   return result.length == 2;
function d(callback) { // d
  ("CB_AJAX" // this is just => htp.p('X');
  ,{pageItems : '#P9_EMPNO'}
// anything here will run before plsql finished
So I think the conclusion to be drawn out of all this is that Tom Kyte is right, and always has been. Run code, if it raises an exception, let out propagate, politely let the user know; otherwise success and move on.

Anything else and you are going against the grain.

APEX provides the ability to do this declaratively with dynamic actions, stick with option A, and we'll improve the mechanism in future to incorporate a library to set/read P0_SIGNAL with a JSON string that will get converted into signal type/message and invoke Alertify as appropriate, as opposed to setting it with a single value as currently done in <redacted>.


Monday, 14 December 2015

Tutorial: Include action button in report

In reality, this 'add' button could represent any action you would like in a report that would execute PL/SQL upon press of a row level button.

In this example I click on a button in a report to add the row to collection, without submitting the page.

Prepare page

Add Static id to your report region:

Add hidden page item: P2_ADD
Set protected = No if you need to submit the page for other processes.

Create before header process to initialise collection

Add link column

Define a new column in your report that will serve as the link button, it only returns null. For classic reports you could create a virtual column for the purposes of a link.

My report on emp uses this SQL
select e.*, null add_btn from emp e

Modify the new column and set the column type to 'Link'.

Set URL to: javascript:void(0);
Link text: Add
Link Attributes: data-id="#EMPNO#" class="add t-Button t-Button--warning t-Button--simple t-Button--stretch"

The data tag creates an attribute that we can interrogate easily with jQuery, returning the ID of the record.
The classes represent the same classes that would be applied when choosing relevant template options. The 'add' class is added for our dynamic action.

Create dynamic action

Create a new dynamic action that will respond to button press, using on click of a jQuery selector.

Use the following selector to identify add button clicks on your report.
#p2_my_report .add

Add an action to execute the following JavaScript. It sets the page item with the value of the ID set in the data tag.
$s('P2_ADD', $(this.triggeringElement).data('id'));

Don't forget to set this to not Fire on Page Load. this.triggeringElement represents the button pressed, which is generated as the following HTML.
<a href="javascript:void(0);" 
   class="add t-Button t-Button--warning t-Button--simple t-Button--stretch">Add</a>

Other JavaScript options

If other information was required, you could define more data tags, or traverse the DOM to find other values in the row. For IR you would need to first define static ID for the column as SAL. Classic reports automatically use the column alias.

If you were defining a remove function, then a second statement could be added to immediately hide the row from view without needing to refresh the report by locating the surrounding tr tag.
Though this may make pagination feel a little strange, as the number of rows displayed won't add up.

Execute the PL/SQL

You could then define a second action that executes PL/SQL, including P2_ADD in 'Page Items to Submit', so you can then refer to :P2_ADD as a bind variable in the PL/SQL. Note, you should always explicitly convert any value from session state that is not a string.

Alternatively, you could define an onChange event on P2_ADD which does the same thing. This would allow different UI on the page to invoke the same action.

The onChange dynamic action should only execute when the item is not null, and an action after the PL/SQL should clear the item. This allows the same value to be selected successively, otherwise the value wouldn't 'change' the second time around.
In the screenshot & example below I also refresh the region containing the collection.


So that describes a pattern I use frequently, and some variation of which is asked on the forums all the time. I plan to extend this example to include the collection report as a modal dialog with the ability to add & remove.

Run the demo to see it in action.

If you want to explore this further, you might like my book on jQuery with APEX. </shameless-plug>

Wednesday, 9 December 2015

Book: Pro jQuery in Oracle APEX

The word on the street is my book is now available.

Update: Chapter 9 is an old revision, the publisher is chasing this up for me. This chapter had a major upgrade, so stay tuned.

If you like using dynamic actions in APEX and want to learn how to use jQuery selectors effectively, this book is for you.

If you want more flexibility with your dynamic actions, and understand how handy this.triggeringElement can be, give it a read.

If you want some more ideas on how to improve the way your users interactive with your data, check it out.

Practical techniques to enhance your APEX user interface, written for APEX 5.0.

Complete acknowledgements in the book, but thank you to Alex Fatkulin for being the technical reviewer. Any mistakes you might find are on me, some chapters got some major revisions after Alex's eyes cast through them. In part thanks to the APEX 5.0 release, in part to me continuing to learn better ways to write jQuery.

Roel has already spotted a mistake, thanks mate ;p
I missed the second X when referring to built in substitution string APP_AJAX_X01.

I feel like I'm standing on the shoulders of some giants who have paved the way before me. Without the help of the broader community on the OTN forums, StackOverflow, Slack, Twitter, conferences and people sitting right next to me, such an accomplishment would not be possible. Three in particular have been vital in my learning of jQuery, thanks Tom PetrusJari Laine, and Jeff Eberhard.

I've reviewed a bunch of books (and things) in the past, too, if you're looking for more book selections. I bought a few in the Cyber Monday sales, including the revised, multi-author gold nugget that is Expert Oracle APEX, plus a couple on node.js.

I welcome you all to review mine on Amazon or your blog once you've had a read. Hit me on Twitter or Slack if you have any questions or commentary. I welcome your input. It was quite the experience writing this and I have all the hesitations I'm sure many before me have faced regarding it's reception. It was a rather lone project, but one undertaken with pride and enthusiasm.

I'll write a few posts over the next few weeks that might whet your appetite for some jQuery action. And no, I wouldn't rule out writing another one day!


Tuesday, 8 December 2015

Kscope ODTUG Interview

The ODTUG booth at Kscope15 had a permanent spot for interviews, and they can all be found on the ODTUG channel.

Martin interviewed me, and here it is, with embarrassing front still and all.

If you want some more Australian accent, and the sound of a local magpie, listen to Connor's little video regarding his move to Oracle. Though he's done quite a few more now since I drafted this post.

I have an idea or two for some videos from myself, finally. All just a matter of time.

Thursday, 3 December 2015

Customising APEX 5.0 workspace login

A few years ago Peter Raganitsch showed us how to customise the workspace login page in APEX 4.x.

I think it's even easier in APEX 5.0, though it looks pretty slick already.

Here is the solution I shared on Slack a few weeks ago.
<script type="text/javascript">
$( document ).ready(function() {
  $('.a-Login-title').text("Scott's workspace").css('color','darkgreen');

 // In order of reference
 // Oracle header
 // Down arrow
 // Second page fluff
 $('.a-Login-message, .a-Header--login, .a-Login-slideNav, .a-Login-slide--secondary').css('display','none');
 // Orange message bar
 $('.a-Login-message').text('Reminder: use your windows credentials');

 // Hide reset password

 // Change logo, list in /i/apex_ui/css/Core.css


Paste this in the same location in the INTERNAL workspace, under Manage Instance -> Login message.

And voila!
Customised Workspace Login
Perhaps use this to indicate environment details.

I believe some others have been tackling easy options to replace the icon. I'll share how I made the login page to my Boggex game at some point.

George Hrab @TEDx Rethinking Doubt

I realise the Internet is a big place, so if you haven't heard of the TED talks then I recommend you head over and check it out.

They're limited length talks about amazing things people are doing, discovering, and innovating around the world. Ideas worth spreading, is the catch-phrase.

Many talks are about topics that are really beyond our personal purview, but today I listened to one that I think everybody should listen to, especially teenagers. The speaker, George Hrab, addresses something that everyone can apply, every day.

Thanks to one of my heroes, Phil Plait, for pointing this one out. Though considering I periodically listen to George's podcast, I would have heard his golden voice at TEDx at some point. He's also a drummer in a funk band and has released some cool science based music.