In Oracle 21c introduces JavaScript as a new language for server-side development, adding to its existing capabilities. JavaScript, widely known for its versatility and popularity in both front-end and back-end development, has been integrated into Oracle’s Converged Database concept.
This concept emphasizes a flexible, multi-model database system that caters to various development needs, making development processes simpler. Powered by GraalVM, the JavaScript engine in Oracle 21c, known as Multilingual Engine (MLE), enables high-performance execution of JavaScript code within the database environment.
The release of Oracle 21c emphasizes dynamic execution of JavaScript code snippets and their integration into Oracle’s low-code application framework, Application Express (APEX). Developers can utilize the DBMS_MLE package to execute JavaScript code within the database, whether on-premises or in the cloud
Enhanced JavaScript Support in Oracle Database 23c
In the Oracle 23c database, numerous enhancements have been made to the Multilingual Engine (MLE), including simplifications in calling JavaScript from PL/SQL and SQL. For instance, functions like isEmail, compute_json_result, and print_json_result, which are written in JavaScript, can now be invoked as if they were standard PL/SQL functions.
declare
valid number;
result json;
begin
select count(*) into :valid from emp
where validator.isEmail(email) = true;
result := compute_json_result(valid);
print_json_result(result);
end;
Furthermore, it’s feasible to invoke PL/SQL and SQL from JavaScript.
export function printEmployeeCount() {
const cnt = session.execute {"SELECT count(*) from emp").row[0][0];
session.execute("BEGIN DBMS_OUTPUT.PUT_LINE(:cnt);END;",[cnt]);
}
}
Oracle 23c now allows to import of JavaScript modules into other JavaScript code within the Oracle database.
import { isEmail } from "validator";
export function printEmailInfo (name, email) {
console.log(name + "'s email address valid: " + isEmail(email));
}
JavaScript modules and environments
In the following example demonstrate how to create a MLE JavaScript module with actual JavaScript code provided in-line with declaration. You also have an option to create JavaScript from a BFILES stored in the file system or based on CLOBs (Character Large Objects) from the table.
create mle module if not exists validator_mod
language javascript as
export function isEmail(email){
return String(email)
.toLowerCase()
.match(
/^(([^<>()[\]\\.,;:\s@"]+(\.[^<>()[\]\\.,;:\s@"]+)*)|.(".+"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/
) ? true: false;
}
In order to seamlessly invoke JavaScript from PL/SQL without concern for its underlying nature, it is necessary to establish function signatures that reference the PL/SQL function alongside the JavaScript function.
create function if not exists isEmail(
p_str varchar2
) return boolean
as mle module validator_mod
signature 'isEmail(string)';
See the results when calling the function isEmail.
select isEmail('abc@xyz.com') valid;
VALID
----------
1
Javascript Inline Procedure for Simple Things
The preceding example can indeed be streamlined with the following code. However, this approach is only suitable when there are no dependencies on other JavaScript modules. If the situation becomes more complex, it’s advisable to create a Multilingual Engine (MLE) module instead.
CREATE OR REPLACE FUNCTION isEmail("email" in VARCHAR2)
return BOOLEAN as MLE LANGUAGE JAVASCRIPT
q'~
return String(email)
.toLowerCase()
.match(
/^(([^<>()[\]\\.,;:\s@"]+(\.[^<>()[\]\\.,;:\s@"]+)*)|.(".+"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/
) ? true: false;
~';
select isEmail('abc@xyz.com.com') valid;
With this latest addition in Oracle 23c, it’s now possible to construct a complete application using JavaScript directly within the database.
