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
Posts
