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.

Topics

Those variables and functions not exported are considered private in the module. Example 3-3 demonstrates the use of both public and private functions in an MLE JavaScript module.

An ECMAScript module can import other ECMAScript modules using import statements or dynamic import calls. This functionality is present in MLE as well. Complementary metadata to MLE modules is provided in MLE environments.

Note that console output in MLE is facilitated using the console object. By default, anything written to console.log() is routed to DBMS_OUTPUT and will end up on the screen.

JavaScript code like that in Example 3-1 cannot be accessed from SQL or PL/SQL without the help of call specifications. For now, you can think of a call specification as a PL/SQL program unit (function, procedure, or package) where its PL/SQL body is replaced with a reference to the JavaScript module and function, as shown in Example 3-2. For more information about call specifications, see MLE JavaScript Functions.

CREATE OR REPLACE FUNCTION order_value(
    p_line_items JSON
) RETURN NUMBER AS
MLE MODULE po_module
SIGNATURE 'orderValue';
/

SELECT
    po.po_document.PONumber,
    order_value(po.po_document.LineItems[*]) order_value
FROM
    j_purchaseorder po;

PONUMBER   ORDER_VALUE
---------- -----------
1600             279.3
672              359.5

CREATE OR REPLACE MLE MODULE po_module LANGUAGE JAVASCRIPT AS

/**
* calculate the value of a given line item. Factored out of the public 
* function to allow for currency conversions in a later step
* @param {number} unitPrice - the price of a single article
* @param {number} quantity - the quantity of articles ordered
* @returns {number} the monetary value of the line item
*/
function lineItemValue(unitPrice, quantity) {
    return unitPrice * quantity;
}

/**
* 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
*/
function orderValue(lineItems) {
    
    return lineItems
                .map( x => lineItemValue(x.Part.UnitPrice, x.Quantity) )
                .reduce( 
                    (accumulator, currentValue) => accumulator + currentValue, 0 
                );
}

export { orderValue }
/

Creating MLE modules using the BFILE clause can cause problems with logical replication such as GoldenGate. In order for the DDL command to succeed on the target database, the same directory must exist on the target database. Furthermore, the same JavaScript file must be present in this directory. Failure to adhere to these conditions will cause the call to create the MLE module on the target database to fail.

A BLOB or a CLOB can also be used to create an MLE module as an alternative to using a BFILE. Example 3-5 shows how to create a JavaScript module using a CLOB. If you prefer to use a BLOB, the syntax is the same but the value of the BLOB will differ from that of a CLOB.

CREATE MLE MODULE mod_from_bfile
LANGUAGE JAVASCRIPT
USING BFILE(JS_SRC_DIR,'myJavaScriptModule.js');
/

CREATE OR REPLACE MLE MODULE mod_from_clob_inline
LANGUAGE JAVASCRIPT USING CLOB (
    SELECT q'~
    export function clob_hello(who){
        return `hello, ${who}`;
}
~')
/

CREATE OR REPLACE MLE MODULE mod_from_clob_table
LANGUAGE JAVASCRIPT USING CLOB (
    SELECT src
    FROM javascript_src
    WHERE 
        id = 1 AND 
        commit_hash = 'ac1fd40'
)
/

Both kinds of metadata are purely informational and do not influence the behavior of MLE. They are stored alongside the module in the data dictionary.

The VERSION flag can be used as an internal reminder about what version of the code is deployed. The information stored in the VERSION field allows developers and administrators to identify the code in the version control system.

The format for JSON metadata is not bound to a schema; anything useful or informative can be added by the developer. In case the MLE module is an aggregation of sources created by a tool such as rollup.js or webpack, it can be useful to store the associated package-lock.json file alongside the module.

The metadata field can be used to create a software bill of material (SBOM), allowing security teams and administrators to track information about deployed packages, especially in the case where third-party modules are used.

Tracking dependencies and vulnerabilities in the upstream repository supports easier identification of components in need of update after security vulnerabilities have been reported.

CREATE OR REPLACE MLE MODULE version_mod
 LANGUAGE JAVASCRIPT
 VERSION '1.0.0.1.0' 
AS
export function sq(num) {
  return num * num;
}
/

ALTER MLE MODULE version_mod
SET METADATA USING CLOB 
(SELECT 
  '{
    "name": "devel",
    "lockfileVersion": 2,
    "requires": true,
    "packages": {}
  }'
)
/

The DROP statement specifies the name, and optionally the schema of the module to be dropped. If a schema is not specified, the schema of the current user is assumed.

Attempting to drop an MLE module that does not exist causes an error to be thrown. In cases where this is not desirable, the IF EXISTS clause can be used. The DROP MLE MODULE command is silently skipped if the indicated MLE module does not exist.

DROP MLE MODULE unused_mod;

DROP MLE MODULE IF EXISTS unused_mod;

The ALTER MLE MODULE statement specifies the name, and optionally the schema of the module to be altered. If the module name is not prefixed with a schema, the schema of the current user is assumed.

ALTER MLE MODULE change_mod
  SET METADATA USING CLOB(SELECT'{...}');

Built-in modules are not deployed to the database as user-defined MLE modules, but are included as part of the MLE runtime. In particular, MLE provides the following three built-in JavaScript modules:

• mle-js-oracledb is the JavaScript MLE SQL Driver.

• mle-js-bindings provides functionality to import and export values from the PL/SQL engine.

• mle-js-plsqltypes provides definitions for the PL/SQL wrapper types. For example, JavaScript types that wrap PL/SQL and SQL types like OracleNumber.

• mle-js-fetch provides a partial Fetch API polyfill, allowing developers to invoke external resources.

• mle-encode-base64 contains code to work with base64-encoded data.

• mle-js-encodings provides functionality to handle text in UTF-8 and UTF-16 encodings.

• mle-js-plsql-ffi provides functionality to handle PL/SQL packages, functions, and procedures as JavaScript objects.

These modules can be used to interact with the database and provide type conversions between the JavaScript engine and database engine.

Topics