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 I would like to encourage you to read his blog post to see how this feature works. There is a little bit more to it than covered by Peter, which is why I decided to write this short post.
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. I covered that topic extensively on this blog, feel free to browse the list of articles for something that tickles your fancy.
The great folks over at the ORDS team have made it really easy to use MLE modules with ORDS handlers. I don’t want to cover the basics, Peter did that perfectly on his blog so please head over if you have any questions this post does not answer.
As always, here’s a list of components used to put this article together:
- Oracle Database 23ai Free (23.4.0.24.05) on Oracle Linux 9.4 (x86-64) [download link]
- ORDS 24.2.0 [download link]
Let me see some code
This example assumes that the account you use has been granted all the necessary privileges to create and execute JavaScript code. It is also assumed that you REST-enabled that schema.
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
);
> 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 to production.
With the MLE module and environment in place it’s time to create the ORDS module, handler, and template. I went with a GET request which requires encoding the question, but it should be sufficient to bring the point back home.
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 code with 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 I don’t have to check for the question element in the uri_parameter object I do it anyway to protect against potential future 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. The new feature is documented in the ORDS Developer’s Guide, which contains a handy reference. Happy coding!
Martins Blog 