Oracle | DBMS

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: 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

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	EXPORT_TO_MLE	SET_CTX_STDERR_TO_DBMS_OUTPUT
DISABLE_CTX_STDERR	EXPORT_UROWID	SET_CTX_STDOUT
DISABLE_CTX_STDOUT	GET_AVAILABLE_LANGUAGES	SET_CTX_STDOUT_TO_DBMS_OUTPUT
DISABLE_STDERR	GET_CTX_ERROR_STACK	SET_STDERR
DISABLE_STDOUT	IMPORT_CHAR	SET_STDERR_TO_DBMS_OUTPUT
DROP_CONTEXT	IMPORT_FROM_MLE	SET_STDOUT
EVAL	IMPORT_RAW	SET_STDOUT_TO_DBMS_OUTPUT
EXPORT_CHAR	IMPORT_UROWID	Demo
EXPORT_RAW	SET_CTX_STDERR

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!