Additional Options for Providing JavaScript Code to MLE

The JavaScript source code of an MLE module can be specified inline with PL/SQL but can also be provided using a BFILE, BLOB, or CLOB, in which case the source file must be UTF8 encoded.

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.

Another option available is to use the mle create-module command with SQLcl to load a JavaScript file into the database. Because the module is being created directly from a JavaScript file, there is no need to sandwich the JavaScript code between DDL statements. This means regular programming steps such as linting, local unit testing, and the use of formatting tools can be performed more conveniently. The use of SQLcl can be particularly well suited for Continuous Integration (CI) pipelines.

Example 3-4 Providing JavaScript Source Code Using a BFILE

In this example, JS_SRC_DIR is a database directory object mapping to a location on the local file system containing the module's source code in a file called myJavaScriptModule.js. When loading the file from the directory location, MLE stores the source code in the dictionary. Subsequent calls to the MLE module will not cause the source code to be refreshed from the disk. If there is a new version of the module stored in myJavaScriptModule.js, it must be deployed using another call to CREATE OR REPLACE MLE MODULE.

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

Example 3-5 Providing JavaScript Source Code Using a CLOB

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

As an alternative, you also have the option of using JavaScript source code that is stored in a table. This example variation assumes your schema features a table named javascript_src containing the JavaScript source code in column src along with some additional metadata. The following statement fetches the CLOB and creates the module.

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'
)
/

Staging tables like this can be found in environments where Continuous Integration (CI) pipelines are used to deploy JavaScript code to the database.

Example 3-6 Providing JavaScript Source Code Using SQLcl

In this example, the module's source code is in a file called myJavaScriptModule.js, which is located in a local file directory folder called tmp. The following command creates a module called my_js_mod, replacing the module if it exists or creating one if it does not.

mle create-module -
  -language javascript -
  -replace -
  -filename /tmp/myJavaScriptModule.js -
  -module-name my_js_mod

For more information about SQLcl MLE commands and their syntax, see Oracle SQLcl User’s Guide.