Some logic is easier to write, or already written, in JavaScript: string and date handling, geographic calculations, validation shared with a web front end. Oracle AI Database 26ai runs JavaScript inside the database with the Multilingual Engine (MLE), next to PL/SQL. An MLE module stores JavaScript code as a schema object, and a call specification makes its exported functions callable from SQL and PL/SQL.
Code for This Guide
The main examples are in the examples/schema-objects 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
create [or replace] mle module module language javascript as <JavaScript code with export statements> / create [or replace] function name (params) return type as mle module module signature 'exportedFunction(types)';
The module is an ECMAScript module: only exported functions can be called from outside. The SIGNATURE maps the SQL parameters to the JavaScript function's parameters.
A Distance Function in JavaScript
The module exports a function that computes the great-circle distance between two points with the haversine formula. A call specification, HAVERSINE_KM, makes it a SQL function, used here on airport coordinates.
Example:
create mle module geo_module language javascript as
export function haversineKm(lat1, lon1, lat2, lon2) {
const r = Math.PI / 180, dLat = (lat2 - lat1) * r, dLon = (lon2 - lon1) * r;
const h = Math.sin(dLat / 2) ** 2 +
Math.cos(lat1 * r) * Math.cos(lat2 * r) * Math.sin(dLon / 2) ** 2;
return Math.round(2 * 6371 * Math.asin(Math.sqrt(h)));
}
/
create function haversine_km (lat1 number, lon1 number, lat2 number, lon2 number)
return number
as mle module geo_module signature 'haversineKm(number, number, number, number)';
/
select a.airport_code || '-' || b.airport_code as route,
haversine_km(a.latitude, a.longitude, b.latitude, b.longitude) as km
from airports a, airports b
where a.airport_code = 'DXB' and b.airport_code in ('LHR', 'SYD');Output:
MLE module GEO_MODULE compiled Function HAVERSINE_KM compiled ROUTE KM __________ ________ DXB-LHR 5497 DXB-SYD 12043
The computed distances match the route distances stored in the schema: 5,497 km from Dubai to London and 12,043 km to Sydney. SQLcl may print a hint about SHOW ERRORS after creating the module, which does not apply to MLE modules.
Things to Know
- The module and the function are separate objects: several call specifications can expose functions of one module, and a module can import other modules.
- USER_MLE_MODULES lists the modules of the schema.
- For a single short function, an inline MLE function avoids creating a module.
- Running JavaScript requires the EXECUTE ON JAVASCRIPT privilege, and creating modules CREATE MLE.
Related Guides
Conclusion
MLE modules store JavaScript in the database, and call specifications expose their exported functions to SQL and PL/SQL with a signature. Use them for logic that is natural in JavaScript or shared with web code, called like any other function.
