codemonth.dk

One project every month - making stuff better ...

PLSQL promises vs dbms_scheduler chains

10 lines of code vs 80 lines of code! When implementing JS promises in PLSQL, one of the main use cases is the ability that promises gives, to easily chain together dependent steps defined as functions. Also the ability to easily pass output from one step along to the next step in the chain.

Adding support for Edition Based Redefinition for PROMISES_NINJA

As part of another project, where I need to support the Edition Based Redefinition feature, I needed to add support inside the promise class for the dbms_scheduler job_class parameter. The job_class parameter can point to a service, and a service can be assigned a default edition to run in. This allows me to execute code inside a promise, and automatically support any edition that has been created in the database, as well as assigned to a service.

plsql promises - promise complete solution

Finally. I got the 2 last methods completed. promise.all and promise.race is finished. So now the plsql promises library is feature complete. promise.all method takes a list of promises, built with the help of the promises_ninja package, and once the entire list of promises is fulfilled the top promise is set to fulfilled. Here is an example:

-- Test all method
declare
test_promise promise := promise();
a_promise1 promise;
a_promise2 promise;
a_promise3 promise;
all_promises promises_ninja.promise_list_type;
begin
  a_promise1 := promise('p_multiplier', 10);
  promises_ninja.build_promise_list(all_promises, a_promise1);
  a_promise2 := promise('p_multiplier', 20);
  promises_ninja.build_promise_list(all_promises, a_promise2);
  a_promise3 := promise('p_multiplier', 30);
  promises_ninja.build_promise_list(all_promises, a_promise3);
  test_promise.all_p(promises_ninja.convert_promise_list(all_promises));
  dbms_lock.sleep(5);
  test_promise.check_and_set_value();
  dbms_output.put_line('State is: ' || test_promise.state);
end;
/

The output is the list of the promises with the fulfilled values. So you can loop through the output and display individual values like this:

declare
test_promise promise := promise();
a_promise1 promise;
a_promise2 promise;
a_promise3 promise;
all_promises promises_ninja.promise_list_type;
promise_list_o promises_list_obj;
promise_val promise;
begin
  a_promise1 := promise('p_multiplier', 10);
  promises_ninja.build_promise_list(all_promises, a_promise1);
  a_promise2 := promise('p_multiplier', 20);
  promises_ninja.build_promise_list(all_promises, a_promise2);
  a_promise3 := promise('p_multiplier', 30);
  promises_ninja.build_promise_list(all_promises, a_promise3);
  test_promise.all_p(promises_ninja.convert_promise_list(all_promises));
  dbms_lock.sleep(5);
  test_promise.check_and_set_value();
  dbms_output.put_line('State is: ' || test_promise.state);
  if test_promise.val.getObject(promise_list_o) = dbms_types.success then
    -- We converted the object successfully
    for i in 1..promise_list_o.promise_list.count loop
      if promise_list_o.promise_list(i).getObject(promise_val) = dbms_types.success then
        -- We have an individual promise output value
        dbms_output.put_line('Value of promise is: ' || promise_val.getanyvalue);
      end if;
    end loop;
  end if;
end;
/

Same code and technique can be used for the race method. The only difference is that the output in the race call is a normal promise and not a list of promises, as only the first promise to complete is returned.

plsql promises - chaining and thenables

So last blog entry Do or do not. on the promises library, covered the basics of what promises can give us.

This entry will cover more around chaining of promises and how you can easily run your code in parallel and still make sure they run in the right order, when needed.

Promises by definition has a method called [then]. What this method does, is that it will take the value of the promise once it is fulfilled, and use that as input to a new function defined by us. The input to then is, either a function to run when fulfilled or a function to run if rejected or both. Although not in the standard there are more and more libraries implementing a method called [done]. It has the same input options as the [then] method but it does not return a new promise. So only use this method when you do not care about the result of the chain call. Since we are in Oracle I cannot name the method [then] since it is a reserved word, so I had to add an _f to the name, so it becomes [then_f].

So here is the first example. We create a promise based on the function p_multiplier, that takes a number and multiplies it by 2. We then say that if this promise is fulfilled we want to execute the function called p_multi_convert. This function takes the previous promise result, and will return a text that will tell us if the output is less or more than 50.


declare
test_promise promise;
begin
test_promise := promise('p_multiplier', 42);
test_promise := test_promise.then_f('p_multi_convert');
promises_ninja.promise_wait(test_promise);
dbms_output.put_line('Value of test_promise2 is: ' || test_promise.getanyvalue);
end;
/

Do, or do not. There is no "try".

About 7 months ago, I was reading an article about some of the new features that were in javascript/ecma/coffeescript or whatever it is called now. One of the things that stuck out, was promises. I liked the idea of "asynchronous" execution with a possible chained dependency, that you could just define and run, and then carry on with other tasks without having to wait for the result. What you do instead, is you receive a "promise" of the execution. A promise that at some point will contain the result from your call.

The Oracle database has the capability to do this already in dbms_scheduler using chains, but it is not dynamic and the complete flow has to be defined up front.

So I decided to try and see if this could be done in plsql somehow ... And then I got occupied with something else. Instead I picked up the idea over christmas, and decided to give it a go. What I have today is a 60% implementation, that should get to 100% over the next 2 weeks, but the stuff that is here now, is functional and you can do some fun pieces of code with it

Let me just give you a couple of samples of what PROMISES_NINJA is capable of: