Friday, September 8, 2023

Multilingual Engine (MLE) for JavaScript in Oracle Database 23c

Multilingual Engine (MLE), Oracle JavaScript in Oracle Database 23c

The multilingual engine (MLE) for JavaScript was first introduced as a beta option for Oracle 18c. It was published in 21c, with the ability to execute ad-hoc JavaScript using the DBMS_MLE package. Oracle 23c extends the multilingual engine with the addition of persistent MLE modules and call specs that allow modules to be called from SQL and PL/SQL.

This article intentionally keeps the JavaScript really simple, so it can demonstrate the basic mechanism of creating and calling MLE modules in the Oracle database. Teaching JavaScript is beyond the scope of this article.

Setup


We create a new test user.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

drop user if exists testuser1 cascade;

create user testuser1 identified by testuser1 quota unlimited on users;
grant create session to testuser1;

To create MLE modules and the PL/SQL call specs we need the following two grants.

grant create mle to testuser1;
grant create procedure to testuser1;

If we want the ability to execute JavaScript, we must grant the following to our test user.

grant execute on javascript to testuser1;

If we want to run dynamic JavaScript using the DBMS_MLE package, we will need the following grant also.

grant execute dynamic mle to testuser1;

We'll add DB_DEVELOPER_ROLE as a catch-all.

grant db_developer_role to testuser1;

We check the multilingual engine is enabled for our database. Setting it to DISABLED will disable it at the PDB or CDB level respectively.

SQL> show parameter multilingual_engine

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
multilingual_engine                  string      enable
SQL>

Connect to the test user.

conn testuser1/testuser1@//localhost:1521/freepdb1

One of the examples needs access to the following table.

drop table if exists t1 purge;

create table t1 (
  id           number generated always as identity primary key,
  description  varchar2(20)
);

insert into t1 (description) values ('one'), ('two'), ('three');
commit;

Create MLE Modules


We create a JavaScript module using the CREATE MLE MODULE command. In this example we create a simple module to add two numbers together.

create or replace mle module math_mod language javascript as

export function add_numbers(num1, num2) {
  return(num1 + num2);
}
/

We create a call spec to allow the JavaScript module to be called from SQL and PL/SQL.

create or replace function add_numbers_fnc (
  num1 number,
  num2 number)
  return number
as mle module math_mod
signature 'add_numbers(number, number)';
/

We can now test it using a call from SQL or PL/SQL.

select add_numbers_fnc(1, 3);

ADD_NUMBERS_FNC(1,3)
--------------------
                   4

SQL>

In this example we create a module containing two functions. Notice only one of them is exported, so we have one public and one private function. We've used console.log to write output.

create or replace mle module math_mod language javascript as

export function add_numbers(num1, num2) {
  return do_the_work(num1, num2);
}

function do_the_work(num1, num2) {
  console.log('Doing some work');
  return(num1 + num2);
}
/

Alternatively, we could have exported the function like this.

create or replace mle module math_mod language javascript as

function add_numbers(num1, num2) {
  return do_the_work(num1, num2);
}

function do_the_work(num1, num2) {
  console.log('Doing some work');
  return(num1 + num2);
}

export { add_numbers }
/

We can use the same call spec, because only add_numbers is public.

create or replace function add_numbers_fnc (
  num1 number,
  num2 number)
  return number
as mle module math_mod
signature 'add_numbers(number, number)';
/

We turn on SERVEROUTPUT so we can see the message from console.log.

set serveroutput on
select add_numbers_fnc(1, 3);

ADD_NUMBERS_FNC(1,3)
--------------------
                   4

Doing some work
SQL>

The USER_MLE_MODULES view displays the modules in our schema.

column module_name format a20
column language_name forma a20

select module_name, language_name
from   user_mle_modules;

MODULE_NAME          LANGUAGE_NAME
-------------------- --------------------
MATH_MOD             JAVASCRIPT

SQL>

The USER_SOURCE view allows us to display the contents of the module.

column text forma a50

select line, text
from   user_source
where  type = 'MLE MODULE'
and    name = 'MATH_MOD'
order by line;

      LINE TEXT
---------- --------------------------------------------------
         1 function add_numbers(num1, num2) {
         2   return do_the_work(num1, num2);
         3 }
         4
         5 function do_the_work(num1, num2) {
         6   console.log('Doing some work');
         7   return(num1 + num2);
         8 }
         9
        10 export { add_numbers }

