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() {
console.log('a');
apex.server.process
  ("CB_AJAX"
  ,{pageItems : '#P9_EMPNO'}
  ,{dataType:"text"
   ,async:false
   ,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() {
console.log('b');
apex.server.process
  ("CB_AJAX" // this is just => htp.p('X');
  ,{pageItems : '#P9_EMPNO'}
  ,{dataType:"text"}
).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() {
  console.log('c');
  return new Promise(function(resolve, reject) {
    var jqxhr = apex.server.process
 ("CB_AJAX" // this is just => htp.p('X');
 ,{pageItems : '#P9_EMPNO'}
 ,{dataType:"text"}
).done(resolve);
  });
}

c().then(function(result) {
  // code depending on result
  console.log('.then');
  console.log(result);
  return result.length == 2;
}).catch(function() {
  // an error occurred
  console.log('error');
});

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
console.log('d');
apex.server.process
  ("CB_AJAX" // this is just => htp.p('X');
  ,{pageItems : '#P9_EMPNO'}
  ,{dataType:"text"}
).done(callback);
// anything here will run before plsql finished
}
d(myCallback);

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.

References

• http://stackoverflow.com/questions/6847697/how-to-return-value-from-an-asynchronous-callback-function
• http://www.html5rocks.com/en/tutorials/es6/promises/
• http://caniuse.com/#search=promises