Oracle | DBMS

Oracle DBMS_MLE Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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.

-- The flow of dynamic MLE execution will typically look like this:

-- ---------------- -- | create_context | -- ---------------- -- | -- | -- v -- | -- ---------------->| -- | |----- -- | v | -- | --------------- -- |-------->| export_to_mle | -- | ^ --------------- -- | | | | -- | ----------- | | -- | |<----- -- | | -- | v -- | ------ -- |------------->| eval | -- | ------ -- | | -- | | -- | v -- | ---------------- -- | --->| import_from_mle | -- | | ---------------- -- | | | -- | -------------| -- | | -- -----------------| -- | -- v -- -------------- -- | drop_context | -- --------------

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_LOB	PLITBLM	UTL_IDENT
DBMS_SYS_MLE

Documented Yes: Packages and Types Reference

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

Object Privilege The COLLECT DEBUG INFO privilege controls whether a user, who does not own a module, but has the EXECUTE privilege can debug the module.

Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);

Security Model Owned by SYS with EXECUTE granted to PUBLIC

EXECUTE DYNAMIC MLE system privilege is required for some functionality

Source {ORACLE_HOME}/rdbms/admin/dbmsmle.sql
{ORACLE_HOME}/rdbms/admin/prvtmle.plb

Subprograms

CREATE_CONTEXT	EXPORT_RAW	SET_CTX_STDERR_TO_DBMS_OUTPUT
DISABLE_CTX_STDERR	EXPORT_TO_MLE	SET_CTX_STDOUT
DISABLE_CTX_STDOUT	EXPORT_UROWID	SET_CTX_STDOUT_TO_DBMS_OUTPUT
DISABLE_DEBUGGING	GET_AVAILABLE_LANGUAGES	SET_ICS_STDERR
DISABLE_ICS_STDERR	GET_CTX_ERROR_STACK	SET_ICS_STDERR_TO_DBMS_OUTPUT
DISABLE_ICS_STDOUT	GET_ERROR_STACK	SET_ICS_STDOUT
DISABLE_STDERR	IMPORT_CHAR	SET_ICS_STDOUT_TO_DBMS_OUTPUT
DISABLE_STDOUT	IMPORT_FROM_MLE	SET_STDERR
DROP_CONTEXT	IMPORT_RAW	SET_STDERR_TO_DBMS_OUTPUT
ENABLE_DEBUGGING	IMPORT_UROWID	SET_STDOUT
EVAL	PARSE_DEBUG_OUTPUT	SET_STDOUT_TO_DBMS_OUTPUT
EXPORT_CHAR	SET_CTX_STDERR	Demo

CREATE_CONTEXT (new 23c parameter)

Creates an MLE context for executing snippets in any MLE language dbms_mle.create_context(environment IN VARCHAR2 DEFAULT NULL) RETURN context_handle_t;

See Demo At Page Bottom

DISABLE_CTX_STDERR

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

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_DEBUGGING (new 23c)

Disables currently enabled debug points for the current session dbms_mle.disable_debugging;

exec dbms_mle.disable_debugging;

      

      PL/SQL procedure successfully completed.

DISABLE_ICS_STDERR (new 23c)

Disables the stderr stream of the inlined MLE call so future writes to stderr are discarded dbms_mle.disable_ics_stderr(name IN VARCHAR2);

TBD

DISABLE_ICS_STDOUT (new 23c)

Disables the stdout stream of the inlined MLE call so future writes to stdout are discarded dbms_mle.disable_ics_stdout(name IN VARCHAR2);

TBD

DISABLE_STDERR (new 23c overloads and parameters)

Disables the stderr stream of all MLE contexts, so that future writes to stderr are discarded

Overload 1 dbms_mle.disable_stderr(module_name IN VARCHAR2);

TBD

Overload 2 dbms_mle.disable_stderr( module_name IN VARCHAR2, env_name IN VARCHAR2);

TBD

Overload 3 dbms_mle.disable_stderr;

See DISABLE_CTX_STDERR Demo Above

DISABLE_STDOUT (new 23c overloads and parameters)

Disables the stdout stream of all MLE contexts, so that future writes to stdout are discarded

Overload 1 dbms_mle.disable_stdout(module_name IN VARCHAR2);

TBD

Overload 2 dbms_mle.disable_stdout( module_name IN VARCHAR2, env_name IN VARCHAR2);

TBD

Overload 3 dbms_mle.disable_stdout;

See DISABLE_CTX_STDERR Demo Above

DROP_CONTEXT

Drop an MLE context created using CREATE_CONTEXT dbms_mle.drop_context(context_handle IN context_handle_t);

See Demo At Page Bottom

ENABLE_DEBUGGING (new 23c)

Enable a set of debugpoints for the current session dbms_mle.disable_debugging( debugspec IN JSON, sink OUT NOCOPY BLOB);

TBD

EVAL (new 23c parameters)

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, options IN VARCHAR2 DEFAULT NULL);

See Demo At Page Bottom

Overload 2 dbms_mle.eval( context_handle IN context_handle_t, language_id IN language_t, source IN CLOB, options IN VARCHAR2 DEFAULT NULL);

TBD

Overload 3 dbms_mle.eval( context_handle IN context_handle_t, language_id IN language_t, source IN VARCHAR2, options IN VARCHAR2 DEFAULT NULL);

TBD

Overload 4 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, options IN VARCHAR2 DEFAULT NULL);