10 rows selected.

SQL>

We can load modules from files on the host file system. We create a directory object pointing to a directory holding our JavaScript module.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

create or replace directory tmp_dir as '/tmp';
grant read on directory tmp_dir to testuser1;

conn testuser1/testuser1@//localhost:1521/freepdb1

We have a file called "/tmp/math_mod2.js" with the following contents.

function add_numbers(num1, num2) {
  return do_the_work(num1, num2);
}

function do_the_work(num1, num2) {
  console.log('Doing some work');
  return(num1 + num2);
}

export { add_numbers }

We create the module with a BFILE reference to the source file.

create or replace mle module math_mod2
language javascript using bfile(TMP_DIR,'math_mod2.js');
/

The source is loaded into the dictionary as a one-off operation. If the source file changes, it has to be loaded again manually.

column text forma a50

select line, text
from   user_source
where  type = 'MLE MODULE'
and    name = 'MATH_MOD2'
order by line;

      LINE TEXT
---------- --------------------------------------------------
         1 function add_numbers(num1, num2) {
         2   return do_the_work(num1, num2);
         3 }
         4
         5 function do_the_work(num1, num2) {
         6   console.log('Doing some work');
         7   return(num1 + num2);
         8 }
         9
        10 export { add_numbers }

10 rows selected.

SQL>

Once loaded, it can be used in the same way shown before.

create or replace function add_numbers_fnc2 (
  num1 number,
  num2 number)
  return number
as mle module math_mod2
signature 'add_numbers(number, number)';
/

set serveroutput on
select add_numbers_fnc2(1, 3);

ADD_NUMBERS_FNC2(1,3)
---------------------
                    4

Doing some work
SQL>

We can also assign a version to a module using the optional VERSION keyword.

create or replace mle module math_mod
language javascript
version '1.0'
as

export function add_numbers(num1, num2) {
  return(num1 + num2);
}
/

select version
from   user_mle_modules
where  module_name = 'MATH_MOD';

VERSION
--------------------------------------------------------------------------------
1.0

SQL>

MLE Environments


We are able to reuse modules by importing them into a new module. To do this we need to create an MLE environment.

We create a new environment importing the two modules we created previously.

drop mle env if exists math_env;

create mle env math_env imports ('MATH_MOD' module MATH_MOD, 'MATH_MOD2' module MATH_MOD2);

The USER_MLE_ENVS and USER_MLE_ENV_IMPORTS views display information about our environment.

select env_name
from   user_mle_envs;

ENV_NAME
--------------------------------------------------------------------------------
MATH_ENV

SQL>

column env_name format a10
column import_name format a12
column module_owner format a12
column module_name format a12

select env_name,
       import_name,
       module_owner,
       module_name
from   user_mle_env_imports;

ENV_NAME   IMPORT_NAME  MODULE_OWNER MODULE_NAME
---------- ------------ ------------ ------------
MATH_ENV   MATH_MOD     TESTUSER1    MATH_MOD
MATH_ENV   MATH_MOD2    TESTUSER1    MATH_MOD2

SQL>

We create a new module, importing one of the modules from our environment.

create or replace mle module imp_math_mod language javascript as

import * as mm from "MATH_MOD"

export function add_numbers(num1, num2) {
  return mm.add_numbers(num1, num2);
}
/

We create a call spec and execute the module.

create or replace function imp_add_numbers_fnc (
  num1 number,
  num2 number)
  return number
as mle module imp_math_mod
env math_env
signature 'add_numbers(number, number)';
/

set serveroutput on
select imp_add_numbers_fnc(1, 3);

IMP_ADD_NUMBERS_FNC(1,3)
------------------------
                       4

SQL>

Drop MLE Modules


We use the DROP MLE MODULE command to drop modules.

drop mle module if exists math_mod;
drop mle module if exists math_mod2;

Remember to drop any call specs also.

drop function if exists add_numbers_fnc;
drop function if exists add_numbers_fnc2;

MLE JavaScript SQL Driver


The MLE JavaScript SQL Driver allows JavaScript modules to interact with the database. As you can imagine, there is a lot of documentation related to this functionality (here), but we'll just give a simple example.

