Skip to content

Dynamic MLE Execution

Introduction

Dynamic MLE Execution refers to the direct execution of a JavaScript or Python code snippets stored as character string without having to deploy the code as a module. This functionality is made available via the package DBMS_MLE, and is analogous to the DBMS_SQL package in Oracle, which allows dynamic execution of arbitrary pieces of PL/SQL code.

Hello World

The example below demonstrates how to dynamically execute a JavaScript code snippet:

SET SERVEROUTPUT ON;
DECLARE
  script CLOB;
  hdl DBMS_MLE.SCRIPT_HANDLE_T;
BEGIN
  script := 'console.log("Hello world!");'; -- JS code as CLOB
  hdl := DBMS_MLE.CREATE_SCRIPT('JS', script); -- Create script
  DBMS_MLE.EXECUTE_SCRIPT(hdl); -- Execute script
  DBMS_MLE.DROP_SCRIPT(hdl); -- Drop script
END;
/
Output:
Hello world!

The first step in executing a dynamic code snippet is to create a script: the function DBMS_MLE.CREATE_SCRIPT takes two mandatory inputs - the JavaScript code snippet to be executed, stored in a CLOB or VARCHAR2 variable, as well as the language of this script as VARCHAR2 (currently supported lanuguage identifiers are DBMS_MLE.JS (or simply 'JS') and DBMS_MLE.PYTHON/'PYTHON') - and returns a handle (of type DBMS_MLE.SCRIPT_HANDLE_T) to the created script. We can now execute this script by passing the returned handle to DBMS_MLE.EXECUTE_SCRIPT. For the example above, this will print Hello world! to the database console. Finally, since we no longer need the script, we drop it by invoking DBMS_MLE.DROP_SCRIPT.

Binding Variables

Dynamic MLE scripts can operate on bind variables as the following example demonstrates:

SET SERVEROUTPUT ON;
DECLARE
  script CLOB;
  hdl DBMS_MLE.SCRIPT_HANDLE_T;
BEGIN
  script := 'console.log("Hello " + mle.binds.person + "!");'; -- Use bind variable 'person'
  hdl := DBMS_MLE.CREATE_SCRIPT('JS', script);
  DBMS_MLE.BIND_VARIABLE(hdl, 'person', 'Larry'); -- bind value 'Larry' to 'person'
  DBMS_MLE.EXECUTE_SCRIPT(hdl);
  DBMS_MLE.DROP_SCRIPT(hdl);
END;
/
Output:
Hello Larry!

Values are bound to variables via the DBMS_MLE.BIND_VARIABLE procedure, which accepts as input a script handle (hdl), a variable name ('person'), and the value to bind to it ('Larry'). The bound value is then available to the script via the corresponding variable in the mle_binds object (mle.binds.person for the script above). Note that currently only the types shown below can be used for binding.

For a script using bind variables, all variables must be bound to a value before the script is executed; the behavior of the script is undefined otherwise. Once all variables are bound, a script can be executed several times if needed.

Retrieving Results

Just as IN parameters are used to pass values to a dynamic MLE script, OUT parameters can be used to retrieve values set during script execution:

SET SERVEROUTPUT ON;
DECLARE
  script CLOB;
  hdl DBMS_MLE.SCRIPT_HANDLE_T;
  res VARCHAR2(200);
BEGIN
  script := 'mle.binds.greeting = "Hello " + mle.binds.person + "!";';  -- Set out parameter value
  hdl := DBMS_MLE.CREATE_SCRIPT('JS', script);
  DBMS_MLE.BIND_VARIABLE(hdl, 'person', 'Larry');
  DBMS_MLE.EXECUTE_SCRIPT(hdl); -- Execute script
  DBMS_MLE.VARIABLE_VALUE(hdl, 'greeting', res); -- Retrieve out parameter value
  DBMS_MLE.DROP_SCRIPT(hdl);
  DBMS_OUTPUT.PUT_LINE('Result: ' || res);
END;
/
Output:
Result: Hello Larry!

In the example above, the JavaScript code snippet sets the value of the OUT parameter 'greeting' by assigning it to the corresponding field (mle.binds.greeting) of the object mle.binds. The value is then retrieved using the procedure DBMS_MLE.VARIABLE_VALUE by passing it the script handle (hdl), the variable name ('greeting'), and the PL/SQL variable that the value is read into (res). As with IN parameters, only the types shown below can be used for the value.

DBMS_MLE.BIND_VARIABLE does best-effort implicit conversion from the dynamic type of an object in JavaScript to the PL/SQL type required (VARCHAR2 or SIMPLE_INTEGER). In case of SIMPLE_INTEGER, this potentially includes rounding and/or truncation of numeric values, while in the case of VARCHAR2, toString() is applied as needed.

A given SCRIPT_HANDLE_T can be executed several times using EXECUTE_SCRIPT. For each execution, new values can be bound using BIND_VARIABLE. After each execution, out parameter values can be retrieved using VARIABLE_VALUE.

