Avoiding precision loss with MLE/JavaScript in Oracle AI Database 26ai

Oracle AI Database 26ai introduced Multilingual Engine (MLE), allowing you to run JavaScript inside the database. This feature bumps the number of languages available to write server-side code to 3: PL/SQL, Java, and now JavaScript. JavaScript is a modern language with many interesting features and data types, but there are things worth knowing. Number arithmetic based on the native Number data type comes with a pitfall, and you should know how to address it (spoiler: it’s simple with Oracle AI Database)

Number Arithmetic in pure/plain JavaScript

JavaScript’s Number type uses binary floating-point (IEEE 754), which cannot exactly represent many decimal fractions (like 0.1 or 19.95). As a result, arithmetic operations introduce tiny rounding errors. For example:

			
$ node
Welcome to Node.js v24.13.0.
Type ".help" for more information.
> 0.1 + 0.2
0.30000000000000004

		

These small inaccuracies can accumulate over multiple calculations, leading to visibly incorrect totals. In monetary calculations, even a tiny error (like a fraction of a cent) is unacceptable.

There are multiple common workarounds in JavaScript, for example storing money as integers. 10.99 € can be represented as 1099 cents, which doesn’t seem terribly appealing to a database person. There are simply too many permutations in how currency calculations are performed.

Libraries such as decimal.js, big.js, and others are probably better suited for the job. These implement base-10 arithmetic, so numbers like 0.1 are exact, which is critical. And then there’s also an emerging standard named Decimal (a TC39 proposal) but it doesn’t seem to have too much traction at the time of writing.

Problem Statement

Let’s assume your task at hand is to calculate the value of a Purchase Order. Data is provided in a table named J_PURCHASEORDER and looks like this.

JSON

			
{
    "PONumber": 1600,
    "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
        }
    ]
}

		

This example has been borrowed from the JSON Developer’s Guide, chapter 4 but slightly abbridged. If you used JavaScript’s built-in Number type you’d get an incorrect result in node 24:

			
const po = {
    "PONumber": 1600,
    "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
        }
    ]
};
const lineItems = po.LineItems;
const poValue = lineItems
    .map(x => x.Part.UnitPrice * x.Quantity)
    .reduce((accumulator, currentValue) => accumulator + currentValue, 0);
// will print "the value of PO 1600 is 279.29999999999995" in node v24.13.0
console.log(`the value of PO 1600 is ${poValue}`);

		

Let’s fix this!

Solution 1: Using the Database

From an Oracle Database point of view, the most convenient way to deal with number arithmetic in JavaScript is to use SQL. You can think of a function like the following one: it accepts a PO Number, looks the corresponding PO up in the database, and transforms the JSON to a relational structure using json_table(). It then sums the value of each line item – job done, no rounding errors.

			
create or replace function get_po_value("PONumber" number) return number
as mle language javascript
{{
  const result = session.execute(`
    select
      jt.po_number,
      sum(jt.unit_price * jt.quantity) as po_value
    from
      j_purchaseorder j,
      json_table(
        j.data,
        '$'
        columns (
          po_number NUMBER PATH '$.PONumber',
          nested path '$.LineItems[*]'
          columns (
            unit_price NUMBER PATH '$.Part.UnitPrice',
            quantity   NUMBER PATH '$.Quantity'
          )
        )
      ) jt
    where
      j.data.PONumber = :poNumber
    group by po_number`,
    [ PONumber ]
  );
  
  if (result.rows.length !== 1)
    throw new Error(`no data found for PONumber ${PONumber}`);
  
  return result.rows[0].PO_VALUE;
}};
/
SQL> select get_po_value(1600)
  2  /
GET_PO_VALUE(1600)
------------------
             279.3

		

That’s the correct result.

Solution 2: use OracleNumber in JavaScript

If you can’t perform arithmetic in SQL for some reason, you may want to have a look at the OracleNumber implementation. It is a MLE/JavaScript API for the database’s Number data type. Since Oracle 23.5, this class supports infix operator arithmetic rather than resorting to functions, making it very easy to use.

The logic you saw implemented in SQL can be expressed in JavaScript, too, as shown here. This is an almost identical representation of the initial node.js example. Run it in Database Actions, APEX, or pass the JavaScript part to DBMS_MLE.

			
const po = {
    "PONumber": 1600,
    "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
        }
    ]
};
const lineItems = po.LineItems;
const poValue = lineItems
        .map( x => new OracleNumber(x.Part.UnitPrice) * new OracleNumber(x.Quantity) )
        .reduce( 
            (accumulator, currentValue) => accumulator + currentValue, OracleNumber.zero
        );
console.log(`the value of PO 1600 is ${poValue}`);

		

The output matches the SQL result:

the value of PO 1600 is 279.3

Summary

While the introduction of server-side JavaScript in Oracle AI Database 26ai opens up powerful new possibilities, developers must be aware that JavaScript’s native Number type uses floating-point arithmetic, which can lead to precision errors.

Using a purchase order example, this post demonstrates how standard JavaScript arithmetic produces inaccurate results due to floating-point rounding issues. Two reliable solutions are available:

Perform calculations in SQL, leveraging Oracle’s exact NUMBER type via json_table()—the recommended and most robust approach.
Use the OracleNumber class in MLE JavaScript, which provides precise decimal arithmetic and integrates seamlessly with JavaScript syntax.

Both approaches ensure accurate results, highlighting that while plain vanilla JavaScript has limitations with numeric precision, Oracle AI Database provides straightforward ways to avoid them.

JavaScript, MLE