MLE JavaScript Deep Dive

 

MLE (Multilingual Engine) is a new technology available in Oracle Database 23c. It allows us to run code in the database other than PL/SQL. The first language that became available with this was JavaScript. For a while I understood the concept, but I really wanted to to understand how to use it so decided to go through all the components

The version of JavaScript that is available is one that is compliant was ECMAScript 2022. JavaScript is pretty fast evolving, so you can refer to the following spec if you want to see if the specific JavaScipt feature you want to use is available in this environment.

Required Grants

In order to use MLE, your database user requires some privileges. To execute JavaScript code you require the execute on javascript privilege; To execute dynamic JavaScript (through the DBMS_MLE package), you require the execute dynamic mle privilege. And finally, to be able to create MLE schema objects, you require the create mle privilege.

Refer to this security document for more information.

Running JavaScript Code

The first thing you will likely want to do is run some code in the database. What better way than to start with "Hello, World!" of sorts. At the most basic level, you can use the package DBMS_MLE to create a context and pass in a string which is your JavaScipt code. That block will look something like this:

It's worth pointing out here, by default console.log statements print out to the dbms_output stream. It is however configurable to go out to a clob instead, by calling the procedure DBMS_MLE.SET_STDOUT prior to the execution of the script.

APEX and SQL Developer Web provide functionality to make quick snippets like this much easier (so you don't need to wrap your JS code in this PL/SQL code yourself.

First, for the APEX side - when you are SQL Commands, there is a new "Language" filter. By default this is focused on SQL, but you can switch it between:

  • SQL
  • PL/SQL
  • JavaScript (MLE)

When you focus on "JavaScript (MLE)" we can write JavaScript code directly.

This same functionality exists for all of the processes definitions (page processes, application processes) within an APEX application - where you can specify either PL/SQL or JavaScript (MLE).

Now over in SQL Developer Web, within the Launchpad, if you have MLE available you will notice a new tile for running MLE JS code. 

After navigating to that page for the first time, you will be focused on the Editor tab which is used for creating modules - more on those later. For now, to run ad-hoc JavaScript, switch to the snippets tab where you can write the code you want to run.

Environments

So far, the code we have been running in the "default" environment. One of the types of MLE schema objects is an Environment and they have a couple of important characteristics. They allow you to specify one or more language options and allow you to specify imports for any modules compiled into the database - you link an import name (usually the lowercase name of the module) to a compiled module. When the JavaScript code runs under that environment, those imports can be properly resolved.

At the time of writing this post, there are 3 language options you can specify for JavaScript:

  1. Run in strict mode (js.strict=false)
  2. Enable the console object (js.console=true)
  3. Polyglot (js.polyglot-builtin=true)
At the most basic level, an environment is created with the statement, create mle env NAME. That is creating an environment with no special characteristics. When you define a call specification for a JavaScript function, the environments is one of the directives that you can specify (more on that later). Taking the same snippet we ran earlier on with the DBMS_MLE package, we can extend that snippet to specify the environment in our context definition.

You can read more about what strict mode does in Appendix C of the JavaScript spec, but just to take one example - it doesn't allow you to assign values to undeclared variables. e.g. name = "Fred" vs const name = "Fred". 

For disabling console, that makes that object unavailable for use and will raise an exception about an undeclared variable if you attempt to reference it in your code.

And for the polyglot, if you disable this you will not be able to import modules into your code - be than built-in modules or custom modules you have written or imported.

To specify language options, the declare statement for the environment has a clause for language option. You can specify a comma separated list of any of the language options you wish to differ from their default value.

The result of violating an language option is an ORA-04161: Reference Error.

From the APEX side, there is a new Database Session attribute in Security Attributes for your application "MLE Environment". I'm not aware if it's possible to define this globally so it can apply for your SQL Commands session, but at least you can specify it at the application level and have it apply to any code you write for your application.

From SQL Developer Web's perspective, they make it super easy to run against different environments. In the snippets worksheet, in the toolbar, there is a selection for Environments that shows all the environments that are available to you.

Modules

An MLE module can be thought of in the same way you think of JavaScript modules. We load them as schema objects which can then either be referenced in other modules or exposed as a PL/SQL function or procedure.

To demonstrate this, lets make a calculator module that we will name "CALC_MODULE". The object we create is an mle module, and we must specify the language on the declaration. In the module definition, we must export any objects we want to be made available outside of the module. This won't be unfamiliar if you have written any modules in JavaScript outside of the database.

Once you have a module defined, the next decision you need to make is if this function needs to be accessible in SQL (the other option being its a module that you will import to other modules). To address the first task of making it available to SQL - you need to define a call specification to the function. On your function definition, you need to specify which module on the object definition. In the case of JavaScript functions, in the signature you do not put the parenthesis - for example you specify signature 'addition' and NOT signature 'addition()'. 

With those schema objects created, we should now be able to run our function.

 

One other technique you can use for creating a JavaScript based function is to have an inlined call specification. Essentially you create your JavaScript function as a string literal and specify the as mle language javascript directive on the PL/SQL function. This is explained in this doc.

Now to take it one step further, what if you have two modules in your schema and you want one module to be importable by one or more of your other modules. To accomplish this, we do two actions:

  1. We define import statements in our environment definition for those that will be imported elsewhere
  2. When declaring the call specification on your JavaScript function, you specify the env directive
To demonstrate this, we will create a second module (payroll_module) that imports the calc_module. This will contain a function called processBonus.

Now, before we define a call specification, we update our environment with the calc_module specified as an import and then create a procedure but unlike previous examples, we additional specify the env.


Without doing this step, you will get an error about being unable to load calc_module.

Loading Third Party Modules

So far the examples have been about writing our own modules. The other common action you may want to do is load third party modules. APEX SQL Workshop provides an interface that makes it super easy to load those modules - by allowing you to either upload a file or specify the URL.

For a practical example, let's say I wanted to load the validator module. First I need to go and determine the download URL - if you go to the popular jsDeliver site and search for "validator" - I will end up at the following URL: https://www.jsdelivr.com/package/npm/validator. Within this page there is a little widget near the top of the page where you can grab the download link while switching a toggle between Default and ESM. We need to grab the ESM link.

Now that we have the module's URL, back in Object Browser, in the context menu for MLE Modules - the Create MLE Module dialog provides for 3 options, where one of those is URL. Version is an optional field, but I feel it is worthwhile to store that on the schema object so you can easily keep track of which version you deployed via the MLE dictionary views.


Running SQL Code

To run SQL (or PL/SQL) code you need the connection from the SQL Driver. This is built in and available in your code already. The following references can be used:

  • oracledb.defaultConnection()
  • session
  • apex.conn (obviously, only in APEX)

Once you have a reference to the connection, you will typically be using the execute function, which takes 3 arguments: SQL statement, binds (array or object) and additional options.

By default, queries will return the results in object format. But one configuration you pass into the 3rd parameter (additional options) is to specify you want the result set returned as an array instead. This is done by setting the property "outFormat": oracledb.OUT_FORMAT_ARRAY.  

One thing to note, APEX items are not automatically inherited as binds if you run a query referencing items from your application. You have to explicitly provide the binds in an array or object in the second parameter. To get the value of application or page items, APEX provides a special object apex.env which contains all page and application items. For example apex.env.P1_INPUT.

For full documentation on this module, you can refer to the following doc link: https://oracle-samples.github.io/mle-modules/docs/mle-js-oracledb/23c/.

Other Built in Modules

Aside from the SQL Driver, there are some other built in modules that may be of interest:

  • MLE Bindings / mle-js-bindings
  • MLE PL/SQL Types / mle-plsql-bindings
  • MLE Fetch API Polyfill / mle-js-fetch
  • MLE Base64 Encoding / mle-encode-base64

These are all documented here: https://oracle-samples.github.io/mle-modules/

References

Credits

Opening Splash Photo by Veronica Reverse on Unsplash

 

Popular posts from this blog

Report row buttons firing a dynamic action

Image
A lot of the time on reports of data, we'll define a link column, and specify an image which is the beloved pencil icon. Which is good and all, but looking in the Sample Database Application (packaged application), they have taken a different approach to style links - a button that stretches the width of it's containing cell. So, the UI ends up looking like so: So, is this done? Well first, you define a new column for the placeholder. Suppose I want my link column to be Report, I define an extra column in my query: , 'Report' report_link Then I need to go to my new column's attributes, and change the column type to a link. And, in the link attributes, set the text to what you want displayed. Here, I'm just going to display the value of the new column and point to my desired page. So I set the target page, then also the Link text as #REPORT_LINK#. At this point the report will just show link text, as we expect. So, then to replicate t
Read more

Accessing the last request value from a page submission

Image
When you submit the page, you will typically have page processes that run. For me, I use these request values to determine which processes are run. This is rather straight forward for on submit processes as you can just specify the condition request = Expression 1 Or, if you have multiple requests that link to this process, Request is Contained within Expression 1, and Expression 1 would be a comma delimited set of values. Request simply refers to the bind variable :REQUEST (or apex_application.g_request). If we try and do the same on a page rendering process, we no longer have access to this value - which makes sense. If you update the branch, to set the request portion of the URL, we will then be able to access the bind variable :REQUEST in all page rendering processing points. This has the obvious downside that if the user wants to reload the page,  clicks in the address bar and hits enter (or clicks refresh), the processes on that condition
Read more

Installing Oracle Instant Client on Ubuntu

Now that Oracle has enabled us to download instant client without any click through for accepting the license , I wanted to revisit a seamless install of the instant client on a new set up. Ubuntu has the documentation about installing the instant client here: https://help.ubuntu.com/community/Oracle%20Instant%20Client. First - because Oracle provides their releases in RPM archive format (or a tarball), in order to have an installer you need to create a DEB archive. There is a package in the archives, alien, which aids this process. This gives the start of the script: #!/bin/bash # Install dependencies sudo apt install alien The 3 packages the Ubuntu documentation tells us to retrieve are: - devel - basic (I opt for basiclite instead) - sqlplus So, over at the downloads page: https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html we can grab the link. # Download files. Example specific to 19.3 # Some links were not correct on the do
Read more