Executing JavaScript from PL/SQL in Oracle Database 21c with Multi Language Engine

Oracle Database Release 7 – end of 1993 – introduced PL/SQL as language for Stored Procedures, Functions and Triggers. This was just before I joined Oracle Corporation, in May 1994. I have made extensive use of PL/SQL. With Oracle 8i we saw the advent of the Embedded JVM that made it possible to run Java code (from) within the database. I have never been a big fan of stored Java procedures. The somewhat clunky integration was a large part of that. It seemed that apart for some very specific use cases, Stored Java had not much to offer. With the Oracle Advanced Analytics Option – now a no-extra-cost option with every database edition – came Embedded R; support for the statistical R language included inside the Oracle Database platform.

And now we are in a new era. With Oracle Database 21c we see the formal introduction of a new concept: the Multi Language Engine (MLE) that allows us to run code snippets in additional languages besides PL/SQL. At present, only JavaScript is support by MLE. However, because MLE is powered by GraalVM that has growing support for (making) various languages (interoperate) I am confident that before too long we will be able execute Python, Ruby, R as well as JVM languages such as Java, Kotlin and Groovy from PL/SQL – and perhaps create stand alone stored procedures and functions written in these languages as well.

Using JavaScript from PL/SQL

At this point, I will show you some examples of what JavaScript integration from PL/SQL looks like. And I intend to show you how while we may not be able to create “real” Stored JavaScript procedures, we can do something quite similar.

Here a simple example where a JavaScript snippet is constructed as string in PL/SQL then send to the MLE Context where it is evaluated as JAVASCRIPT – the only language currently supported in MLE. The result from executing this snippet is similar to executing dbms_output.put_line – in a quite resource expensive way.

declare
  ctx dbms_mle.context_handle_t;
begin
  ctx := dbms_mle.create_context(); -- Create execution context for MLE execution
  dbms_mle.eval(ctx, 'JAVASCRIPT', 'console.log(`Hello from JavaScript`)'); -- Evaluate the source code snippet in the execution context
  dbms_mle.drop_context(ctx); -- Drop the execution context once no longer required; note: as long as the context is retained, it holds values of global objects including functions
end;

This example shows how we can send a piece of code in JavaScript and have it executed. It is not very sophisticated – as it basically a static string that is dynamically evaluated. And no results can be passed back from the JavaScript context – except to the standard output.

You will be glad to know that in JavaScript we can access the database – tables and views – through regular SQL statements, as we will see in a moment.

Now let’s first look at how we can transfer data – from PL/SQL to the world of JavaScript and vice versa. In the next snippet, we use the MLE Context. This context is the interface between the multiple language contexts – currently just two: PL/SQL and JavaScript. From every language we can put stuff in the MLE context and from every language we can retrieve values from the context.

Here is an example of using the MLE context to pass a person (‘s name) from PL/SQL – the first green arrow represents the storing in the context of this value. The second arrow indicates the line where JavaScript retrieves the value from the MLE context. The third arrow shows how JavaScript puts a result – called greeting – in the MLE context and the fourth arrow depicts how the PL/SQL code acquires this result to do useful things with. Note on line 9 we need to require the module mle-js-bindings into the JavaScript snippet in order to use the bindings object that allows the interaction with the MLE context.

declare
  ctx dbms_mle.context_handle_t;
  source clob;
  greeting varchar2(2000);
begin
  ctx := dbms_mle.create_context(); -- Create execution context for MLE execution
  dbms_mle.export_to_mle(ctx, 'person', 'John'); -- Export value from PL/SQL
  source := q'~
    const bindings = require("mle-js-bindings"); // in order to work with MLE context
    const person = bindings.importValue("person"); // Import value previously exported from PL/SQL; this is how PL/SQL and JavaScript interact - via the MLE context - not directly through parameters and return values
    const greeting = `Hello, ${person} `;
    bindings.exportValue("greeting", greeting); // Export value to PL/SQL - the MLE equivalent of the function return statement 
  ~';
  dbms_mle.eval(ctx, 'JAVASCRIPT', source); -- Evaluate the source code snippet in the execution context
  dbms_mle.import_from_mle(ctx, 'greeting', greeting); -- Import value previously exported from MLE - the handshake to complete the "return result to caller"
  dbms_output.put_line('Greetings from MLE: ' || greeting);
  dbms_mle.drop_context(ctx); -- Drop the execution context once no longer required; as long as the context is retained, it holds values of global objects including functions
