Oracle DBMS_MLE
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose This Multi-Lingual Engine (MLE) package allows users to execute JavaScript code inside the Oracle Database and exchange data seamlessly between PL/SQL and JavaScript. The JavaScript code itself can execute PL/SQL and SQL through built-in JavaScript modules.

JavaScript data types are automatically mapped to Oracle Database data types and vice versa.

With the DBMS_MLE package, developers can write their data processing logic in JavaScript.

The runtime state for MLE execution is encapsulated in execution contexts that users can explicitly create and destroy. Users can also export values from PL/SQL to MLE, and import values from MLE back into PL/SQL.
AUTHID CURRENT_USER
Data Types SUBTYPE context_handle_t IS RAW(16); /* type for context handle */

SUBTYPE language_t IS VARCHAR2(64); /* type for language identifier */
TYPE languages_t IS TABLE OF language_t; /* table of language identifiers */

TYPE error_frame_t IS RECORD (
func   VARCHAR2(4000),
source VARCHAR2(4000),
line   PLS_INTEGER,
col    PLS_INTEGER); /* type for each error frame returned by get_(ctx_)error_stack */
Dependencies
DBMS_SYS_MLE    
Documented Yes
Exceptions
Error Code Reason
ORA-04108 The current container, the current user, or the currently enabled roles are different from those in effect at the time of context creation
ORA-04157 The value of the passed property_name is NULL or an empty string
First Available 20c
Security Model Owned by SYS with EXECUTE granted to PUBLIC

EXECUTE DYNAMIC MLE system privilege required for some functionality
Source {ORACLE_HOME}/rdbms/admin/
Subprograms
 
CREATE_CONTEXT (new 21c)
Creates an MLE context for executing snippets in any MLE language dbms_mle.create_context RETURN context_handle_t;
See Demo At Page Bottom
 
DISABLE_CTX_STDERR (new 21c)
Disables stderr stream of the specified MLE context, so that future writes to stderr are discarded dbms_mle.disable_ctx_stderr(context_handle IN context_handle_t);
DECLARE
 ctxh dbms_mle.context_handle_t;
BEGIN
  ctxh := dbms_mle.create_context;
  dbms_mle.disable_ctx_stderr(ctxh);
  dbms_mle.disable_ctx_stdout(ctxh);

  dbms_mle.disable_stderr;
  dbms_mle.disable_stdout;

  dbms_mle.drop_context(ctxh);
END;
/

PL/SQL procedure successfully completed.
 
DISABLE_CTX_STDOUT (new 21c)
Disables stdout stream of the specified MLE context, so that future writes to stdout are discarded dbms_mle.disable_ctx_stdout(context_handle IN context_handle_t);
See DISABLE_CTX_STDERR Demo Above
 
DISABLE_STDERR (new 21c)
Disables the stderr stream of all MLE contexts, so that future writes to stderr are discarded dbms_mle.disable_stderr;
See DISABLE_CTX_STDERR Demo Above
 
DISABLE_STDOUT (new 21c)
Disables the stdout stream of all MLE contexts, so that future writes to stdout are discarded dbms_mle.disable_stdout;;
See DISABLE_CTX_STDERR Demo Above
 
DROP_CONTEXT (new 21c)
Drop an MLE context created using CREATE_CONTEXT dbms_mle.drop_context(context_handle IN context_handle_t);
See Demo At Page Bottom
 
EVAL (new 21c)
Executes the given MLE language code within the context identified by the context handle

