Recently the MLE team was asked if it was possible to pass JSON PL/SQL types to MLE/JavaScript.Remember that you have multiple options to work with JSON in Oracle Database 26ai: SQL (using the JSON data type), PL/SQL (using the PL/SQL API), JavaScript (did you know that the J in JSON stands for JavaScript?), etc.

Shout out to Steve Muench, who peer-reviewed the article and provided a wealth of great feedback.

There are many reasons why you might want to interact with the PL/SQL API in JavaScript, this post describes some ways how you could do that. When you break it down, there are essentially 2 scenarios:

1. Pass a PL/SQL JSON type to MLE
2. Pass a JavaScript object to PL/SQL

Let’s look at these in more detail. By the way, all of the following code examples have been tested on an Autonomous Database (Serverless) 26ai on Oct 29, 2025.

Passing PL/SQL JSON types to MLE

The first use case concerns passing PL/SQL JSON types to JavaScript, for example, whenever your PL/SQL API invokes a JavaScript function.

Let’s have a look at an example (I borrowed this from chapter 4 in the JSON Developer Guide). An anonymous PL/SQL block defines a Purchase Order document as JSON_OBJECT_T. This might as well be a function or procedure, as long as it’s PL/SQL it doesn’t matter. You could of course have used the JSON data type, but the point of this article is to show how to pass PL/SQL JSON types to MLE, this is one way, but maybe not the best way to solve this in a more general setting.

The handover to MLE happens towards the end: get_po_value, an inline JavaScript function calculate the value of the PO and prints it to the screen.

declare
    jo      JSON_OBJECT_T;
    val     number;
begin
    -- the reason JSON_OBJECT_T was chosen over the JSON data
    -- type is purely for educational reasons in the context of this
    -- article. The native JSON data type is most likely the better
    -- choice in most cases
    jo := json_object_t.parse('{
      "PONumber": 1600,
      "Reference": "ABULL-20140421",
      "Requestor": "Alexis Bull",
      "User": "ABULL",
      "CostCenter": "A50",
      "ShippingInstructions": {
        "name": "Alexis Bull",
        "Address": {
          "street": "200 Sporting Green",
          "city": "South San Francisco",
          "state": "CA",
          "zipCode": 99236,
          "country": "United States of America"
        },
        "Phone": [
          {
            "type": "Office",
            "number": "909-555-7307"
          },
          {
            "type": "Mobile",
            "number": "415-555-1234"
          }
        ]
      },
      "Special Instructions": null,
      "AllowPartialShipment": true,
      "LineItems": [
        {
          "ItemNumber": 1,
          "Part": {
            "Description": "One Magic Christmas",
            "UnitPrice": 19.95,
            "UPCCode": 13131092899
          },
          "Quantity": 9.0
        },
        {
          "ItemNumber": 2,
          "Part": {
            "Description": "Lethal Weapon",
            "UnitPrice": 19.95,
            "UPCCode": 85391628927
          },
          "Quantity": 5.0
        }
      ]
    }');

    val := get_po_value(jo.to_json);
    dbms_output.put_line('total value of the purchase order is ' || val);
end;
/

The JavaScript function used to calculate the PO’s value is this:

create or replace function get_po_value("purchaseOrder" json)
return number
as mle language javascript
{{
    // you typically add sanity checks here, or else risk runtime
    // errors. These are omitted for brevity
    return purchaseOrder.LineItems
        .map( x => x.Part.UnitPrice * x.Quantity )
        .reduce( 
            (accumulator, currentValue) => accumulator + currentValue, 0
        );
}};
/

The documentation mentions further serialisation and conversion functions for earlier releases such as 19c that don’t feature the native JSON data type.

Passing a JSON object to PL/SQL

The second use case involves passing a JavaScript object to PL/SQL. Let’s assume a MLE/JavaScript function does some fancy processing of data, and returns the result – a JavaScript object – to PL/SQL. This can be demonstrated as follows: a web API is interrogated by the JavaScript function, the result is then passed to PL/SQL and enriched.

create or replace function generate_json
return json
as mle language javascript
{{
    await import ("mle-js-fetch");
    const response = await fetch('https://api.ipify.org/?format=json');
    if (response.ok) {
        const data = await response.json();
        return data;
    } else {
        throw new Error(
          `unexpected network error: ${response.status}`
        );
    }

}};
/

Let’s call this function and use it in PL/SQL:

declare
    jo  json_object_t;
begin

    jo := json_object_t(
        generate_json
    );

    jo.put('lastUpdated', sysdate);

    dbms_output.put_line(
        jo.to_string
    );
end;
/

That’s it! Details about possible additional PL/SQL JSON type constructors can be found in the JSON Developer’s Guide.

Summary

If your application is partially implemented in PL/SQL and JavaScript, it isn’t hard to exchange JSON information between the 2 programming languages. The examples above cover the typical use cases for data exchange in Oracle Database 26ai.

Note that the JSON data type was introduced in Oracle Database 21c, which requires you to switch to a different constructor and/or conversion function depending on how your application works with JSON. The switch to Oracle Database 26ai takes a lot of these headaches away from you, and it something you should probably consider.