Getting Started

Oracle Database MLE allows Oracle Database users to write extensions in JavaScript or TypeScript. Specifically, MLE supports ECMAScript Version 8 (i.e. ECMAScript 2017). Extensions deployed into the database run inside database server processes. As a result, extensions don't suffer inter-process communication overhead when interacting with the SQL query engine.

Hello World

We start with a simple example that deploys a JavaScript function in Oracle Database MLE that returns "Hello World".
The Oracle Database MLE requires all JavaScript extensions to be implemented as JavaScript modules, following practice that is common to JavaScript developers. Oracle Database MLE supports modules written as UMD modules. It is the most common pattern for writing modules and can be generated by a variety of tools (like, e.g., browserify). However, the simplest way to write a module that can be loaded by Oracle Database MLE is to use Common-JS and to specify the functions that should be callable from the Oracle Database as properties of module.exports as illustrated here:

module.exports.helloworld = function () {
  return "Hello World";
}

We save this file as helloworld.js

The next step is to provide a type declaration for all exported functions. Since we only export one function in our example, we provide a specification for the input and output types of helloworld. This specification is written as a TypeScript declaration file and states that the function has no inputs and produces a string as output.

export function helloworld() : string;

We save this file as helloworld.d.ts

The specification is needed to bridge the gap between the statically-typed SQL world of the database and the dynamically typed JavaScript world. With an implementation and a type specification we can then use the deployment tool dbjs to deploy the script into the database.

$ dbjs deploy helloworld.js -u myuser -p mypasswd -c mydb:1521/globaldbname

The dbjs utility is available in both, the MLE Docker container as well as the VirtualBox appliance. If you are using MLE in a Docker container, the best practise is to mount a volume that contains the JavaScript files from the host.

To use this script, we connect to the database with our favorite DB connector (e.g., SQL*Plus) and use a query that calls the function helloworld.

select helloworld() from dual;

For more details on how to use more sophisticated JavaScript and reuse existing packages see our description on supported JavaScript modules. Also see the conversion matrix for information on which JavaScript type to expect for which database type and what to return for which database type.

JavaScript Modules

A JavaScript module (also module for short) is a unit that can be deployed into the database. A modules consists of

  1. the JavaScript source code packed such that it can be loaded by a loader for UMD modules (this includes CommonJS and AMD modules),
  2. call specifications for all functions exported by the JavaScript source code.

When writing scripts by hand we recommend using CommonJS's module.exports to export all functions to the database. When using tools like browserify or writing the extension directly in TypeScript, an easier way is to instruct these tools to generate UMD modules directly.

There are different ways to create a JavaScript module inside the database. We recommend to use dbjs to load a JavaScript module from a JavaScript source (UMD-loadable module) and a TypeScript specification of the call specification (stored in a TypeScript declaration file).

JavaScript Type

A JavaScript type is one of the types known to JavaScript, i.e., the result of applying the JavaScript builtin typeof to a value. Possible JavaScript types are number, string, object, Symbol, and undefined.

Call Specification

A Call Specification provides types for the parameters and the result of a JavaScript function that can be called from the database. It is needed to bridge the dynamically-typed JavaScript world and the statically-typed SQL world.