Database Multilingual Engine (MLE) hands-on

Preamble

Oracle Database Multilingual Engine (MLE), the first time I have heard about this 21c new feature is tweets from enthusiastic developer advocates. MLE allows you to execute Javascript code directly inside the database. Like you would execute Javascript on server side with Node.js versus the Javascript that is executed on client i.e. your internet preferred browser. The feature is baked by GraalVM.

Oracle server side programming has a long history of available languages and the obvious de facto one is PL/SQL. When I have started to work on Oracle more than 25 years I have also written some code in Pro*C/C++ where you have a precompiler and finally a C/C++ compiler to create OS executable. Pro*Cobol is also available but I feel it has less been used…

Server side programming can also be a Java program that will be executed on your database server. Java has all the tools and method to access an Oracle database easily. This could also be (not tested) a CREATE OR REPLACE JAVA [CLASS | SOURCE | RESOURCE] with Java code directly embedded inside the database. The natural way of using Java with an Oracle database will normally be an application written in java running on an application server and accessing remotely the database through JDBC or OCI.

Never (yet) tested but you can also create functions in PL/SQL that will directly call a C or a Java program:

CREATE FUNCTION function01 RETURN VARCHAR2 AS [LANGUAGE JAVA NAME '...' | LANGUAGE C NAME '...'];

Testing has been done using Oracle database Enterprise Edition 21c (21.7) running on a 12 cores and 64GB Red Hat Enterprise Linux Server release 7.9 (Maipo) server.

Even if tweets are optimistic about the feature I personally have difficulties to see the real added value… One argument I tend to agree with is the fact that Javascript being a very popular language you can find thousand of cool libraries around that could be used with no delay directly onto your database figures (or not).

Multilingual Engine (MLE) testing

To access somme dummy table I have created this test table with top 5 polluters (source):

CREATE TABLE fossil_carbon_dioxide_emissions
(
    id NUMBER,
    country VARCHAR2(30),
    YEAR NUMBER,
    total_mt NUMBER,
    CONSTRAINT fossil_carbon_dioxide_emissions_pk PRIMARY KEY (id) enable 
);
 
INSERT INTO fossil_carbon_dioxide_emissions VALUES(1,'China',2017, 10877.218);
INSERT INTO fossil_carbon_dioxide_emissions VALUES(2,'United States',2017, 5107.393);
INSERT INTO fossil_carbon_dioxide_emissions VALUES(3,'India',2017, 2454.774);
INSERT INTO fossil_carbon_dioxide_emissions VALUES(4,'Russia',2017, 1764.866);
INSERT INTO fossil_carbon_dioxide_emissions VALUES(5,'Japan',2017, 1320.776);
COMMIT;

I have granted those two privileges to my personal account:

SQL> GRANT EXECUTE DYNAMIC MLE TO yjaquier;
 
GRANT succeeded.
 
SQL> GRANT EXECUTE ON JAVASCRIPT TO yjaquier;
 
GRANT succeeded.

I firstly wanted to see how many languages are supported as I had the feeling that the door is open to other languages (might be in future):

SQL> SET serveroutput ON
SQL>
DECLARE
  lang_list dbms_mle.languages_t;
  lang dbms_mle.language_t;
  i NUMBER;
BEGIN
  lang_list:=dbms_mle.get_available_languages();
  FOR i IN 1..lang_list.COUNT() LOOP
  lang:=lang_list(i);
  dbms_output.put_line(lang);
  END LOOP;
END;
/
JAVASCRIPT
 
PL/SQL PROCEDURE successfully completed.

Okay so far only javascript is supported… If Oracle has first chosen Javascript it is probably because the language is the most popular one and the Stackoverflow survey has ranked it number one language for the ninth year in a row.

The first script is connecting onto the database (mle-js-oracledb) and accessing/displaying my dummy figures:

SET serveroutput ON
DECLARE
-- Create execution context for MLE execution
ctx dbms_mle.context_handle_t := dbms_mle.create_context();
user_code clob := q'~
  const oracledb = require("mle-js-oracledb");
  const sql = "select country, total_mt from fossil_carbon_dioxide_emissions order by total_mt desc"
  // execute query
  const result = oracledb.defaultConnection().execute(sql);
  //console.log(JSON.stringify(result.rows));
  for(let i=0;i < result.rows.length; i++) {
    console.log('Country '+result.rows[i][0]+' has emitted '+result.rows[i][1]+' Mt OF CO2 IN 2017');
    }
~';
BEGIN
-- Evaluate the source code snippet in the execution context
dbms_mle.eval(ctx, 'JAVASCRIPT', user_code);
-- Drop the execution context once no longer required
dbms_mle.drop_context(ctx);
END;
/
 
Country China has emitted 10877.218 Mt OF CO2 IN 2017
Country United States has emitted 5107.393 Mt OF CO2 IN 2017
Country India has emitted 2454.7740000000003 Mt OF CO2 IN 2017
Country Russia has emitted 1764.866 Mt OF CO2 IN 2017
Country Japan has emitted 1320.776 Mt OF CO2 IN 2017
 
PL/SQL PROCEDURE successfully completed.

I’m hitting the number weird behavior that can be anyway easily corrected…

The second script is still connecting to the database but I take two arguments (year and country) and export a sentence containing the expected result (mle-js-bindings). This could also be encapsulated in a PL/SQL procedure or function:

SQL> SET serveroutput ON
SQL>
DECLARE
  ctx dbms_mle.context_handle_t;
  source clob;
  output VARCHAR2(100);
BEGIN
  ctx := dbms_mle.create_context();
  -- Export value from PL/SQL
  dbms_mle.export_to_mle(ctx, 'country', 'United States');
  dbms_mle.export_to_mle(ctx, 'year', 2017);
  source := q'~
    const oracledb = require("mle-js-oracledb");
    let bindings = require("mle-js-bindings");
    // Import value previously exported from PL/SQL
    let country = bindings.importValue("country");
    let year = bindings.importValue("year");
    const sql = "select total_mt from fossil_carbon_dioxide_emissions where country='"+country+"' and year="+year;
    const result = oracledb.defaultConnection().execute(sql);
    let output = "Country "+country+" has emitted "+ result.rows[0]+" Mt of CO2 in "+year;
    // Export value to PL/SQL
    bindings.exportValue("output", output);
  ~';
  dbms_mle.eval(ctx, 'JAVASCRIPT', source);
  -- Import value previously exported from MLE
  dbms_mle.import_from_mle(ctx, 'output', output);
  dbms_output.put_line(output);
  dbms_mle.drop_context(ctx);
END;
/
Country United States has emitted 5107.393 Mt OF CO2 IN 2017
 
PL/SQL PROCEDURE successfully completed.

Multilingual Engine (MLE) advanced testing

I wanted to build a more advanced test case by directly building a pie chart representing my dummy figures. I was about to use Apache ECharts to generate this pie chart but at the time of writing this post nothing is available to import an external library into your code like you would do with NPM.

Some people have succeeded to do it either by copy/pasting the entire Javascript code inside the MLE code but first it is really cumbersome and second in term of library upgrade and/or benefiting from a CDN it is not robust enough.

I have seen another method that consist of copy/pasting the Javascript library code into a table having a CLOB column and then using this table to import the module.

But again really not mature enough and I’m really expecting the DBMS_MLE package to provide a method to import directly and simply any Javascript library we like.

I might come back to this feature if things improved in future…

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

IT World