In this example we query some data from the T1 table based on the supplied ID value.

create or replace mle module sql_mod_1 language javascript as

function queryT1(id) {
  if (id === undefined) {
    throw "Parameter ID is mandatory.";
  }

  try {
    const result = session.execute(
      `SELECT id, description FROM t1 WHERE id = :id`,
      [ id ],
      { outFormat: oracledb.OUT_FORMAT_OBJECT }
    );

    if (result.rows.length > 0) {
      for (let row of result.rows) {
        console.log(`The query found a row : id=${row.ID} description=${row.DESCRIPTION}`);
      }
    } else {
      console.log(`No data found.`);
    }

  } catch (err) {
    console.error(`Error: ${err.message}`);
  }
}

export { queryT1 };
/

We create a call spec for the module and execute the module.

create or replace procedure queryT1 (
  id number)
as mle module sql_mod_1 
signature 'queryT1(number)';
/

set serveroutput on
exec queryt1(2);

The query found a row : id=2 description=two

PL/SQL procedure successfully completed.

SQL>

Dynamic MLE Execution (DBMS_MLE)


In Oracle 21c dynamic MLE execution using the DBMS_MLE package was the main way to execute JavaScript in the database. With the inclusion of MLE modules in Oracle 23c, it is likely to be used far less frequently. We can think of the DBMS_MLE package as the JavaScript equivalent of the DBMS_SQL package used for dynamic SQL.

Here is a simple example of using the DBMS_MLE package to execute some JavaScript to add two numbers together.

set serveroutput on;
declare
  l_ctx     dbms_mle.context_handle_t;
  l_source  clob;
  l_num1    number := 1;
  l_num2    number := 3;
  l_output  varchar2(100);
begin
  l_ctx := dbms_mle.create_context();

  dbms_mle.export_to_mle(l_ctx, 'num1', l_num1);
  dbms_mle.export_to_mle(l_ctx, 'num2', l_num2);

  l_source := q'~
    (async () => {
       const bindings = await import("mle-js-bindings");
       const num1 = bindings.importValue("num1");
       const num2 = bindings.importValue("num2");
       const output = num1 + "+" + num2 + "=" + (num1+num2);
       bindings.exportValue("output", output);
     }
    )();
  ~';

  dbms_mle.eval(l_ctx, 'JAVASCRIPT', l_source);
  dbms_mle.import_from_mle(l_ctx, 'output', l_output);
  dbms_output.put_line(l_output);
  dbms_mle.drop_context(l_ctx);
end;
/
1+3=4

PL/SQL procedure successfully completed.

SQL>

The code block is executed as an asynchronous anonymous function. Alternatively we can split function and invocation.

set serveroutput on;
declare
  l_ctx     dbms_mle.context_handle_t;
  l_source  clob;
  l_num1    number := 1;
  l_num2    number := 3;
  l_output  varchar2(100);
begin
  l_ctx := dbms_mle.create_context();

  dbms_mle.export_to_mle(l_ctx, 'num1', l_num1);
  dbms_mle.export_to_mle(l_ctx, 'num2', l_num2);

  l_source := q'~
    async function dbms_mle_example() {
      const bindings = await import("mle-js-bindings");
      const num1 = bindings.importValue("num1");
      const num2 = bindings.importValue("num2");
      const output = num1 + "+" + num2 + "=" + (num1+num2);
      bindings.exportValue("output", output);
    }

    dbms_mle_example();
  ~';

  dbms_mle.eval(l_ctx, 'JAVASCRIPT', l_source);
  dbms_mle.import_from_mle(l_ctx, 'output', l_output);
  dbms_output.put_line(l_output);
  dbms_mle.drop_context(l_ctx);
end;
/

Thoughts


The Multilingual Engine (MLE) is all about choice. Most long term users of Oracle will be comfortable with SQL and PL/SQL, and probably won't consider using JavaScript in the database on a regular basis. The way those same users didn't use Java in the database when it was introduced in Oracle 8i.

If a new user comes to Oracle with existing JavaScript skills, they can choose to use those skills rather than focussing on PL/SQL. There may also be some tasks that are easier to achieve using existing JavaScript modules.

So JavaScript is not a replacement for PL/SQL. It is simply another option for developers.

Source: oracle-base.com

Related Posts

0 comments:

Post a Comment