Ad-hoc MLE JavaScript in Oracle Database 23c: DBMS_MLE

February 29, 2024 | 6 minute read
Text Size 100%:

Occasionally it makes sense to run MLE JavaScript 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_MLE directly (this post)
  • 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. Future posts describe the other 2 options.

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. In case you don't have APEX or Database Actions available this post might help you save some time.

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 policies 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 this example 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. Just as with node, you must use a dynamic import. This is what node tells you if you ignored that rule:

> 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 first 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 compared to the first example:

  • 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!

Martin Bach

Martin is a product manager at Oracle helping customers in Europe and around the world address their IT related problems. He is most interested in cloud technology, DevOps and how these can be used best with Oracle technology.


Previous Post

NetSuite as OIDC Provider

Michael Bao | 5 min read

Next Post


NetSuite UI Customization Made Simple: A Decision-Tree Approach

Mohammed Kassem | 7 min read