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 highlighted row (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>.

Edit: If you've ready this post, you should also about async wait by by Vincent.


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. You can explore these with the UT button builder.
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.

Or you could add a declarative true action to hide the triggering element, so the button can't be double clicked by a frustrated user.

Partial Page Refresh

May 2017 - I've added this section in response to a reader comment. I can't believe I neglected this property in the first place.

You'll find a problem if the report is refreshed due to a range of actions such re-sorting, applying filter, or perhaps invoked as a refresh action in yet another dynamic action - the on click dynamic action on our action button no longer works!

This is easily adjusted using the Event Scope property for the on click dynamic action, using the 'Dynamic' option (formerly 'Live').

Set Dynamic Action Event Scope to Dynamic

This maps to a jQuery setting that, as the item help describes:
Binds the event handler to the triggering element(s) for the lifetime of the current page, irrespective of any triggering elements being recreated via Partial Page Refresh (PPR).
The default option is static perhaps as the lowest common denominator, favouring speed. Most reports would have this adjusted to Dynamic.

The second property allows you to define the surrounding container of what's being refreshed, ie, the region. It's my understanding that you would include the following value.

And this would reduce the search area required to find our particular buttons, but I thought that was the purpose of supplying this as a surrounding ID/repeating class combination.

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: The original copy mistakenly had a draft of  Chapter 9 included. If you had an early copy, please be sure to read the revised chapter online.

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 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 from APEX 5.0 (through to at least 18.2), though it looks pretty slick already.

Here is the solution I shared on Slack a few weeks ago, also on Github.
<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.

A setting was introduced in 18.x to hide some of this content, but I'm not sure it works properly.
INTERNAL workspace instance setting
I believe some others have been tackling easy options to replace the icon.

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.