How to Exchange Values with JavaScript in DBMS_MLE

Dynamic JavaScript run with DBMS_MLE is only useful if it can take inputs and return results. DBMS_MLE.EXPORT_TO_MLE passes PL/SQL values into the JavaScript context, the mle-js-bindings module reads and writes them inside JavaScript, and IMPORT_FROM_MLE brings results back into PL/SQL variables.

Code for This Guide

The main examples are in the examples/pkg-mle folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dbms_mle.export_to_mle(ctx, 'name', plsql_value);      -- PL/SQL to JavaScript

-- in JavaScript
const bindings = require("mle-js-bindings");
const v = bindings.importValue("name");
bindings.exportValue("result", value);

dbms_mle.import_from_mle(ctx, 'result', plsql_variable); -- JavaScript to PL/SQL

Convert a Fare in JavaScript

Example:

declare
  ctx   dbms_mle.context_handle_t := dbms_mle.create_context();
  v_aed number;
  v_msg varchar2(100);
begin
  dbms_mle.export_to_mle(ctx, 'fare', 520);                  -- PL/SQL to JavaScript
  dbms_mle.export_to_mle(ctx, 'route', 'DXB-LHR');
  dbms_mle.eval(ctx, 'JAVASCRIPT', q'[
    const bindings = require("mle-js-bindings");
    const fare = bindings.importValue("fare");
    bindings.exportValue("aed", Math.round(fare * 3.6725 * 100) / 100);
    bindings.exportValue("msg", `${bindings.importValue("route")}: ${fare} USD`);
  ]');
  dbms_mle.import_from_mle(ctx, 'aed', v_aed);             -- and back
  dbms_mle.import_from_mle(ctx, 'msg', v_msg);
  dbms_output.put_line(v_msg || ' = ' || v_aed || ' AED');
  dbms_mle.drop_context(ctx);
end;
/

Output:

DXB-LHR: 520 USD = 1909.7 AED

PL/SQL procedure successfully completed.

The fare 520 and the route DXB-LHR go into the context. JavaScript converts the fare to dirhams, builds a message with a template string, and exports both. IMPORT_FROM_MLE reads them into a NUMBER and a VARCHAR2.

Things to Know

• Values are converted between PL/SQL and JavaScript types: numbers, strings, dates, and others.
• Exported values stay in the context for later EVAL calls until the context is dropped.
• Names are case-sensitive and must match between export and import.

Related Guides

• How to Run JavaScript Dynamically with DBMS_MLE
• How to Use JSON_OBJECT_T in PL/SQL

Conclusion

EXPORT_TO_MLE, the mle-js-bindings importValue and exportValue functions, and IMPORT_FROM_MLE move values between PL/SQL and JavaScript in a DBMS_MLE context. Pass inputs in, compute in JavaScript, and read results back into PL/SQL variables.