Infrastructure at your Service

Franck Pachot

ODC Appreciation Day : Javascript in the database

By October 10, 2017 Oracle One Comment

Tim Hall has launched the idea to post small blogs this day, from all the Oracle community, about an Oracle feature. I choose one feature that is only released in beta test for the moment: the Multilingual Engine (MLE) which is able to run Javascript stored procedures in the database.

Why?

When I first heard about this idea, last year before OOW16, I didn’t understand the idea at all. But the good thing at Oracle Open World, is that we can discuss with Oracle product managers, and with other Oracle DBAs or Developers, rather than relying on rumors or wrong ideas. My perception of Javascript was narrowed to the language used at client-side in thin clients, in the browser, to manage the presentation layer. It is interpreted by the browser, has no type checking, and errors are not easy to understand. Clearly, the opposite if something that I want to run in my database, on my data. PL/SQL is obviously the best choice: compiled and run into the database, strong typing to avoid runtime errors, directly integrated with SQL for better performance, etc.

So that idea of JS in the database made me smile, but I was wrong. What I didn’t get is that Javascript is just a language, and running Javascript does not mean that it has to be interpreted like when it is running on a browser.

Multilingual Engine (MLE)

Actually, what Oracle is developing in its lab goes far beyond just running Javascript in the database. They are building an execution engine, like PL/SQL or SQL execution engine, but this one being able to run programs written in different languages. They start with Javascript and TypeScript (and then strong typing here) but this can be extended in the future (Python, and why not PL/SQL one day running there). The programs will be loaded into the database as stored procedures/functions/packages and compiled into an intermediate representation, like bytecode. This code is optimized to access efficiently to data, like the PL/SQL engine.

Actually, I’ll show in a future post that this new engine can run faster than PL/SQL for some processing and that it looks like the context switching with the SQL engine is highly efficient.

Javascript

So, why would you write your stored procedure in Javascript? The first reason is that there are a lot of existing libraries available and you may not want to re-write one. For example, I remember when working on an airline company application that I had to write in PL/SQL the function to calculate the orthodromic distance (aka great circle). This is a very simple example. But if you can get the formula in Javascript, then why not compile from this rather than translate it into another language? Currently, you can find pretty everything in Javascript or Python.

The second reason is that your application may have to use the same function at different layers. For example, you can check that a credit card number is correctly formed in the presentation layer, in order to show quickly to the user if it is correct or not. That may be Javascript in the browser. But the database should also verify that in case the rows are inserted with a different application, or in case the number has been corrupt in between. That may be PL/SQL in the database. Then you have to maintain two libraries in two different languages, but doing the same thing. Being able to run Javascript in the database let us re-use exactly the same library in the client and in the database.

Finally, one reason why some enterprise architects do not want to write procedures in the database is that the language for that, PL/SQL, can only run on Oracle. If they can write their business logic in a language that can run everywhere, then there is no vendor lock-in anymore. They have the possibility to run on another RDBMS if needed, and still get the optimal performance of processing data in the database.

Public Beta

Currently, this is a lab project from Oracle in Zurich. They have released a public beta downloadable as a VM. Just go to the download page at http://www.oracle.com/technetwork/database/multilingual-engine/overview/index.html

Capture;LE

And stay tuned to this blog to see some performance comparison with PL/SQL User-Defined Function.

 

One Comment

Leave a Reply


2 + two =

Franck Pachot
Franck Pachot

Technology Leader