Dontcheff

Archive for March, 2022|Monthly archive page

Running JavaScript from within the Oracle Database using DBMS_MLE

In DBA, New features, PL/SQL on March 8, 2022 at 08:39

JavaScript is by far the most used language according to Github’s Octoverse Report.

As of 2021 JavaScript is the most commonly used programming language among software developers around the world, with nearly 65 percent of respondents stating that they used JavaScript. HTML/CSS, Python, SQL, and Java rounded out the top five most widely used programming languages around the world.

Did you know that you can run JavaScript code from right inside the Oracle Database?

By using the new 21c PL/SQL multilingual engine package called DBMS_MLE, we can now run JavaScrips from within the Oracle database.

The new package has 18 procedures and 1 function. So, it might take some time to understand what they do and how they should be used.

As of today, it is not possible to create stored JavaScript procedures in the database. But what we can do for now, is store them in a CLOB column in a table and create an executable PL/SQL procedure which will select the CLOB, i.e., the JavaScript procedure, and execute it via DBMS_MLE.EVAL

We cannot only run JavaScript code inside the Oracle Database but also exchange data seamlessly between PL/SQL and JavaScript. The JavaScript code itself can execute PL/SQL and SQL through built-in JavaScript modules. JavaScript data types are automatically mapped to Oracle Database data types and vice versa.

Well, here is a simple example of how to run JavaScript from the database:

The user of DBMS_MLE must have EXECUTE DYNAMIC MLE and EXECUTE ON JAVASCRIPT otherwise you will get again ORA-01031.

For further and more detailed examples, check Lucas Jellema‘s and Stefan Dobre‘s articles.

One might wonder, why run JavaScript code from within Oracle?

Actually, there are several reason why we would want to run JavaScript from within the Oracle database.

Sean Stacey outlined 3 reasons in his blog post How to Run JavaScript In Oracle Database 21c:

  1. Use JavaScript with your APEX applications: Stefan Dobre wrote an excellent blog post entitled JavaScript as a Server-Side Language in Oracle APEX 20.2
  2. Use your existing JavaScript programs and run them directly against your Oracle database without having to worry about re-writing the logic in PL/SQL. There are about 100K reusable JS libraries that can be leveraged.
  3. Not to use the Oracle database simply as a bag of tables with data. There is no need to move the data out of the database to run your JavaScript code against it.

There are several other reasons as well. There are more software developers who are and interested in writing JS code compared to PL/SQL. R and Java are already part of the Oracle database, so it is natural that JavaScript is being embedded as well. Naturally, most likely Python, Ruby, etc. are also in the pipeline.

As a side note (on the humorous side), here is a Twitter screenshot for the ones watching on Nextflix “Emily in Paris” 🙂

Two important additional resources:

Oracle Database Multilingual Engine (MLE): GraalVM in the Database

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

Oracle JavaScript Extension Toolkit (Oracle JET)