Occasionally it makes sense to run MLE JavaScript in an ad-hoc fashion. I typically do that to test something before wrapping the JavaScript code into a module. There are many ways to achieve this goal:

• using DBMS_MLE directly
• using APEX’s SQL Workshop
• using Database Actions

This post demonstrates how to use DBMS_MLE. The package is documented in the JavaScript Developer’s Guide. The package reference can be found in PL/SQL Packages and Types Reference.

Prerequisites

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 23c, 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 testing should really be done with MLE modules and environments, or inline call specs. This post covers DBMS_MLE as it ships with Oracle Database 23c, the older syntax (use of the require keyword for example) remains valid for compatibility reasons but shouldn’t be used in new code.

DBMS_MLE does come handy at times if you want to test things, although APEX and Database Actions provide a better experience than the command line. I don’t always have APEX or Database Actions available, so I thought providing a small note to myself might help me and others.

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.

Broadly speaking there are two different scenarios:

1. You want to write some JavaScript code without referring to any module other than those built-in
2. You want to invoke functions exported from a MLE module

Let’s look at these in more detail.

Calling built-in modules and ad-hoc scripting

The global scope contains a number of objects that have been placed there for your convenience. Interacting with the SODA API or database driver for example doesn’t require you to import anything, for example:

set serveroutput on;
declare
    l_ctx           dbms_mle.context_handle_t;
    l_source_code   clob;
begin
    -- Create execution context for MLE execution and provide an environment_
    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'`,
        [],
        {
            outFormat: oracledb.OUT_FORMAT_ARRAY
        }
    );

    const message = result.rows[0][0];

    console.log(message);

~';
    dbms_mle.eval(
        context_handle => l_ctx,
        language_id => 'JAVASCRIPT',
        source => l_source_code,
        source_name => 'example01'
    );

    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 your MLE modules

If you would like to invoke functionality exported by MLE modules stored in your schema, the example requires a little 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 fakerjs, you proceed.

Don’t forget that you need to grab the ESM (ECMAScript) version of the module, for example from cdn.jsdelivr.net. Store the file in a directory object on your database server to which your account has read privileges. In my case the file is stored in a directory name SRC_CODE_DIR:

create mle module fakerjs_module
language javascript
version '8.4.1'
using BFILE(SRC_CODE_DIR, 'faker-8.4.1.js');
/

You need an MLE environment, too:

create mle env fakerjs_env imports (
    'fakerjs' module fakerjs_module
);

Now you’re all set to use fakerjs/faker. As with all ad-hoc JavaScript calling custom MLE modules you have to resort to dynamic imports, just as with node’s REPL server:

> import oracledb from "oracledb";
import oracledb from "oracledb";
^^^^^^

Uncaught:
SyntaxError: Cannot use import statement inside the Node.js REPL, alternatively use 
dynamic import: const { default: oracledb } = await import("oracledb");

The code snippet therefore becomes this:

set serveroutput on;
declare
    l_ctx           dbms_mle.context_handle_t;
    l_source_code   clob;
begin
    -- Create execution context for MLE execution _providing an 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 => 'example02'
    );

    dbms_mle.drop_context(l_ctx);
exception
    when others then
        dbms_mle.drop_context(l_ctx);
        raise;
end;
/

There are a few noteworthy differences:

• you need to declare an inline asynchronous function as a “wrapper” to allow the dynamic import
• you must provide the environment 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 text:

SQL> l
  1  declare
  2  	l_ctx		dbms_mle.context_handle_t;
  3  	l_source_code	clob;
  4  begin
  5	 -- Create execution context for MLE execution _providing an environment_
  6  	l_ctx	 := dbms_mle.create_context(
  7	     environment => 'FAKERJS_ENV'
  8	 );
  9
 10	 -- using q-quotes to avoid problems with unwanted string termination
 11  	l_source_code :=
 12  q'~
 13  (async() => {
 14	 const { faker } = await import ("fakerjs");
 15
 16	 console.log(
 17	     faker.lorem.paragraphs(5)
 18	 )
 19  })();
 20  ~';
 21  	dbms_mle.eval(
 22	     context_handle => l_ctx,
 23	     language_id => 'JAVASCRIPT',
 24	     source => l_source_code,
 25	     source_name => 'example02'
 26	 );
 27
 28	 dbms_mle.drop_context(l_ctx);
 29  exception
 30	 when others then
 31	     dbms_mle.drop_context(l_ctx);
 32	     raise;
 33* end;
SQL> /
Validus concido officiis. Causa artificiose cognatus volutabrum. Aurum depraedor
conscendo arcesso tonsor ustulo stultus demonstro absens.
Tergiversatio civis tendo cavus ubi tubineus amplexus corrumpo. Alius venio
voveo vorax ver cunabula cito suggero. Timidus conqueror autem acies aedificium.
Sodalitas eaque deprimo acer. Decens doloribus verumtamen capio cariosus
despecto abbas ultio curto. Decretum vulariter in.
Viridis bestia pax conor temporibus verumtamen explicabo aegre cum coepi.
Cohaero tener canto coniuratio tantum thema adopto tepesco vereor. Compello
denego tendo.
Absorbeo amor sollers ars aptus eveniet universe. Temporibus complectus expedita
terminatio approbo adstringo crepusculum vociferor incidunt. Summa modi solum
architecto arbor vereor.

PL/SQL procedure successfully completed.

You can take this game up a notch by saving the JavaScript code in a file on the database server, and then use dbms_lob.loadclobfromfile() to load the code as described in the documentation.

That’s it – happy testing!