Skip to content

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., webpack, 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.

The JavaScript source can be deployed via the newly integrated CREATE SOURCE statement.

For our helloworld example, we create a file hello.sql with the following content:

CREATE OR REPLACE JAVASCRIPT SOURCE NAMED "mymodule.js" AS
module.exports.hello = function (who) {
  return "Hello " + who + "!"; }
/

We run this script in the scott/tiger schema as follows:

sqlplus scott/tiger@ORCLPDB1 @hello.sql

Note that ORCLPDB1 needs to be replaced if a container other than ORCLPDB1 was choosen at container startup.

We now need provide a type declaration for the hello function via the CREATE FUNCTION statement. This specification is needed to bridge the gap between the statically-typed SQL world of the database and the dynamically typed JavaScript world. This call specification can be created via SQL*Plus as follows:

CREATE OR REPLACE FUNCTION hello(who IN varchar2) 
RETURN STRING AS LANGUAGE JAVASCRIPT 
NAME 'mymodule\.js.hello(who string) return string';
/

Note that to create or replace a schema object containing the JavaScript/MLE-Language source in your own schema, you must have the CREATE FUNCTION system privilege.

We can now run a query that calls the function hello and pass a string as argument:

select hello('world') from dual;

The output is as follows:

HELLO('WORLD')
--------------------------------------------------------------------------------
Hello world!

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 webpack 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 the CREATE JAVASCRIPT SOURCE statement to load a JavaScript module from a JavaScript source (UMD-loadable module) and provide the call specification via CREATE FUNCTION or CREATE PROCEDURE statements.

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.

Dynamic Script Execution

For JavaScript code that is generated a runtime, the Dyamic JavaScript Execution feature can be used. This allows the execution of JavaScript code without requiring an upfront deployment step.