Starting with 21c, Oracle Database can now execute JavaScript, powered by GraalVM. In this blog post, we peek under the hood of this new feature; we will also have a follow-up blog post showing JavaScript as a server-side language in Oracle APEX.
◉ Using JavaScript in Oracle Database
◉ Multilingual Engine: Embedding GraalVM in the Oracle Database
◉ A Vision for the Future
Recently, Oracle released the next version of the world’s most advanced database, Oracle Database 21c. This release adds the ability to execute JavaScript code directly in the database. Oracle Database has long had support for server-side programming in PL/SQL, Java and C. Server-side business logic plays an important role in many enterprise applications: first, the logic gets executed where the data resides, rather than having to bring the data to the logic. This removes unnecessary data transfer over the network and can significantly improve performance of data-intensive operations, especially if applied to terabytes of data or more. Second, storing and executing, for example, business rules inside the database guarantees that the rules are followed by every application, as well as users accessing the data, which can drastically simplify the implementation of security and compliance requirements. Last, but not least, commonly-used functionality can be stored in a central place and executed as simple user-defined functions on top of ordinary SQL statements, avoiding the need to replicate the code in every application. This can be especially practical when the logic is more complex and/or tends to change frequently.
Read More: 1Z0-497: Oracle Database 12c Essentials
With 21c, the set of supported languages was expanded to include JavaScript, one of the most widespread and popular programming languages today. Developers can now use this popular language for database programming and tap into the rich ecosystem of tools and libraries available for JavaScript. Similar to PL/SQL, server-side JavaScript execution is tightly integrated with the database with code executing within the process of a database session, close to the data and SQL execution. This tight integration enables efficient data exchange between JavaScript on the one side and SQL and PL/SQL on the other.
In the 21c release, JavaScript support in Oracle Database focuses on the low-code application framework Oracle Application Express (APEX). Starting with Oracle Database 21c and APEX 20.2, developers can implement server-side logic in APEX applications (e.g. dynamic actions and processes) in JavaScript and are no longer restricted to just PL/SQL. We believe this is an exciting new feature for APEX developers, that has the potential to increase productivity and enable cool new features in APEX applications. We’ll take a more detailed look at the new JavaScript feature in APEX in a follow-up blog post.
Besides APEX, server-side JavaScript execution in Oracle Database is available through a general-purpose PL/SQL API. In this blog post, we give a short tour through this API and some of the features available in 21c. Furthermore, we peek under the hood and discuss some details about how server-side JavaScript execution is implemented.
We call the component that powers JavaScript execution in Oracle Database Multilingual Engine (short: MLE). The name might give away that MLE is much more than just a JavaScript engine. Under the hood, the major ingredient of MLE is GraalVM — a polyglot runtime that can execute several programming languages with high performance. We built MLE on the GraalVM foundation using the unique embedding capabilities of GraalVM that we believe are interesting for other scenarios as well. But first, let’s have a look at the user-facing side.
Using JavaScript in Oracle Database
Oracle Database provides the PL/SQL’s quote syntax to avoid any escaping issues in code snippets.
DECLARE
ctx DBMS_MLE.context_handle_t := DBMS_MLE.create_context();
BEGIN
DBMS_MLE.eval(ctx, 'JAVASCRIPT', q'~console.log("Hello, World!");~');
DBMS_MLE.drop_context(ctx);
END;
/
The DBMS_MLE package offers additional procedures, for example to exchange values between JavaScript and PL/SQL.
By default, the JavaScript function console.log() writes to the buffer of the PL/SQL package DBMS_OUTPUT. If this snippet is executed through a client like SQL Developer Web, SQLcl or SQL*Plus, you’ll see the output generated by console.log() on the console of those tools. Depending on the client, it might be necessary to enable retrieval of DBMS_OUTPUT in the client. For example, in SQL*Plus, the printing of the output generated by DBMS_OUTPUT is controlled by the SET SERVEROUTPUT setting. The output can also be retrieved manually using the DBMS_OUTPUT package.
DBMS_MLE is not limited to a single context within a session. Using DBMS_MLE.create_context(), we can create multiple contexts within a session, each of which represents a completely independent JavaScript runtime. Granting fine-grained control over contexts to developers is a powerful feature: it enables isolation of different applications in separate contexts within the same session and prevents any interference between the applications. Note, however, that contexts are not free. Each context consumes memory within the current database session. To minimize the impact on database resources, applications should not create more contexts at a time than are strictly required, and drop contexts as soon as they are no longer referenced. This is no different to resources like SQL cursors.
To be really useful for server-side programming — for example in APEX applications — JavaScript code needs to be able to interact with the database. MLE provides the mle-js-oracledb JavaScript module that supports the execution of SQL and PL/SQL statements. In the next example, we use this module to execute a simple SQL query that returns the current time.
DECLARE
ctx DBMS_MLE.context_handle_t := DBMS_MLE.create_context();
user_code clob := q'~
const oracledb = require("mle-js-oracledb");
const sql = "SELECT SYSTIMESTAMP as ts FROM dual"
// execute query
const result = oracledb.defaultConnection().execute(sql);
console.log(JSON.stringify(result.rows));
~';
BEGIN
DBMS_MLE.eval(ctx, 'JAVASCRIPT', user_code);
DBMS_MLE.drop_context(ctx);
END;
/
The mle-js-oracledb API closely follows the regular client-side Oracle Database driver for node.js. Using mle-js-oracledb, applications can execute arbitrary SQL statements (e.g. queries, DML statements, anonymous PL/SQL blocks), bind values to placeholders, and fetch query results. Existing JavaScript code that uses the node-oracledb API can usually be adapted to mle-js-oracledb with little effort. Compared to node-oracledb, we don’t have to actually connect to the database here. All SQL statements executed from JavaScript code in MLE are executed within the current database session. The oracledb.defaultConnection method returns a Connection object that represents the current session. Note that server-side JavaScript code executed in APEX can conveniently access the connection object via the apex.conn property.
When fetching query results and binding placeholders in SQL statements, mle-js-oracledb translates between PL/SQL types on the one side, and JavaScript types on the other side. Just as with node-oracledb, PL/SQL types are mapped to the closest respective JavaScript type by default. In the example above, the result column ts has the PL/SQL type TIMESTAMP WITH TIME ZONE, and is fetched as a JavaScript Date value.
Converting between PL/SQL types and native JavaScript types is not always appropriate, though. Data type conversions can lead to a loss of precision. In the next example, we execute a SQL query that returns a NUMBER value. We truncate the result of exp(4) to 3 three decimal places, expecting the result 54.598.
DECLARE
ctx DBMS_MLE.context_handle_t := DBMS_MLE.create_context();
user_code clob := q'~
const oracledb = require("mle-js-oracledb");
// SQL NUMBER result: 54.598
const sql = "SELECT trunc(exp(4), 3) AS n FROM dual";
const result = oracledb.defaultConnection().execute(sql);
// fetch floating-point JavaScript number value
// Output: 54.598000000000006
console.log(result.rows[0][0].toString());
~';
BEGIN
DBMS_MLE.eval(ctx, 'JAVASCRIPT', user_code);
DBMS_MLE.drop_context(ctx);
END;
/
By default, Oracle NUMBER values are fetched as JavaScript number values. Conversion from the more precise Oracle NUMBER format to the less precise JavaScript number format can lead to unexpected results. In our example, the returned JavaScript number 54.598000000000006 differs slightly from the actual result of the SQL query due to the floating-point conversion. Although this effect might not seem too relevant in this particular example, this changes drastically once we start dealing with currency values, for example — here, numerical precision is paramount.
To improve on this situation, the mle-js-oracledb API enables developers to choose the data representation that is appropriate for a particular application scenario. As in the two preceding examples, SQL values can be fetched as native JavaScript types, which integrates conveniently with existing JavaScript code. As an alternative, MLE offers JavaScript APIs for selected PL/SQL types that eliminate the need for conversion to a native JavaScript type, and avoids any loss of precision. In the following example, we execute the same SQL query as before and fetch the resulting NUMBER column. However, this time we instruct mle-js-oracledb to fetch the column as OracleNumber objects.
DECLARE
ctx DBMS_MLE.context_handle_t := DBMS_MLE.create_context();
user_code clob := q'~
const oracledb = require("mle-js-oracledb");
const sql = "SELECT trunc(exp(4), 3) AS n FROM dual";
// fetch NUMBER column as OracleNumber
const options = { fetchInfo: { N: { type: oracledb.ORACLE_NUMBER } } };
const result = oracledb.defaultConnection().execute(sql, [], options);
// print decimal OracleNumber value
// Output: 54.598
console.log(result.rows[0][0].toString());
~'
BEGIN
DBMS_MLE.eval(ctx, 'JAVASCRIPT', user_code);
DBMS_MLE.drop_context(ctx);
END;
/
OracleNumber objects represent the exact same decimal value as the original SQL value. Furthermore, OracleNumber objects provide methods for decimal precision arithmetics with the same semantics as the corresponding PL/SQL operations on the NUMBER type.
Multilingual Engine: Embedding GraalVM in the Oracle Database
So far, we’ve seen how Oracle Database 21c can execute JavaScript and enable APEX applications to benefit from server-side JavaScript logic. But how does this actually work under the hood? In the introduction of this article, we mentioned that the Multilingual Engine (MLE) at its core is an embedding of GraalVM in Oracle Database. Not only is GraalVM a great stand-alone runtime for Java and other languages, but it is also embeddable into native applications. MLE is an interesting example of how GraalVM can enrich existing applications with programming language capabilities, for example JavaScript execution. In the rest of this article, we shine a spotlight on some aspects of the architecture of MLE and discuss the GraalVM features that enable this embedding.
The following diagram shows a simplified version of MLE’s architecture. Let’s go through some of these boxes and arrows and see what they entail.
Posts
0 comments:
Post a Comment