end;

The values passed through the MLE context to JavaScript can be more complex than a simple string. Most Oracle Database types are supported – including timestamp, interval, raw, blob and clob. By passing a string representing a JSON document, deeply nested data structures and easily be passed. And of course, JavaScript is very good at working with JSON. In fact, it may be tempting to defer all JSON processing to JavaScript rather than the elaborate manipulation that PL/SQL supports.

Here is an example of passing a moderately interesting JSON variable to the MLE context and using it “on the other side” in JavaScript:

declare
  ctx dbms_mle.context_handle_t;
  source clob;
  greeting varchar2(2000);
begin
  ctx := dbms_mle.create_context(); -- Create execution context for MLE execution
  dbms_mle.export_to_mle(ctx, 'person', '{"firstName":"John", "lastName":"Doe", "address":{"streetname":"Penn Avenue", "houseNumber":20}, "pets":["rabbit","canary","leopard"]}'); -- Export value from PL/SQL
  source := q'~
    const bindings = require("mle-js-bindings"); // in order to work with MLE context
    const person = JSON.parse ( bindings.importValue("person")); // Import value previously exported from PL/SQL; this is how PL/SQL and JavaScript interact - via the MLE context - not directly through parameters and return values
    const greeting = `Hello, ${person.firstName} with ${person.pets.length} pets `;
    bindings.exportValue("greeting", greeting); // Export value to PL/SQL - the MLE equivalent of the function return statement 
  ~';
  dbms_mle.eval(ctx, 'JAVASCRIPT', source); -- Evaluate the source code snippet in the execution context
  dbms_mle.import_from_mle(ctx, 'greeting', greeting); -- Import value previously exported from MLE - the handshake to complete the "return result to caller"
  dbms_output.put_line('Greetings from MLE: ' || greeting);
  dbms_mle.drop_context(ctx); -- Drop the execution context once no longer required; as long as the context is retained, it holds values of global objects including functions
end;

The string passed from PL/SQL is valid JSON. The value imported inside JavaScript is fed into JSON.parse in order to turn it into a JavaScript object. When the greeting is composed in line 11, we can access the various nested properties of the person object.

Creating Stored JavaScript procedures

Well, at present we cannot create stored JavaScript procedures.

What we can do: store our JavaScript “procedure” in a CLOB column in a table MY_JS_PROCEDURES and create a fairly generic piece of PL/SQL that can be invoked with the name of JS_PROCEDURE to be invoked that will subsequently execute that JavaScript. The transfer of the input to the MLE Context and the retrieval of the output could even be included in this generic PL/SQL procedure.

The simple approach is shown here, to provide some inspiration. As a sufficiently privileged user I create the table MY_JS_PROCEDURES and the procedure INVOKE_JS_PROCEDURE. Not just anyone can create JS procedures in this table and not everyone is allowed to invoke these procedures:

create table MY_JS_PROCEDURES 
( procedure_name varchar2(100)
, procedure_body clob
);

insert into MY_JS_PROCEDURES
( procedure_name, procedure_body)
values 
( 'greeting', 'console.log(`Hello World!`)')

create or replace procedure
invoke_js_procedure
( p_procedure_name in varchar2
, p_context in out dbms_mle.context_handle_t )
is
  l_procedure_body clob;
  l_context dbms_mle.context_handle_t ;
begin
  select procedure_body
  into   l_procedure_body
  from   MY_JS_PROCEDURES
  where  procedure_name = p_procedure_name;
  l_context := p_context;
  if l_context is null
  then
    l_context := dbms_mle.create_context(); 
  end if;
  dbms_mle.eval(l_context, 'JAVASCRIPT', l_procedure_body); 
  p_context := l_context;
