UPDATE: This code sample works in Database 23c too. I will soon be posting an update on some additional enhancements introduced in Database 23c.
Did you know that you can run JavaScript code from right inside the Oracle Database? Well you can with a new PL/SQL package called: DBMS_MLE. MLE in this instance is short for Multilingual Engine.
Here’s a quick primer to get you started…
As with all first time programs our first code snippet is Hello World…
DECLARE
ctx varchar2(50) ;
BEGIN
ctx := DBMS_MLE.create_context();
DBMS_MLE.eval(ctx, 'JAVASCRIPT', q'~console.log("Hello, World!");~');
DBMS_MLE.drop_context(ctx);
END;
/
What do you think? Pretty cool huh?
So let’s take a look at what we just ran. The first step we performed here was to create a “context handle” that we called “ctx”. The context provides a runtime space for our JavaScript to run in our session. The context is for this code snippet alone and is unique for this session so our runtime is completely isolated from other JavaScript runtimes even within the same database session.
The next step is to actually run the JavaScript snippet using the DBMS_MLE.eval() procedure. To run this we provided:
- our context-handle,
- the language of our code snippet – JAVASCRIPT in this case. (stay tuned to hear more about this)
- the source code
- the result
The final step in our sample program is to drop the context. The context is terminated when our session ends, but it is still a good practice to drop the context when it is no longer needed as it does consume session resources.
Tidying up this program…
My intention with the first code sample was to demonstrate what the basic building blocks are to get started with the DBMS_MLE package. But before we move onto a more interesting example, it is necessary to make a small modification to our sample code that addresses a flaw in the code.
In the the first code sample we declared “ctx” to be a datatype of varchar2(50). This worked well enough to hold the runtime of our our simple hello world snippet. But the proper way to do this is to define our context handle to be of type: DBMS_MLE context-handle. This provides us with a flexible way to accommodate the more dynamic runtime contents of future snippets. This is done as follows:
DECLARE ctx := DBMS_MLE.context_handle_t ;
Of course we can take this another step further to streamline our code and assign the handle to our context at the same time as you will see in our next example:
Moving right along…
DECLARE
ctx DBMS_MLE.context_handle_t := DBMS_MLE.create_context();
codeSnippet clob ;
BEGIN
codeSnippet := q'~
var oradb = require("mle-js-oracledb");
var sqlQuery = "SELECT sysdate FROM dual" ;
var result = oradb.defaultConnection().execute(sqlQuery);
console.log(result.rows) ~';
DBMS_MLE.eval(ctx, 'JAVASCRIPT', codeSnippet);
DBMS_MLE.drop_context(ctx);
END;
/
In this example we have moved the JavaScript code snippet into a CLOB we have called “codeSnippet”. We can then simply pass that CLOB to our DBMS_MLE.eval() procedure. We are also using an API called “mle-js-oracledb”. The API provides similar capabilities to the Oracle database driver for node.js meaning that existing programs that work with the node.js driver should work with this API too. As you can see we can then use the API to connect to the database engine and to execute a SQL statement. In this code snippet we are also using console.log() to output our results.
So where do we go from here?
For our final code sample we will see how we can pass bind variables to add yet another layer of functionality to our JavaScript code:
DECLARE
ctx dbms_mle.context_handle_t;
code_snippet clob := q'~
var bindings = require("mle-js-bindings");
var year = bindings.importValue("year");
var isLeapYear
if ((year % 4) || ((year % 100 === 0) && (year % 400))) {
isLeapYear = "The year " + year + " is not a leap year" }
else {
isLeapYear = "The year " + year + " is a leap year"; }
bindings.exportValue("isleapyear", isLeapYear);
~';
isleapyear varchar2(40);
year number(4) ;
BEGIN
SELECT to_char(SYSDATE, 'YYYY') INTO year FROM dual ;
ctx := dbms_mle.create_context();
dbms_mle.export_to_mle(ctx, 'year', year);
dbms_mle.eval(ctx, 'JAVASCRIPT', code_snippet);
dbms_mle.import_from_mle(ctx, 'isleapyear', isleapyear);
dbms_output.put_line(isleapyear);
dbms_mle.drop_context(ctx); -- Drop the execution context once no longer required
END;
/
In this sample we are using JavaScript to determine if the year being returned from SYSDATE is a leap year.
The first thing you may have noticed is that we are defining the JavaScript code-snippet in our DECLARE statement. The next thing you should notice is that this time we are not using “mle-js-oracledb”. Instead we are calling “require(“mle-js-bindings”)” this module is used to hold the bindings between the SQL engine and the JavaScript code. We can then import and export our bind values. For example we import a value (year) that was previously exported from PL/SQL and in PL/SQL we then do the reverse. The net result is we pass the value for “year” from our SQL statement into our JavaScript code-snippet and then we output the message “isleapyear” back to PL/SQL. We can then use the “year” within our JavaScript code. The final step in our code snippet is to export our results back to the parent PL/SQL program. This time however, we are able to use the PL/SQL package: DBMS_OUTPUT to display our results.
As you can probably tell, we’ve only just scratched the surface of the DBMS_MLE package. There are a lot of powerful capabilities that we have not even looked at, for example handling type conversion and user permissions.
But do not worry that information is readily available here:
BTW – you may be wondering why would you ever want to do this? Well there are a number of reasons:
- The first reason is that it means you can use JavaScript with your APEX applications.
- The second reason I can think of is that it means you can now take existing JavaScript programs and run them directly against your Oracle database without having to worry about re writing the logic in PL/SQL
- And (yes bad grammar, sorry Mrs Rosa) the third reason is that it means you can get all the benefits of running JavaScript right where the data resides. There is no need to move the data out of the database to run your JavaScript code against it.
Let me know what you think of this article and don’t hesitate to reach if you have any questions about using this feature.