codemonth.dk

One project every month - making stuff better ...

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:

The first thing we need to do, is to define our promise. For that we use the promise object that is created when you install the library.

declare
my_promise promise;
begin
null;
end;
/

Next we need to initialize it. We can initialize it in a couple of ways. You can initialize as an empty promise. This promise will not do anything, and there will be no value at any point assigned to the promise

declare
my_promise promise;
begin
my_promise := promise();
end;
/

But if we want something useful out of our promise, we want to initialize with a definition of what we want executed. This can be done in different ways:

declare
my_promise promise;
begin
-- Initialize and call function "my_function" with no input arguments.
my_promise := promise('my_function');
-- Initialize and call function "my_function" with a number input argument.
my_promise := promise('my_function', 42);
-- Initialize and call function "my_function" with a varchar2 input argument.
my_promise := promise('my_function', 'hello world');
end;
/

What "my_promise" is now, is an object, with a placeholder for the value that will be returned at some point when "my_function" is complete. We do not know when it will be finished, or if it will succeed, but what ever happens, that promise will have the value or the error at some point. To access a potential value, you can use the different getvalue_[datatype] functions. If you know the datatype that is returned you can get it directly, if not you can extract the core value, which is of the sys.anydata datatype. If the promise has not yet been fulfilled or rejected, any attempt to get a value will return null.

declare
my_promise promise;
my_promise_value sys.anydata;
begin
my_promise := promise('my_function', 42);
-- pretend to wait for the promise to complete
dbms_lock.sleep(5);
-- Get the value if number
dbms_output.put_line('Value is: ' || my_promise.getvalue_number);
-- Get the value if varchar2
dbms_output.put_line('Value is: ' || my_promise.getvalue_varchar);
-- Get the core value from the object
my_promise_value := my_promise.val;
-- Get any value, and automatically convert to varchar2
dbms_output.put_line('Value is: ' || my_promise.getanyvalue);
end;
/

You can also query the state attribute of the promise object to get the current status, which can be any of the following three values

  • pending - The promise is either waiting or running and has no value
  • fulfilled - The promise is complete and value is in the val attribute
  • rejected - The promise failed and the error reason is in the val attribute

One of the things that we do not have to handle when you work with promises are exceptions. The title of my blog post is both a reference to of course yoda, but also if you look at all my code, there is no exception handling. That is because that is handled automatically by the promise implementation. Whatever exception that happens in your promise call, will "bubble" up to the promise value and you can deal with it. This gives us a clear separation of business logic and the technical implementation, and makes our top code more easy to read and understand.

Another big part of the promise implementation, is the fact that you can chain together promises, and run them based on a previous promise result. That is what the promise implementation can do with the "then" implementation. The "then" method takes as input, a promise and either a "on success" function or a "on reject" function as inputs, and will call them with the value returned from the precious promise.

So imagine our "my_function" returns a number, and we want to do something with that number once we have it using another function called "my_other_function". We can then do the following:

declare
my_promise promise;
my_next_promise promise;
begin
my_promise := promise('my_function', 42);
my_promise.then_p(my_next_promise, 'my_other_function');
end;
/

Once my_promise is in "fulfilled" state, it will execute the "my_other_function" with the output value from my_promise as the input argument. We can then wait for my_next_promise to be in "fulfilled" state, and then access the result in the my_next_promise.val attribute.

The "then" method can be chained as many times as wanted, and if any step fails the exception will once again "bubble" up to the top, and we can handle it.

My next blog post will show how to use both on_success and on_reject functions and show some more advanced uses of how you can chain together promises.

For now you can get the code on my github page but I will also make a NPG package available soon, that will make installation a lot easier.

Tagged in : Advanced Queuing, DBMS_SCHEDULER, Object types, PL/SQL, sys.anydata