Managing JavaScript Modules in the Database

SQL allows the creation of MLE modules as schema objects, assuming the necessary privileges are in place.

At a minimum, you need the CREATE MLE MODULE privilege to create or replace an MLE module in your own schema. Additionally, you must have the execute privilege on the target JavaScript language object.

See Also:

• System and Object Privileges Required for Working with JavaScript in MLE for more information about MLE-specific privileges

• Oracle AI Database Security Guide for more details about privileges and roles in Oracle Database

Topics

• Naming JavaScript Modules
  Each JavaScript module name must be unique in the schema that it is created in. Unless a fully qualified name is used, the current user's schema is used.
• Creating JavaScript Modules in the Database
  JavaScript modules are created in the database using the CREATE MLE MODULE DDL statement, specifying name and source code of the MLE module.
• Storing JavaScript Code in Databases Using Single-Byte Character Sets
  Character set standards and things to remember when using a single-byte character set with MLE.
• Code Analysis
  JavaScript syntax errors are flagged when an MLE module is created, but linting tool of your choice should still be used to perform analysis before executing the CREATE MLE MODULE command.

Naming JavaScript Modules

Each JavaScript module name must be unique in the schema that it is created in. Unless a fully qualified name is used, the current user's schema is used.

As with other schema object identifiers, the module name is case-sensitive if enclosed in double quotation marks. If the enclosing quotation marks are omitted, the name is implicitly converted to uppercase.

When choosing a unique name, note that MLE objects share the namespace with tables, views, materialized views, sequences, private synonyms, PL/SQL packages, functions, procedures, and cache groups.

Creating JavaScript Modules in the Database

JavaScript modules are created in the database using the CREATE MLE MODULE DDL statement, specifying name and source code of the MLE module.

As soon as an MLE module has been created, it is persisted in the database dictionary. This is one of the differences when compared with dynamic execution of JavaScript code using DBMS_MLE.

CREATE MLE MODULE (without the OR REPLACE clause) throws an error if an MLE module with the given name already exists. With CREATE OR REPLACE MLE MODULE, the existing module is replaced if it exists, otherwise a new one is created. When an MLE module is replaced, privileges to it do not need to be re-granted.

For those who are familiar with PL/SQL, note that this is exactly the same behavior experienced with PL/SQL program units.

If you do not wish to replace an existing module in the event the module name is already in use, you can use the IF NOT EXISTS clause rather than CREATE OR REPLACE. The syntax for this variation is shown in Example 3-1. The IF NOT EXISTS and OR REPLACE clauses are mutually exclusive.

See Also:

• Oracle AI Database SQL Language Reference for the complete CREATE MLE MODULE syntax

• Oracle AI Database Development Guide for more information about using the IF [NOT] EXISTS syntax

Example 3-1 Creating a JavaScript Module in the Database

This example demonstrates the creation of an MLE module and the export of a simple JavaScript function.

CREATE MLE MODULE IF NOT EXISTS po_module LANGUAGE JAVASCRIPT AS

/**
* get the value of all line items in an order
* @param {array} lineItems - all the line items in a purchase order
* @returns {number} the total value of all line items in a purchase order
*/
export function orderValue(lineItems) {
    
    return lineItems
        .map( x => x.Part.UnitPrice * x.Quantity )
        .reduce( 
            (accumulator, currentValue) => accumulator + currentValue, 0
        );
}
/

The first line of this code block specifies the JavaScript module name as po_module. The remaining lines define the actual JavaScript code. Note that in line with the ECMAScript standard, the export keyword indicates the function to be exported to potential callers of the module. MLE accepts code adhering to the ECMAScript 2023 standard.

Storing JavaScript Code in Databases Using Single-Byte Character Sets

Character set standards and things to remember when using a single-byte character set with MLE.

JavaScript is encoded in Unicode. The Unicode Standard is a character encoding system that defines every character in most of the spoken languages in the world. It was developed to overcome limitations of other character-set encodings.

Oracle recommends creating databases using the AL32UTF8 character set. Using the AL32UTF8 character set in the database ensures the use of the latest version of the Unicode Standards and minimizes the potential for character-set conversion errors.

In case your database still uses a single-byte character set such as US7ASCII, WE8ISO8859-n, or WE8MSWIN1252, you must be careful not to use Unicode features in MLE JavaScript code. This is no different than handling other types of input data with such a database.

See Also:

Oracle AI Database Globalization Support Guide for more details about the Unicode Standard

Code Analysis

JavaScript syntax errors are flagged when an MLE module is created, but linting tool of your choice should still be used to perform analysis before executing the CREATE MLE MODULE command.

When creating MLE modules in the database, you should use a well-established toolchain in the same way other JavaScript projects are governed. In this sense, the call to CREATE MLE MODULE can be considered a deployment step, similar to deploying a server application. Code checking should be performed during a build step, for example, by a continuous integration and continuous deployment (CI/CD) pipeline, prior to deployment.

If a module is created using CREATE MLE MODULE that includes syntax errors in the JavaScript code, the module will be created but it will exist in an invalid state. This also applies to functions created using inline call specifications. However, this check does not apply to any SQL statements called within the module, so separate testing should still be performed to ensure that the code works as expected.

It is considered an industry best practice to process code with a tool called a linter before checking it into a source-code repository. As with any other development project, you are free to choose the best option for yourself and your team. Some potential options include ESLint, JSHint, JSLint, and others that perform static code analysis to flag syntax errors, bugs, or otherwise problematic code. They can also be used to enforce a certain coding style. Many integrated development environments (IDEs) provide linting as a built-in feature, invoking the tool as soon as a file is saved to disk and flagging any issues.

In addition to executing linting dynamically, it is possible to automate the code analysis using highly automated DevOps environments to invoke linting as part of a build pipeline. This step usually occurs prior to submitting the JavaScript module to the database.

The aim is to trap as many potential issues as possible before they can produce problems at runtime. Unit tests can help further mitigate these risks and their inclusion into the development process have become an industry best practice. Regardless of the method you choose, the code analysis step occurs prior to submitting the JavaScript module to the database.