TBD

EXPORT_CHAR

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

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 23c overload)

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 JSON);

TBD

Overload 10 dbms_mle.export_to_mle( context_handle IN context_handle_t, property_name IN VARCHAR2, property_value IN NUMBER);

TBD

Overload 11 dbms_mle.export_to_mle( context_handle IN context_handle_t, property_name IN VARCHAR2, property_value IN TIMESTAMP_TZ_UNCONSTRAINED);

TBD

Overload 12 dbms_mle.export_to_mle( context_handle IN context_handle_t, property_name IN VARCHAR2, property_value IN TIMESTAMP_UNCONSTRAINED);

TBD

Overload 13 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 14 dbms_mle.export_to_mle( context_handle IN context_handle_t, property_name IN VARCHAR2, property_value IN YMINTERVAL_UNCONSTRAINED);

TBD

EXPORT_UROWID

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

Returns the set of available languages

As of the initial release of 21c, and in 23c, the only supported 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

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

GET_ERROR_STACK (new 23c)

Retrieves the MLE language stack trace for the most recent application error in the given module/environment call dbms_mle.get_error_stack( module_name IN VARCHAR2, env_name IN VARCHAR2 DEFAULT '') RETURN error_frames_t;

TBD

IMPORT_CHAR

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 23c overload)

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 JSON);

TBD

Overload 10 dbms_mle.import_from_mle( context_handle IN context_handle_t, property_name IN VARCHAR2, target OUT NOCOPY NUMBER);

TBD

Overload 11 dbms_mle.import_from_mle( context_handle IN context_handle_t, property_name IN VARCHAR2, target OUT NOCOPY TIMESTAMP_TZ_UNCONSTRAINED);

TBD

Overload 12 dbms_mle.import_from_mle( context_handle IN context_handle_t, property_name IN VARCHAR2, target OUT NOCOPY TIMESTAMP_UNCONSTRAINED);

TBD

Overload 13 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 14 dbms_mle.import_from_mle( context_handle IN context_handle_t, property_name IN VARCHAR2, target OUT NOCOPY YMINTERVAL_UNCONSTRAINED);

TBD

IMPORT_RAW

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

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

PARSE_DEBUG_OUTPUT (new 23c)

Given a BLOB containing MLE debug output in the Java heap dump format, returns a JSON representation of the debug output.\ dbms_mle.parse_debug_output(debugoutput IN BLOB) RETURN JSON;

TBD

SET_CTX_STDERR

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

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

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

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_ICS_STDERR (new 23c)

Redirects the stderr stream of an inlined MLE call to a CLOB dbms_mle.set_ics_stderr( name IN VARCHAR2, sink IN OUT NOCOPY CLOB CHARACER SET ANY_CS);

TBD

SET_ICS_STDERR_TO_DBMS_OUTPUT (ew 23c)

Redirects the stderr ICS stream to DBMS_OUTPUT dbms_mle.set_ics_stderr_to_dbms_output(module_name IN VARCHAR2);

TBD

SET_ICS_STDOUT (new 23c)

Redirects the stdout stream of an inlined MLE call to a CLOB dbms_mle.set_icd_stdout( name IN VARCHAR2, sink IN OUT NOCOPY CLOB CHARACER SET ANY_CS);

TBD

SET_ICS_STDOUT_TO_DBMS_OUTPUT (new 23c)

Redirects the stdout ICS stream to DBMS_OUTPUT dbms_mle.set_ics_stdout_to_dbms_output(module_name IN VARCHAR2);

TBD

SET_STDERR (new 23c overloads and parameters)

Redirect the stderr stream of all MLE contexts to the given CLOB dbms_mle.set_stderr( module_name IN VARCHAR2, sink IN OUT NOCOPY CLOB CHARACTER SET ANY_CS

TBD

Overload 2 dbms_mle.set_stderr( module_name IN VARCHAR2, env_name IN VARCHAR2, sink IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);

TBD

Overload 3 dbms_mle.set_stderr(sink IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);

TBD

SET_STDERR_TO_DBMS_OUTPUT (new 23c overloads and parameters)

Redirect the stderr stream of all MLE contexts to DBMS_OUTPUT dbms_mle.set_stderr_to_dbms_output(module_name IN VARCHAR2);

TBD

Overload 2 dbms_mle.set_stderr_to_dbms_output( module_name IN VARCHAR2, env_name IN VARCHAR2);

TBD

Overload 3 dbms_mle.set_stderr_to_dbms_output;

exec dbms_mle.set_stderr_to_dbms_output;

SET_STDOUT (new 23c overloads and parameters)

Redirect the stdout stream of all MLE contexts to the given CLOB dbms_mle.set_stdout( module_name IN VARCHAR2, sink IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);

TBD

Overload 2 dbms_mle.set_stdout( module_name IN VARCHAR2, env_name IN VARCHAR2, sink IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);

TBD

Overload 3 dbms_mle.set_stdout(sink IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);

TBD

SET_STDOUT_TO_DBMS_OUTPUT (new 23c overloads and overloads)

Redirect the stdout stream of all MLE contexts to DBMS_OUTPUT dbms_mle.set_stdout_to_dbms_output(module_name IN VARCHAR2);

TBD

Overload 2 dbms_mle.set_stdout_to_dbms_output( module_name IN VARCHAR2, env_name IN VARCHAR2);

TBD

Overload 3 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!