Calling out to the Database

As with deployed modules, dynamic MLE scripts can call out to the database using SQL. Callouts behave the same way in both cases, with the only difference being the name of the SQL driver for dynamic MLE scripts (mle.sql):

SET SERVEROUTPUT ON;
DECLARE
  script CLOB;
  hdl DBMS_MLE.SCRIPT_HANDLE_T;
BEGIN
  script :=
    'for (var row of mle.sql.execute("SELECT EMPNO, ENAME FROM EMP").rows) {' ||
    '  console.log("[" + row[0] + ", " + row[1] + "]")' ||
    '}';
  hdl := DBMS_MLE.CREATE_SCRIPT('JS', script);
  DBMS_MLE.EXECUTE_SCRIPT(hdl);
  DBMS_MLE.DROP_SCRIPT(hdl);
END;
/
Output:
[7369, SMITH]
[7499, ALLEN]
...

Python Example

The previous example can also be written in Python:

SET SERVEROUTPUT ON;
DECLARE
  script CLOB;
  hdl DBMS_MLE.SCRIPT_HANDLE_T;
BEGIN
  script := q'~ 
for row in mle.sql.execute("SELECT EMPNO, ENAME FROM EMP"):
    print("[" + str(row[0]) + ", " + str(row[1]) + "]")
~';
  hdl := DBMS_MLE.CREATE_SCRIPT('PYTHON', script);
  DBMS_MLE.EXECUTE_SCRIPT(hdl);
  DBMS_MLE.DROP_SCRIPT(hdl);
END;
/
Output:
[7369.0, SMITH]
[7499.0, ALLEN]
...

Life Cycle

The following flow chart summarizes the life cycle of a dynamic MLE script:

alt text

Supported Types

The following types are currently supported for dynamic script execution in package DBMS_MLE:

Procedure Supported Data Types
CREATE_SCRIPT VARCHAR2, CLOB
BIND_VARIABLE SIMPLE_INTEGER, VARCHAR2
VARIABLE_VALUE SIMPLE_INTEGER, VARCHAR2

Security Levels

Once created, script handles can be handed over to other users. DBMS_MLE.CREATE_SCRIPT accepts an additional optional parameter, security_level, which provides fine-grained control over how other users may use this handle.

By default, this level is set to 1, which means that only the user who created the script may execute/drop the script and set the values of IN binds; all other users can only read OUT variable values from the handle. Setting security_level to 2 disables read access as well. If even this read access should be prohibited, one can create a script with security level 2. The laxest security_level is 0, which implies that all users have full access to the script handle; extreme care should be exercised when using this.

SET SERVEROUTPUT ON;
DECLARE
  script CLOB;
  hdl DBMS_MLE.SCRIPT_HANDLE_T;
  res VARCHAR2(200);
BEGIN
  script := 'mle.binds.greeting = "Hello " + mle.binds.person + "!";';
  hdl := DBMS_MLE.CREATE_SCRIPT('JS', script, 2);
  DBMS_MLE.BIND_VARIABLE(hdl, 'person', 'Larry');
  DBMS_MLE.EXECUTE_SCRIPT(hdl);
  DBMS_MLE.VARIABLE_VALUE(hdl, 'greeting', res);
  DBMS_MLE.DROP_SCRIPT(hdl);
  DBMS_OUTPUT.PUT_LINE('Result: ' || res);
END;
/
Output:
Result: Hello Larry!

The following table summarizes the three security levels.

Level Description
0 Allows all operations on the created script without any security checks.
1 Requires that the effective user id of the caller to BIND_VARIABLE, EXECUTE and DROP_SCRIPT operations on a script must be the same as the user id of the user who created the script.
2 Requires that the effective user id of the caller to BIND_VARIABLE, EXECUTE, VARIABLE_VALUE, and DROP_SCRIPT operations on a script must be the same as the user id of the user who created the script.

Advanced Usage: the DBMS_SYS_MLE package

A user who has access to DBMS_SYS_MLE (grant with care!) can also create scripts on behalf of other users. This is useful in a situation where an application constructs a script from a given user input (e.g. from a GUI) and then hands the script handle back to the user for execution.

In the example below, user power_user is first granted the necessary access to DBMS_SYS_MLE and then creates a script on behalf of user SCOTT and directly uses it on their behalf.

GRANT EXECUTE ON DBMS_SYS_MLE to power_user;
-- power_user creating a script on behalf of user scott and executing it
SET SERVEROUTPUT ON;
DECLARE
  script CLOB;
  hdl DBMS_MLE.SCRIPT_HANDLE_T;
BEGIN
  script := 'console.log("Hello Scott!");';
  hdl := DBMS_SYS_MLE.CREATE_SCRIPT_AS_USER('JS', script, 'SCOTT');
  DBMS_MLE.EXECUTE_SCRIPT(hdl);
END;
/
Output:
Hello Scott!