Bringing Modern Programming Languages to the Oracle Database with GraalVM
From Guess Author Bastian Hossbach
The original version of this publication was published at https://medium.com/graalvm/bringing-modern-programming-languages-to-the-oracle-database-with-graalvm-80914d0c0167
GraalVM is a universal virtual machine for running a variety of programming languages with high performance on the same platform. It can easily be embedded into existing systems such as databases. By embedding GraalVM into the Oracle Database, we can provide many modern languages and their ecosystems for transaction processing, data analytics and machine learning. We only have to maintain a single runtime independent of the number of supported languages as opposed to embedding one runtime for each supported language. Basic components such as data type conversions or a server-internal SQL driver can be shared across all languages with GraalVM.
GraalVM — A Universal and Embeddable Virtual Machine
Support for many languages is not the only benefit of GraalVM. Although it primarily runs as part of a JVM, GraalVM offers a solution for building native images written in the Java programming language. This native image featureallows to compile an entire Java application, possibly embedding multiple guest languages supported by GraalVM and including the needed virtual machine infrastructure, into a native executable or a shared library. The main advantages of a GraalVM native image are improved startup times and reduced memory footprint. This enables existing applications and platforms not written in Java to embed GraalVM as a shared library and benefit from its universal virtual machine technology.
At Oracle, we are currently working on embedding GraalVM into the Oracle Database and MySQL. We call these extensions Multilingual Engine (MLE). In this article we focus on MLE for Oracle Database only. Oracle Database MLE is an experimental feature at the moment.
Besides embedding GraalVM into the Oracle Database, we also work on tools that make working with MLE as convenient as possible. For example, we are currently developing tools that automatically package an entire application and deploy it into the Oracle Database with a single command.
Another major area of work are the languages we are making available inside the Oracle Database. Languages need to be extended in order to become useful. For example, we need a conversion engine that can convert between database types and language types as well as a bridge between the SQL engine of the Oracle Database and a SQL API of a new language.
MLE offers two different ways for executing code written in by MLE supported languages. First, stored procedures and user-defined functions can be written in a MLE language. Second, a new PL/SQL package called DBMS_MLE for dynamic scripting, i.e., defining anonymous scripts at runtime and executing them, is provided. We first discuss dynamic script execution with DBMS_MLEin the next section. The subsequent sections then explain how to create user-defined extensions that can be used from SQL and PL/SQL.
Ad-hoc Execution of Scripts with Dynamic MLE
DBMS_MLE can execute scripts that are given in form of strings in PL/SQL. Data is exchanged with a script in both directions (in and out) via so-called bind variables. Lastly, a script can print messages that will be put in the output
buffer of the database. Let’s have a look at a concrete example:
After the anonymous PL/SQL block above got executed (for example, in SQL*Plus), the database output buffer will have the following content (show with SET SERVEROUTPUT ON in SQL*Plus or use DBMS_OUTPUT.GET_LINE()to retrieve):
Of course, we could just as easily use Python to accomplish the same thing:
Executing this PL/SQL block would place the following lines in the output buffer:
Component Sharing Across Languages
MLE Stored Procedures
While running a script written in a modern language on-the-fly is convenient in many situations, it is not ideal for developing large and complex applications. Dynamic MLE requires a skeleton in PL/SQL and third party libraries cannot be used directly. Furthermore, code is best managed by the database similar to data. To unleash the full power of MLE, we allow to persistently store and maintain user code in the database in form of modules consisting of user-defined functions and stored procedures. For painless packaging and deployment of modules, we plan to provide external tools that do everything with a single command.
Note that for improving security and performance we use bind variables in the SQL statement. In this particular case we set the values of the bind variables by giving an array of values. That means the position of a value in the array [raise, empno] determines the bind variable it substitutes (i.e., the first bind variable will be set to the value of raise and the second bind variable will be set to the value of empno). Alternatively, bind variables can be set by name.
Next, we can define a function to check if an employee is a manager:
With those two helper functions, we can now implement our business logic:
The assignment to module.exports is used to export the function salraise() to the database. Putting all together in a file named load_salraise.js, we can add additional code that does the deployment into the database:
We can now run the script that deploys the module code and registers the function salraise() as a stored procedure via Node.js:
MLE User-defined Functions
We can first download the validator package using the NPM package manager:
We can then execute the deployment script via Node.js:
After the deployment, we can call the function as follows: