My colleague Peter O’Brien previously pointed out that Oracle REST Data Service (ORDS) support Multilingual Engine (MLE)/JavaScript handlers from Release 24.1.1 onward. Which is great, and you should read his blog post to see how this feature works. This article elaborates a little and it documents an additional use case.

What about more complex JavaScript code?

One of the main advantages of MLE/JavaScript in Oracle Database 23ai as compared to Oracle Database 21c when MLE was introduced is the ability to create/use JavaScript modules. These can either be your own, or third party modules. This topic has been covered extensively on this blog. The great folks over at the ORDS team have made it really easy to use MLE modules with ORDS handlers.

As always, here’s a list of components used to put this article together:

As with everything in IT, details might change, so please check the documentation if you found this post via a web-search.

Let me see some code

This example assumes that the account you use has been granted all the necessary privileges (documented in the JavaScript Developer’s Guide, chapter 9) to create and execute JavaScript code. It is also assumed that you REST-enabled that schema. The code shown in this blog post is owned by EMILY.

The JavaScript portion is kept to its bare minimum:

create or replace mle module demo_module
language javascript as

/**
 * The minimum viable MLE example to use for a demonstration
 * @params {string} question - the question to which you seek an answer
 * @returns {string} the answer
 */
export function answer(question) {
    return `the answer to ${question} is 42`;
}
/

-- The MLE environment is required later or else MLE would not know
-- how to map an import name to a MLE module
create or replace mle env demo_env
imports (
    'jsdemo' module demo_module
);

After submitting it against the database you create the ORDS portion next.

WARNING: the following ORDS module is NOT protected using OAUTH2 or equivalent technology to keep the example simple. You never, ever publish REST-endpoints without proper authentication/authorisation and logging/auditing etc. to production.

With the MLE module and environment in place it’s time to create the ORDS module, handler, and template. To stay in line with Peter’s example a GET request hander is created. This requires encoding the question, but it should be sufficient to bring the point back home. It’s also a little less typing compared to submitting a POST request.

declare
    c_module_name   constant varchar2(255) := 'mle_ords_handler';
    c_pattern       constant varchar2(255) := 'answer/:question';
begin
    ords.define_module(
        p_module_name    => c_module_name,
        p_base_path      => '/demojs/',
        p_status         => 'PUBLISHED',
        p_items_per_page => 25,
        p_comments       => 'ORDS handling MLE/JavaScript modules and environments'
    );

    ords.define_template(
        p_module_name    => c_module_name,
        p_pattern        => c_pattern,
        p_priority       => 0,
        p_etag_type      => 'HASH',
        p_etag_query     => null,
        p_comments       => 'the question to which you need an answer'
    );

    ords.define_handler(
        p_module_name    => c_module_name,
        p_pattern        => c_pattern,
        p_method         => 'GET',
        p_source_type    => 'mle/javascript',
        p_mle_env_name   => 'DEMO_ENV',
        p_items_per_page => 0,
        p_mimes_allowed  => null,
        p_comments       => null,
        p_source         => q'~
(req, resp) => {

    // import question() from demo_module by means of the module's import name
    const { answer } = await import ('jsdemo');

    // not necessary since you cannot call this particular handler without a
    // question but it's always good to test for unexpected behaviour
    if (req.uri_parameters.question === undefined) {
        resp.status(400);
    } else {

        const data = {
            q: req.uri_parameters.question,
            a: answer(req.uri_parameters.question)
        };

        resp.content_type('application/json');
        resp.json(data);
    }
}    
    ~'
    );

    commit;

end;
/

The definition of module, template, and handler are closely modeled after Peter’s original example. The main difference to his code is the use of p_mle_env_name (line 27) which is required for any module that isn’t built into MLE to be resolvable. Just as with all examples using DBMS_MLE (directly, or under the covers) you have to go with dynamic JavaScript imports which is what you see in line 34. Although strictly speaking you don’t need to check for the question element in the uri_parameter object doing so anyway is a good way to to protect against potential future code refactoring.

Let’s try it out!

Testing

Let’s throw a question at the ORDS module:

$ curl -s https://localhost:8443/ords/freepdb1/emily/demojs/answer/life%2C%20the%20universe%2C%20and%20everything | jq
{
  "q": "life, the universe, and everything",
  "a": "the answer to life, the universe, and everything is 42"
}

There you go! In addition to using all the modules built into MLE you can also refer to your own. Happy coding!