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() {
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


7 comments:

Anonymous said...

Hi Scott,
All your investigations in honour!
However I absolutely do think that things like this indeed have to be an integrated part of the developement environment. It's elementary. And so I think it's an elementary task of the dev team to prepare the tool to get it into a state ready for use.
It doesn't make sence to let lots of people fiddle around to find a more or less good/practical solution. Exacly such things is oil to the fire of people who criticize that Apex is (at least halfway) a fiddler's tool. No one of us want to put oil into the fire.
I really hope 5.1 will make also a step forward regarding this issue too.
Well, they still have time until end of april...
Andre

Juergen Schuster said...

Hi Andre,

never heard that APEX is a fiddler's tool. On the contrary 95% you get decleratively. The good part is, that the other 5% - no matter how crazy the requirements are - can be achieved too, because APEX doesn't restrict you on the back end or front end side. The problem posted here is pure JavaScript. I understand only 80%, but it's good to know that I can ask Scott for the other 20% :-)

Juergen

David Lawton said...
This comment has been removed by the author.
David Lawton said...
This comment has been removed by the author.
David Lawton said...

I used calls to apex.server.process fairly often from dynamic actions. Alerts to the user are issued directly from a promise .done() handler rather than by defining another true action. I find that this approach works well.

Stew said...

Pardon my ignorance on this topic, but would you mind fleshing this technique out a bit? For example, I don't see anything in the 2 Javascript scripts that push a value to the P0_SIGNAL item. Something closer to a recipe would be incredibly helpful for those of us who don't get the thrill of working with Apex daily.

Thanks,

Stew

Scott Wesley said...

Stew, I've got a few more posts in mind that explore this further, just a matter of time.