What’s new in Multilingual Engine 23.5: Operator Overloading

New features are introduced to JavaScript/Multilingual Engine (MLE) in Oracle Database 23ai constantly, even though they might not always be end-user visible. With the availability of Release Update 5 (Oracle Database 23.5.0) and later, a few important changes worth reporting have made it, and you can use them, too 🙂

This blog post introduces improvements to the OracleNumber class, allowing developers to use standard mathematical operators to add, subtract, multiply and divide instances of the class.

Background

The JavaScript number data type has certain limitations, and unfortunately they haven’t been addressed yet, at least not by ECMAScript. The most notable proposal, TC39 decimal, points out why there is need for action:

As currently defined in JavaScript, Numbers are 64-bit binary floating-point numbers. The conversion from most decimal values to binary floats rarely is an exact match. For instance: the decimal number 0.5 can be exactly represented in binary, but not 0.1; in fact, the  64-bit floating point number corresponding to 0.1 is actually 0.1000000000000000055511151231257827021181583404541015625. Same for 0.2, 0.3, … Statistically, most human-authored decimal numbers cannot be exactly represented as a binary floating-point number (AKA float).

Although a few years in the making, the decimal proposal hasn’t progressed past stage 1. And it has even been reduced in scope. This means you still run into problems such as these:

> const x = 0.1 + 0.2
undefined
> x
0.30000000000000004

Unrelated to the conversion problem mentioned above, there’s also an issue with the maximum value you can store, defined as Number.MAX_SAFE_INTEGER. Exceeding that value can lead to unexpected results:

> Number.MAX_SAFE_INTEGER +1 === Number.MAX_SAFE_INTEGER + 2
true

Surely this isn’t true, despite of what the JavaScript engine claims.

These problems don’t make JavaScript numbers the most suitable candidate for use in applications where number precision matters. The JavaScript community has worked around the decimal <-> binary float issue by providing libraries such as decimal.js. A new data type, BigInt, allows you to work with integers greater than Number.MAX_SAFE_INTEGER.

Solutions to these problems are provided with Oracle Database 23ai

Oracle Database 23ai potentially faces the same problem: when executing code in the MLE/JavaScript engine, precision can be lost if you use the default data type:

create or replace procedure native_number_dt as
mle language javascript
{{
  const x =  0.1 + 0.2;
  console.log(`x evaluates to ${x}`);
}};
/

SQL> exec native_number_dt
x evaluates to 0.30000000000000004

PL/SQL procedure successfully completed.

To avoid this issue Oracle Database provides PL/SQL types with MLE. These map Oracle SQL and PL/SQL types to JavaScript and vice versa (more details in the documentation). Using the OracleNumber type for example, you can avoid a loss of precision as shown in the following example.

create or replace procedure oracleNumber_dt as
mle language javascript
{{
  const x = new OracleNumber(0.1);
  const y = new OracleNumber(0.2);

  const z = x.add(y);

  console.log(`z evaluates to ${z}`);
}};
/

SQL> exec ORACLENUMBER_DT
z evaluates to .3

PL/SQL procedure successfully completed.

Looking closely at the code you will undoubtedly notice that x invoked the class’s add() function to calculate z. Although in line with other libraries, this doesn’t come natural and can be improved, which is exactly what MLE did in Oracle Database 23ai Release Update 5:

create or replace procedure oracleNumberEnhanced as
mle language javascript
{{
  const x = new OracleNumber(0.1);
  const y = new OracleNumber(0.2);

  const z = x + y;

  console.log(`z evaluates to ${z}`);
}};
/

If you use Oracle Database 23ai Free 23.5.x or later, you should see the following output:

SQL> set serveroutput on
SQL> exec oracleNumberEnhanced
z evaluates to .3

PL/SQL procedure successfully completed.

Summary

JavaScript, as implemented in MLE, offers a built-in library developers can use to avoid a loss of precision when working with numbers in JavaScript. Furthermore, beginning with Oracle Database 23.5 OracleNumber provides developers with the ability to use mathematical operators rather than methods.