Occasionally it makes sense to run In-Database JavaScript code in an ad-hoc fashion. You typically do that to test something before wrapping the JavaScript code into a module. There are many ways to achieve this goal:
- using
DBMS_MLEdirectly (this article) - using APEX’s SQL Workshop
- using Database Actions
This post demonstrates how to use DBMS_MLE for ad-hoc JavaScript execution. The package is documented in the JavaScript Developer’s Guide. The package reference can be found at the usual place, the PL/SQL Packages and Types Reference.
A bit of history …
DBMS_MLE has been introduced in Oracle Database 21c for Linux x86-64. Back in the day this package provided the only way to interact with Multilingual Engine (MLE), but it required some back-and-forth with the PL/SQL Layer. You can still use it in Oracle Database 23ai, but nowadays it is better to use the package in frameworks (like APEX does), and/or Read-Eval-Print-Loop (REPL) servers.
Anything more serious than ad-hoc code execution should really be done via MLE modules and environments, or inline JavaScript code. This post covers DBMS_MLE as it ships with Oracle Database 23ai, the older syntax (use of the require keyword for example) remains valid for compatibility reasons but shouldn’t be used anymore.
Writing ad-hoc JavaScript using DBMS_MLE
Before you can use DBMS_MLE you need an additional privilege to those you need anyway: execute dynamic MLE. Without it, you will get runtime errors. If you’re granted db_developer_role you have all that’s required to get started with In-Database JavaScript development.
Broadly speaking there are two different scenarios for testing:
- You want to write some JavaScript code without referring to any module other than those built-in
- You want to invoke functions exported by a MLE module, either your own or a third-party module
Let’s look at these in more detail.
Calling built-in modules for ad-hoc testing
The global scope contains a number of objects that have been placed there for your convenience. Interacting with the database driver or SODA API for example doesn’t require you to import anything. Here is the most basic, simple example how to use DBMS_MLE in SQLcl or even SQL*Plus:
set serveroutput on;
declare
l_ctx dbms_mle.context_handle_t;
l_source_code clob;
begin
-- Create an execution context for MLE execution
l_ctx := dbms_mle.create_context();
-- using q-quotes to avoid problems with unwanted string termination
l_source_code :=
q'~
const result = session.execute(
`select 'hello, world' as message`
);
const message = result.rows[0].MESSAGE;
console.log(message);
~';
dbms_mle.eval(
context_handle => l_ctx,
language_id => 'JAVASCRIPT',
source => l_source_code,
source_name => 'example 01'
);
dbms_mle.drop_context(l_ctx);
exception
when others then
dbms_mle.drop_context(l_ctx);
raise;
end;
/
You should see the message ‘hello world’ printed on the console. The code snippet above assumes that you are familiar with the concept of an execution context, if not, please head over to the security chapter in the JavaScript Developer’s Guide for more information.
Calling functionality exported by MLE modules
If you would like to invoke functionality exported by MLE modules stored in your schema, the example requires a small modification. Let’s assume you want to create @faker-js/faker in your schema as FAKERJS_MODULE. In that case you’d ensure that using the module is compliant with your company’s policy and the project’s license, and only after confirming that it is safe to use @faker-js, and you are in compliance with all rules, you proceed.
Don’t forget that you need to grab the ESM (ECMAScript) version of the module, for example from JSDelivr or your preferred Content Delivery Network (CDN). At the time of writing version 9.9.0 was the most current one. You can download the module using curl for example:
curl -Lo faker.js https://cdn.jsdelivr.net/npm/@faker-js/faker@9.9.0/+esm
SQLcl offers the easiest way to load the module into the database. After connecting to your database schema, load the module as follows:
SQL> mle create-module -filename ./faker.js -version 9.9.0 -module-name fakerjs_module
MLE Module fakerjs_module created
Before you can use it, you need an MLE environment, or else the import statement would fail with an error to an unknown object. Create the environment like so:
create mle env fakerjs_env imports (
'fakerjs' module fakerjs_module
);
Now you’re all set to use @fakerjs/faker in the database. Just as with node’s REPL server you must resort to dynamic imports when accessing other modules dynamically. Here is an example how to generate sentences using the lorem.paragraphs() function:
set serveroutput on;
declare
l_ctx dbms_mle.context_handle_t;
l_source_code clob;
begin
-- Create execution context for MLE execution, this time providing
-- the name of the previously created environment
l_ctx := dbms_mle.create_context(
environment => 'FAKERJS_ENV'
);
-- using q-quotes to avoid problems with unwanted string termination
l_source_code :=
q'~
(async() => {
const { faker } = await import ("fakerjs");
console.log(
faker.lorem.paragraphs(5)
)
})();
~';
dbms_mle.eval(
context_handle => l_ctx,
language_id => 'JAVASCRIPT',
source => l_source_code,
source_name => 'example 02'
);
dbms_mle.drop_context(l_ctx);
exception
when others then
dbms_mle.drop_context(l_ctx);
raise;
end;
/
There are a few noteworthy differences to the previous example:
- You need to declare an IIFE (Immediately Invoked Function Expression) to dynamically (and asynchronously) import the faker module
- You must provide the environment name to
dbms_mle.create_context()or else MLE won’t be able to resolve the import name
When executing the anonymous PL/SQL block you should see some lovely paragraphs that made me chuckle:
SQL> set serveroutput on;
SQL> declare
2 l_ctx dbms_mle.context_handle_t;
3 l_source_code clob;
4 begin
5 -- Create execution context for MLE execution, this time providing
6 -- the name of the previously created environment
7 l_ctx := dbms_mle.create_context(
8 environment => 'FAKERJS_ENV'
9 );
10
11 -- using q-quotes to avoid problems with unwanted string termination
12 l_source_code :=
13 q'~
14 (async() => {
15 const { faker } = await import ("fakerjs");
16
17 console.log(
18 faker.lorem.paragraphs(5)
19 )
20 })();
21 ~';
22 dbms_mle.eval(
23 context_handle => l_ctx,
24 language_id => 'JAVASCRIPT',
25 source => l_source_code,
26 source_name => 'example 02'
27 );
28
29 dbms_mle.drop_context(l_ctx);
30 exception
31 when others then
32 dbms_mle.drop_context(l_ctx);
33 raise;
34 end;
35* /
Atqui dolores somniculosus acies eum curriculum degusto. Defero cohibeo valde voluptatem conculco. Arbustum reiciendis ambulo.
Vos exercitationem adimpleo vitium ver desidero curvo vulpes suffragium. Defendo blanditiis barba tunc. Adulatio soluta corrumpo sint.
Depono quae admoveo. Eveniet careo modi vulgo sollers stipes caveo pax. Thymum sustineo quis aegrotatio calco acer argumentum tactus cognatus.
Terra alter tricesimus derelinquo sufficio. Arguo officia illum uxor temptatio cupressus decumbo desparatus talio testimonium. Tamisium stillicidium conforto victoria assumenda.
Conculco campana verbera amiculum tonsor. Defetiscor tenax thema caste condico delectus blanditiis carmen. Aer tendo arbor aut contabesco ustulo canto paulatim.
PL/SQL procedure successfully completed.
That’s it – happy testing!
Martins Blog 