Overload 1
dbms_mle.eval(
context_handle IN            context_handle_t,
language_id    IN            language_t,
source         IN            CLOB,
result         IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
See Demo At Page Bottom
Overload 2 dbms_mle.eval(
context_handle IN            context_handle_t,
language_id    IN            language_t,
source         IN            VARCHAR2,
result         IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
TBD
Overload 3 dbms_mle.eval(
context_handle IN context_handle_t,
language_id    IN language_t,
source         IN CLOB);
TBD
Overload 4 dbms_mle.eval(
context_handle IN context_handle_t,
language_id    IN language_t,
source         IN VARCHAR2);
TBD
 
EXPORT_CHAR (new 21c)
Assigns the given value, with appropriate conversion, to the named property in the MLE context. The property is created if it is not already
present.
dbms_mle.export_char(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN CHAR CHARACTER SET ANY_CS);
TBD
 
EXPORT_RAW (new 21c)
Assigns the given value, with appropriate conversion, to the named property in the MLE context. The property is created if it is not already
present.
dbms_mle.export_raw(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN RAW);
TBD
 
EXPORT_TO_MLE (new 21c)
Assigns the given value, with appropriate conversion, to the named property in the MLE context. The property is created if it is not already
present.

Overload 1
dbms_mle.export_to_mle(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN BINARY_INTEGER);
See Demo At Page Bottom
Overload 2 dbms_mle.export_to_mle(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN BINARY_DOUBLE);
TBD
Overload 3 dbms_mle.export_to_mle(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN BINARY_FLOAT);
TBD
Overload 4 dbms_mle.export_to_mle(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN BLOB);
TBD
Overload 5 dbms_mle.export_to_mle(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN BOOLEAN);
TBD
Overload 6 dbms_mle.export_to_mle(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN CLOB CHARACTER SET ANY_CS);
TBD
Overload 7 dbms_mle.export_to_mle(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN DATE);
TBD
Overload 8 dbms_mle.export_to_mle(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN DSINTERVAL_UNCONSTRAINED);
TBD
Overload 9 dbms_mle.export_to_mle(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN NUMBER);
TBD
Overload 10 dbms_mle.export_to_mle(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN TIMESTAMP_TZ_UNCONSTRAINED);
TBD
Overload 11 dbms_mle.export_to_mle(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN TIMESTAMP_UNCONSTRAINED);
TBD
Overload 12 dbms_mle.export_to_mle(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN VARCHAR2 CHARACTER SET ANY_CS);
TBD
Overload 13 dbms_mle.export_to_mle(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN YMINTERVAL_UNCONSTRAINED);
TBD
 
EXPORT_UROWID (new 21c)
Assigns the given value, with appropriate conversion, to the named property in the MLE context. The property is created if it is not already
present.
dbms_mle.export_urowid(
context_handle IN context_handle_t,
property_name  IN VARCHAR2,
property_value IN UROWID);
TBD
 
GET_AVAILABLE_LANGUAGES (new 21c)
Returns the set of available languages

As of the initial release of 21c the only language is JAVASCRIPT
dbms_mle.get_available_languages RETURN languages_t;
DECLARE
 ctxh  dbms_mle.context_handle_t;
 langs dbms_mle.languages_t;
 i     INTEGER;
BEGIN
  ctxh := dbms_mle.create_context;
  langs := dbms_mle.get_available_languages;

  FOR i IN 1 .. langs.COUNT LOOP
    dbms_output.put_line(langs(i));
  END LOOP;

  dbms_mle.drop_context(ctxh); 
END;
/
JAVASCRIPT

PL/SQL procedure successfully completed.
 
GET_CTX_ERROR_STACK (new 21c)
Returns the MLE language stack trace for the most recent application error in the given execution context dbms_mle.get_ctx_error_stack(context_handle IN context_handle_t)
RETURN error_frames_t;
TBD
 
IMPORT_CHAR (new 21c)
Retrieves the value of the named property from the MLE context and converts it to the requested PL/SQL type dbms_mle.import_char(
context_handle IN  context_handle_t,
property_name  IN  VARCHAR2,
target         OUT CHAR CHARACTER SET ANY_CS);
TBD
 
IMPORT_FROM_MLE (new 21c)
Retrieves the value of the named property from the MLE context and converts it to the requested PL/SQL type

Overload 1
dbms_mle.import_from_mle(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY BINARY_INTEGER);
See Demo At Page Bottom
Overload 2 dbms_mle.import_from_mle(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY BINARY_DOUBLE);
TBD
Overload 3 dbms_mle.import_from_mle(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY BINARY_FLOAT);
TBD
Overload 4 dbms_mle.import_from_mle(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY BLOB);
TBD
Overload 5 dbms_mle.import_from_mle(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY BOOLEAN);
TBD
Overload 6 dbms_mle.import_from_mle(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY CLOB CHARACTER SET ANY_CS);
TBD
Overload 7 dbms_mle.import_from_mle(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY DATE);
TBD
Overload 8 dbms_mle.import_from_mle(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY DSINTERVAL_UNCONSTRAINED);
TBD
Overload 9 dbms_mle.import_from_mle(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY NUMBER);
TBD
Overload 10 dbms_mle.import_from_mle(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY TIMESTAMP_TZ_UNCONSTRAINED);
TBD
Overload 11 dbms_mle.import_from_mle(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY TIMESTAMP_UNCONSTRAINED);
TBD
Overload 12 dbms_mle.import_from_mle(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS);
TBD
Overload 13 dbms_mle.import_from_mle(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY YMINTERVAL_UNCONSTRAINED);
TBD
 
IMPORT_RAW (new 21c)
Retrieves the value of the named property from the MLE context and converts it to the requested PL/SQL type dbms_mle.import_raw(
context_handle IN  context_handle_t,
property_name  IN  VARCHAR2,
target         OUT RAW);
TBD
 
IMPORT_UROWID (new 21c)
Retrieves the value of the named property from the MLE context and converts it to the requested PL/SQL type dbms_mle.import_urowid(
context_handle IN         context_handle_t,
property_name  IN         VARCHAR2,
target         OUT NOCOPY UROWID);
TBD
 
SET_CTX_STDERR (new 21c)
Redirect the stderr stream of the MLE context to the given CLOB dbms_mle.set_ctx_stderr(
context_handle IN            context_handle_t,
sink           IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
TBD
 
SET_CTX_STDERR_TO_DBMS_OUTPUT (new 21c)
Redirect the stderr stream of the MLE context to DBMS_OUTPUT dbms_mle.set_ctx_stderr_to_dbms_output(context_handle IN context_handle_t);
TBD
 
SET_CTX_STDOUT (new 21c)
Redirect the stdout stream of the MLE context to the given CLOB dbms_mle.set_ctx_stdout(
context_handle IN            context_handle_t,
sink           IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
TBD
 
SET_CTX_STDOUT_TO_DBMS_OUTPUT (new 21c)
Redirect the stdout stream of the MLE context to DBMS_OUTPUT dbms_mle.set_ctx_stdout_to_dbms_output(context_handle IN context_handle_t);
TBD
 
SET_STDERR (new 21c)
Redirect the stderr stream of all MLE contexts to the given CLOB dbms_mle.set_stderr(sink IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
TBD
 
SET_STDERR_TO_DBMS_OUTPUT (new 21c)
Redirect the stderr stream of all MLE contexts to DBMS_OUTPUT dbms_mle.set_stderr_to_dbms_output;
exec dbms_mle.set_stderr_to_dbms_output;
 
SET_STDOUT (new 21c)
Redirect the stdout stream of all MLE contexts to the given CLOB dbms_mle.set_stdout(sink IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
TBD
 
SET_STDOUT_TO_DBMS_OUTPUT (new 21c)
Redirect the stdout stream of all MLE contexts to DBMS_OUTPUT dbms_mle.set_stdout_to_dbms_output;
exec dbms_mle.set_stdout_to_dbms_output;
 
Demo
The demo, at right, is based on the demo in the Oracle docs set serveroutput on;

DECLARE
 ctxh     dbms_mle.context_handle_t;
 source   CLOB;
 greeting dbms_id;
BEGIN
  ctxh := dbms_mle.create_context();                    -- create MLE execution context
  dbms_mle.export_to_mle(ctxh, 'person', 'World');      -- export value from PL/SQL

  source := q'~
  var bindings = require("mle-js-bindings");
  var person = bindings.importValue("person"); // Imp. value prev. exported with PL/SQL
  var greeting = "Hello, " + person + "!";
  bindings.exportValue("greeting", greeting);  // Export value to PL/SQL
  ~';

  dbms_mle.eval(ctxh, 'JAVASCRIPT', source);            -- eval src in execution context
  dbms_mle.import_from_mle(ctxh, 'greeting', greeting); -- Imp. previously exported val
  dbms_output.put_line('Greetings from MLE: ' || greeting);
  dbms_mle.drop_context(ctxh);
END;
/
Greetings from MLE: Hello, World!

Related Topics
Built-in Functions
Built-in Packages
Database Security
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx