DBA Hub

📋Steps in this guide1/9

Multilingual Engine (MLE) for JavaScript in Oracle Database 23ai/26ai

Oracle 23ai/26ai extends the multilingual engine (MLE) with the addition of persistent MLE modules and call specs that allow modules to be called from SQL and PL/SQL.

oracle 23configurationintermediate
by OracleDba
37 views
1

Setup

We create a new test user. To create MLE modules and the PL/SQL call specs we need the following two grants. If we want the ability to execute JavaScript, we must grant the following to our test user. From 23.9 onward this is no longer necessary, as a user can execute any MLE code they own. If we want to run dynamic JavaScript using the package, we will need the following grant also. We'll add as a catch-all. We check the multilingual engine is enabled for our database. Setting it to will disable it at the PDB or CDB level respectively. The allowable values are currently , and , with being the default. Connect to the test user. One of the examples needs access to the following table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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;

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

grant execute on javascript to testuser1;

grant execute dynamic mle to testuser1;

grant db_developer_role to testuser1;

SQL> show parameter mle_prog_languages

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mle_prog_languages                   string      all
SQL>

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

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;
2

Create MLE Modules

We create a JavaScript module using the command. In this example we create a simple module to add two numbers together. We create a call spec to allow the JavaScript module to be called from SQL and PL/SQL. We can now test it using a call from SQL or PL/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 to write output. Alternatively, we could have exported the function like this. We can use the same call spec, because only is public. We turn on so we can see the message from . The view displays the modules in our schema. The view allows us to display the contents of the module. We can load modules from files on the host file system. We create a directory object pointing to a directory holding our JavaScript module. We have a file called "/tmp/math_mod2.js" with the following contents. We create the module with a reference to the source file. The source is loaded into the dictionary as a one-off operation. If the source file changes, it has to be loaded again manually. Once loaded, it can be used in the same way shown before. We can also assign a version to a module using the optional keyword.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
create or replace mle module math_mod language javascript as

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

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

select add_numbers_fnc(1, 3);

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

SQL>

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);
}
/

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 }
/

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

set serveroutput on
select add_numbers_fnc(1, 3);

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

Doing some work
SQL>

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>

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>

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

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 }

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

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>

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>

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>
3

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. The and views display information about our environment. We create a new module, importing one of the modules from our environment. We create a call spec and execute the module.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
drop mle env if exists math_env;

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

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>

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);
}
/

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>
4

Drop MLE Modules

We use the command to drop modules. Remember to drop any call specs also.

Code/Command (click line numbers to comment):

1
2
3
4
5
drop mle module if exists math_mod;
drop mle module if exists math_mod2;

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

Inline MLE Call Specifications

An inline MLE call specification allows the JavaScript to be included in the call specification directly. For complex code is makes sense to stick with modules, but for simple JavaScript routines an inline MLE call specification may be more appropriate. In the following example we recreate the previous example as an inline MLE call specification. Notice the JavaScript is enclosed by "{{ }}" and the reference to the parameters are in uppercase. From 23.9 onward we get compile time syntax checks for inline MLE call specifications. In this example we purposely add a typo into the JavaScript. To remove this we only need to drop the call specification.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
create or replace function add_numbers_fnc (
  num1 number,
  num2 number)
  return number
as mle language javascript
{{
  return(NUM1 + NUM2);
}};
/


select add_numbers_fnc(1, 3);

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

SQL>

create or replace function add_numbers_fnc (
  num1 number,
  num2 number)
  return number
as mle language javascript
{{
  banana return(NUM1 + NUM2);
}};
/

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION ADD_NUMBERS_FNC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      SyntaxError: ADD_NUMBERS_FNC:2:9 Expected ; but found return
         banana return(NUM1 + NUM2);
         ^

SQL>

drop function add_numbers_fnc;
6

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 table based on the supplied value. We create a call spec for the module and execute the module.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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 };
/

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

Dynamic MLE Execution (DBMS_MLE)

In Oracle 21c dynamic MLE execution using the package was the main way to execute JavaScript in the database. With the inclusion of MLE modules in Oracle 23ai/26ai, it is likely to be used far less frequently. We can think of the package as the JavaScript equivalent of the package used for dynamic SQL. Here is a simple example of using the package to execute some JavaScript to add two numbers together. The code block is executed as an asynchronous anonymous function. Alternatively we can split function and invocation.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
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>

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;
/
8

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 focusing 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. This article has only skimmed the surface of using the Multilingual Engine for JavaScript. There is much more information in the documentation.
9

Updates

The Multilingual Engine (MLE) is evolving rapidly, with release updates (RUs) sometimes adding new functionality. List of changes for each RU are listed here . - 23.8 : The PURE keyword forces restricted execution, so the resulting MLE environments or call specifications cannot access database state. - 23.9 : EXECUTE ON JAVASCRIPT privilege requirement removed. A user can execute all MLE code owned by itself, so this extra grant it unnecessary. - 23.9 : Compile-time syntax checks for inline JavaScript functions. Keep referring back to the documentation to stay up to date. For more information see: - Introduction to Oracle Database Multilingual Engine for JavaScript - DBMS_MLE Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!