Introduction to JavaScript in Oracle Database 23ai

April 11, 2023 | 7 minute read
Text Size 100%:
This post was initially written for Oracle Database 23c Free - Developer Release. It has been updated on June 5, 2024

 

Developing Applications for Oracle Database: Client & Server

The Oracle Database is renown for its rich support of programming languages. In addition to support for many client-side development languages the Oracle database has supported server-side programs for a very long time. Sometimes these have been referred to as "stored procedures", although the name doesn't give the feature the credit it deserves: apart from writing "procedures" a great many other possibilities exist to work with data.

The most common programmatic server-side interface to the Oracle database is PL/SQL. By using PL/SQL it is possible to keep business logic and data together, often offering significant improvements of performance and efficiency. Developers also benefit from a unified processing pattern for data, regardless of the client interface in use. And last but not least using a programmatic interface to the application decouples the frontend from the backend, crucial for modern application development techniques.

In addition to PL/SQL it is possible to create stored procedures using the Java Programming Language in the database. Even more languages are supported via External Procedures. That was the situation before the release of Oracle 21c.

Introducing JavaScript in Oracle Database 21c

Oracle 21c for Linux x86-64 added another language for server-side development to the mix: JavaScript. JavaScript is one of the most popular programming languages today. It has come a long way since its inception as a browser-based solution for interactive web pages. Whilst its popularity for front-end development remains strong, it has found its way into backend development as well: the node.js and deno projects for example are very popular in that space.

JavaScript support further enhances Oracle's already strong message about the Converged Database. A Converged Database is a multi-model, multi-tenant, multi-workload database. It effortlessly supports the data model and access method each development team wants, simplifying the development process. With its high popularity the JavaScript language fits right into this concept. Under the hood the JavaScript engine is based on GraalVM, a polyglot runtime that can execute several programming languages with high performance. The component powering the JavaScript engine in Oracle 21c and later is known as Multilingual Engine (MLE).

Oracle release 21c focused on dynamic execution of JavaScript snippets, and integration into Oracle's low-code application framework: APEX. The DBMS_MLE package allows developers to execute code snippets written in JavaScript inside the database, both on-premises and in the cloud, for Linux x86-64.

Enhanced JavaScript Support in Oracle Database 23ai

The availability of Oracle Database 23ai, including the Free version on Linux x86-64 provides a wealth of new features to developers. In the context of JavaScript in Oracle database the following two concepts are introduced:

  • JavaScript modules and environments
  • Inline JavaScript procedures

The following sections discuss these in more detail. A whole new manual, the Database JavaScript Developer's Guide, has been created to help you get started.

JavaScript modules and environments

Both of these are stored as schema objects and can either be created in-line with the module header, based on Character Large Objects (CLOBs), or BFILEs stored in the file system. The following example demonstrates how to create a MLE JavaScript module with the actual JavaScript code provided in-line with the declaration:

create mle module if not exists example_module 
language javascript as 

/**
 * convert a delimited string into key-value pairs and return JSON
 * @param {string} inputString - the input string to be converted
 * @returns {JSON}
 */
function string2obj(inputString) {
    if ( inputString === undefined ) {
        throw `must provide a string in the form of key1=value1;...;keyN=valueN`;
    }

    let myObject = {};
    if ( inputString.length === 0 ) {
        return myObject;
    }

    const kvPairs = inputString.split(";");
    kvPairs.forEach( pair => {
        const tuple = pair.split("=");
        if ( tuple.length === 1 ) {
            tuple[1] = false;
        } else if ( tuple.length != 2 ) {
            throw "parse error: you need to use exactly one '=' between key and value and not use '=' in either key or value";
        }
        myObject[tuple[0]] = tuple[1];
    });

    return myObject;
}

/**
 * Perform a simple string concatenation
 * @param {string} str1 - the first input string
 * @param {string} str2 - the second input string
 * @returns {string}
 */
function concat(str1, str2) {

    return str1 + str2;
}

export { string2obj, concat }
/

Just as with client-side node.js development, modules can import other modules to allow for a divide-and-conquer programming model. It is also possible to use existing JavaScript modules, leveraging the huge ecosystem the community created, provided they adhere to the rules laid out by the MLE runtime.

JavaScript in Oracle Database 23ai is based on ECMAScript 2023, exporting and importing functionality is based on the export and import keywords. Since there is no file system where modules reside, a new helper-entity named MLE environment is introduced. These MLE environments are the second major new innovation in database 23ai and like MLE modules they are schema objects. MLE environments define import names to be used with the import keyword, pointing to a module. MLE environments and dependencies between MLE modules are not in scope of this introduction and will be covered extensively in future posts. Further details can be found in the new Database JavaScript Developer's Guide, chapter 2.

It is of course possible to call JavaScript code from SQL and PL/SQL as well. A JavaScript specific so-called Call Specification exposes a function exported from the MLE Module, for example:

create function if not exists string_to_JSON_module_example(
    p_str varchar2
) return JSON
as mle module example_module 
signature 'string2obj';
/

Each PL/SQL function or procedure wishing to invoke JavaScript code needs to reference the module and (JavaScript) function, along with the correct number of parameters both in the PL/SQL argument list as well as the mapped JavaScript function using the signature clause. With all requirements satisfied it is possible to execute JavaScript code via the PL/SQL module call:

select 
    json_serialize(
        string_to_JSON_module_example('a=1;b=2;c=3;d')
        PRETTY
    ) as result;

RESULT
--------------------

{
  "a" : "1",
  "b" : "2",
  "c" : "3",
  "d" : false
}

Inline JavaScript Functions and Procedures

In cases where you just need a JavaScript function, you can use inline JavaScript procedures instead of a module. The previous example can be rewritten as an inline function as follows:

create function if not exists string_to_JSON_inline_example(
    "inputString" varchar2
) return JSON
as mle language javascript 
;
/

Inline JavaScript functions offer high convenience at a slight expense of functionality but in many cases the trade-off is negligible. The function in the listing above can be executed as if it were a PL/SQL function, a nice productivity boost.

Prerequisites for using JavaScript in Oracle Database 23ai

Before you can run the examples in this post make sure you meet the following prerequisites:

  • Ensure that the compatible initialization parameter is set to 23.0.0 or higher
  • The new initialization parameter mle_prog_languages is set to ALL
  • The following system privileges have been granted to your user:
    • create mle
    • create procedure
    • any other privileges such as creating tables, indexes, etc. The db_developer_role might prove to be useful
  • The execute on javascript object privilege has been granted to the user
  • Your platform is Linux x86-64

More information

The Database JavaScript Developer's Guide, part of the documentation set for Oracle Database 23ai, covers JavaScript development in great detail. Please refer to the book for all the details. Over time additional posts about JavaScript in Oracle database will appear on this blog, so please stay tuned.

Summary

The JavaScript implementation provided by Oracle Database 23ai on Linux x86-64 is another great step forward, offering developers a popular language alternative to PL/SQL and Java for writing server-side code. MLE Modules and Environments complement the existing feature set initially release with 21c. Their use is simplified for special occasions using inline JavaScript functions and procedures. Additional blog posts with further details are planned for release over the coming weeks.

For those who cannot wait please hop over to the JavaScript Developer's Guide. Have fun!

Martin Bach

Martin is a product manager at Oracle helping customers in Europe and around the world address their IT related problems. He is most interested in cloud technology, DevOps and how these can be used best with Oracle technology.


Previous Post

Introducing Oracle Database 23c Free – Developer Release

Gerald Venzl | 4 min read

Next Post


Oracle Forms in the Oracle Cloud – What’s New

Michael Ferrante | 3 min read