end; 

create public synonym invoke_js_procedure for invoke_js_procedure

grant execute on invoke_js_procedure to dev

declare
  ctx dbms_mle.context_handle_t;
begin  
  invoke_js_procedure('greeting', ctx);
end;

Initially, just the procedure greeting is created. This procedure can be invoked by user dev like this:

I leave it to the reader to create more interesting JS Procedures.

Accessing the Database from JavaScript

One useful capability available to us in the JavaScript code we will have MLE execute for this is interaction with the database in which the the code is running. MLE provides a predefined Database Connection and a subset of the Node Database library for performing database actions. From Alina’s article: “The mle-js-oracledb API closely follows the regular client-side Oracle Database driver for node.js.”.

Here is about the most straightforward SQL statement against the database (select systimestamp from dual):

declare
  ctx dbms_mle.context_handle_t;
  source clob;
begin
  ctx := dbms_mle.create_context(); -- Create execution context for MLE execution
  source := q'~
     const oracledb = require("mle-js-oracledb"); // the Oracle Database driver
     const sql = "SELECT to_char(systimestamp, 'DD-MM-YYYY HH24:MI') as ts FROM dual";
     const result = oracledb.defaultConnection().execute(sql);
     console.log(JSON.stringify(result.rows));
  ~';
  dbms_mle.eval(ctx, 'JAVASCRIPT', source); -- Evaluate the source code snippet in the execution context
  dbms_mle.drop_context(ctx); -- Drop the execution context once no longer required; as long as the context is retained, it holds values of global objects including functions
end;

Of course we can make things much more interesting and complex. For example: from PL/SQL we execute JavaScript that uses the default Database Connection to execute PL/SQL (to write a string to the output). I believe that the PL/SQL executed in the MLE JavaScript context could itself set up an MLE JavaScript engine. But now we are beyond what could conceivably be useful.

Using Existing NPM Modules

I read this article by Stefan Dobre – especially the section titled JavaScript Modules & npm–  where he describes how we can integrate npm modules in our database applications. He shows for example how the npm modules qrcode-generator, validator.js, and marked.js. are downloaded from CDN URLs and stored in a table. When a snippet of custom JavaScript is executed that requires one of these modules, the module source is read from this table and dynamically evaluated. The result is that every object exported by the module becomes available in the current JavaScript context.

I have to assume that these modules as loaded from the CDN URLs are fully selfcontained – meaning they have no additional modules they themselves depend on.

Here is Stefan’s code for activating the modules in the current context. Part of the magic is the module decoration :

Stefan’s code on GitHub : https://gist.github.com/stefandobre/5b7bd0843f09de533af31a6db908620d/raw/11b29ae023960b617f888ba298b931a1e58e9fcf/apex-mle-require-module.js 

Note: we can take most complex collections of JavaScript modules and custom code and turn them into a single package that we can evaluate in the MLE context – by using webpack or other build tools. You can read my article on GraalVM to learn more details on how to use npx , webpack and webpack-cli  and how to create a single JavaScript bundle.

Resources

Multilingual Engine: Executing JavaScript in Oracle Database by Alina Yurenko, Alexander Ulrich, Lucas Braun, Hugo Guiroux, and Stefan Dobre.

Oracle Database 21c Documentation on DBMS_MLE: https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/dbms_mle.html#GUID-0A1A89ED-BF4B-4338-88B3-D7E3CD73AC6F

MLE and the Future of Server-Side Programming in Oracle APEX  by Salim Hlayel https://blogs.oracle.com/apex/mle-and-the-future-of-server-side-programming-in-oracle-apex

JavaScript as a Server-Side Language in Oracle APEX 20.2 by Stefan Dobre – https://medium.com/graalvm/javascript-as-a-server-side-language-in-oracle-apex-20-2-457e073ca4ca