JavaScript is the first language supported by the Multilingual Engine (MLE) in Oracle Database 23ai. Having additional languages in the Oracle Database allows us to use existing libraries within the database. Also, it makes it easier for those without PL/SQL skills to get started with database development. Wasn’t that also the argument for Java in the database? What is easier and better in JavaScript than in Java? How performant are JavaScript modules? When is JavaScript a good alternative to PL/SQL and when is it not?
This is a translation of my German article “PL/SQL oder JavaScript in der Oracle Datenbank 23ai?” published in the Red Stack Magazine No. 6/2024 on 11 October 2024.
Why Do We Need Code in the Database?
I see the following reasons for this.
- We bring the code to the data rather than the data to the code. This allows us to process the data efficiently on the database server and deliver the result to the client in just a few network round trips. This uses fewer resources, is more cost-effective and faster than if we had to transport the data to the client and process it there.
- We take responsibility for the quality of the data stored in the database. Typically, we write data once and read it often. Therefore, we should store data correctly so that consumers can rely on the data when they read it. In this sense, the logic for validating the data belongs in the database. This logic is often more extensive than what today’s database constraints provide. In other words, we need code in the database as part of an API to keep our data consistent and correct.
Even if your database applications do not follow the principles of SmartDB or PinkDB, there are benefits to selectively using code in the database. And if your company policy categorically forbids code in the database, it is probably time to reconsider.
PL/SQL Without SQL
Let’s pretend we need a function to convert a timestamp to Unix time. Wikipedia defines the Unix time as follows.
Unix time is a date and time representation widely used in computing. It measures time by the number of non-leap seconds that have elapsed since 00:00:00 UTC on 1 January 1970, the Unix epoch.
Listing 1 shows how we can implement this in PL/SQL.
create or replace function to_epoch_plsql(
in_ts in timestamp
) return number is
co_epoch_date constant timestamp with time zone :=
timestamp '1970-01-01 00:00:00 UTC';
l_interval interval day(9) to second (3);
begin
l_interval := in_ts - co_epoch_date;
return 1000 * (extract(second from l_interval)
+ extract(minute from l_interval) * 60
+ extract(hour from l_interval) * 60 * 60
+ extract(day from l_interval) * 60 * 60 * 24);
end;
/The to_epoch_plsql function expects a timestamp, a timestamp with timezone would be better. We have omitted this to keep the example as simple as possible. Although the solution may seem simple, we are reimplementing existing functionality. We had to find out how Unix time works, what role time zones play, what leap seconds are for, and that Unix time is used in milliseconds, not seconds.
Wouldn’t it be nice to be able to use an existing, tested function in the database to keep our application’s code to a minimum? Even though there is no to_epoch function in SQL, the Java Development Kit (JDK) offers such functionality. The Oracle Java Virtual Machine (OJVM) is an embedded component of the Oracle Database 23.5. It’s a JDK version 11.
Java Without SQL
The Oracle Database has supported Java stored procedures since version 8i Release 1. This means that we can provide a to_epoch_java function as shown in Listing 2.
create or replace and compile java source named "Util" as
public class Util {
public static long toEpoch(java.sql.Timestamp ts) {
return ts.getTime();
}
}
/
create or replace function to_epoch_java(in_ts in timestamp)
return number is language java name
'Util.toEpoch(java.sql.Timestamp) return java.lang.long';
/For Java, we need to create a class and a call specification. The purpose of the call specification is, among other things, to map the types of input and output data between SQL and Java. For example, to map the return value of java.lang.long to number.
The code no longer contains the formula for converting a timestamp to Unix time, but it is quite extensive. Is there an easy way in JavaScript?
JavaScript Without SQL
With Oracle Database 23ai, we can create JavaScript modules.
create or replace mle module util_mod language javascript as
export function toEpoch(ts) {
return ts.valueOf();
}
/
create or replace function to_epoch_js(in_ts in timestamp)
return number is
mle module util_mod
signature 'toEpoch(Date)';
/The implementation of to_epoch_js in Listing 3 is similar to to_epoch_java. A module in JavaScript and then an MLE call specification. However, it is no longer necessary to fully map the input data types in JavaScript. The Oracle Database defines default values. These can be overridden, but do not need to be explicitly defined as in Java. It is not possible to map the output data type. In this case, it must be possible to convert the return value to a number, otherwise, a runtime error will occur.
However, the implementation for this simple case is quite extensive. Oracle has probably realised this and provided an alternative.
create or replace function to_epoch_js2("in_ts" in timestamp)
return number is
mle language javascript ' return in_ts.valueOf();';
/The to_epoch_js2 function in Listing 4 is equivalent to to_epoch_js but significantly simpler than any of the other variants. However, an inline MLE call specification is only applicable to JavaScript code without dependencies on other modules, this means for JavaScript code without an import command.
Performance of to_epoch_...
Anyone who has worked with Java stored procedures in the database knows that the initialisation of the OJVM in a new database session slows down the response time considerably. This is not the case with MLE because it uses a GraalVM native image. Simply put, it reads only the memory contents of a file, similar to waking your laptop from hibernation. This makes it possible to start a Java program within a millisecond. The native image is integrated into the database as a shared library $ORACLE_HOME/lib/libmle.so. This means that the MLE provides JavaScript via Java, but is completely independent of the OJVM.
In Figure 1 we compare the runtimes of 100,000 function calls. Instead of seconds, we use a normalised unit of time, which makes comparing easier and results less dependent on the hardware stack used.
All experiments were performed using the Oracle Database 23.5 Free Edition on an AMD Ryzen R1600 processor-based system. The shortest time of five repetitions was taken into account. You can reproduce these experiments using the scripts in this GitHub repository.
The to_epoch_plsql, to_epoch_java and to_epoch_js variants are called from a PL/SQL loop. This means 100,000 context switches between PL/SQL and Java or JavaScript. The fourth variant, to_epoch_jsloop, calls toEpoch from a JavaScript loop. In this case, the context switching between PL/SQL and JavaScript makes processing about 50 times slower.
Based on these results, we should avoid context switching between PL/SQL and JavaScript if possible. The performance of JavaScript in the database is impressive in this case. Quite different from the OJVM.
Memory Usage of to_epoch_...
Figure 2 shows the maximum memory used at the end of a to_epoch_… function call. The call was made in a new database session and contains the memory requirements of the measuring instruments.
JavaScript uses significantly more memory than PL/SQL. If you take this into account when sizing the database server and connection pools, this should not be a problem nowadays.
Using a 3rd Party JavaScript Library
Let’s say we want to validate email addresses in our database without actually sending a test email. The rules for a valid email address are quite extensive. In the JavaScript ecosystem, we can find open-source libraries for such requirements that can be used in the database without modification. For this example, we use validator.js, which can validate not only email addresses but also credit card numbers, EAN, IBAN and much more. Using SQLcl’s script command, we can load npm modules directly into the database.
script https://raw.githubusercontent.com/PhilippSalvisberg/mle-sqlcl/main/mle.js install validator_mod https://esm.run/validator@13.12.0 13.12.0
select version, language_name, length(module)
from user_mle_modules
where module_name = 'VALIDATOR_MOD';VERSION LANGUAGE_NAME LENGTH(MODULE)
---------- ---------------- -----------------
13.12.0 JAVASCRIPT 123260The script mle.js in Listing 5 is not read from the local file system as usual but via a URL from GitHub. The script creates a JavaScript module validator_mod with the contents of the URL https://esm.run/validator@13.12.0, which is a minimised, browser-optimised version of the validator.js module in the npm software registry. The last parameter 13.12.0 is the version of the module stored in the Oracle Data Dictionary.
In Listing 6, we create the MLE call specification in a PL/SQL package. The is_mail function accepts only a string as a parameter. The validator options are defined in the package body. This simplifies uniform use in the database application.
create or replace package validator_api is
function is_email(
in_email in varchar2
) return boolean deterministic;
end validator_api;
/
create or replace package body validator_api is
function is_email_internal(
in_email in varchar2,
in_options in json
) return boolean deterministic as mle module validator_mod
signature 'default.isEmail(string, any)';
function is_email(
in_email in varchar2
) return boolean deterministic is
begin
return is_email_internal(
in_email => in_email,
in_options => json('
{
"allow_display_name": false,
"allow_undescores": false,
"require_display_name": false,
"allow_utf8_local_part": true,
"require_tld": true,
"allow_ip_domain": false,
"domain_specific_validation": false,
"blacklisted_chars": "",
"ignore_max_length": false,
"host_blacklist": ["dubious.com"],
"host_whitelist": []
}
')
);
end is_email;
end validator_api;
/Listing 7 shows the use of the validator in SQL. The second email address is invalid because of the allow_display_name option. The third e-mail address is formally correct, but it uses a domain listed under host_blacklist.
select e_mail, validator_api.is_email(e_mail) as is_valid
from (values
('esther.muster@example.com'),
('Esther Muster <esther.muster@example.com>'),
('esther.muster@dubious.com')
) test_data (e_mail);E_MAIL IS_VALID
----------------------------------------- ----------
esther.muster@example.com 1
Esther Muster <esther.muster@example.com> 0
esther.muster@dubious.com 0JavaScript With SQL
The MLE provides a global variable session of type IConnection to communicate with the current database session. Listing 8 shows an example of a simple update statement using bind variables.
create or replace mle module increase_salary_mod
language javascript as
export function increase_salary(deptno, by_percent) {
session.execute(`
update emp
set sal = sal + sal * :by_percent / 100
where deptno = :deptno`, [by_percent, deptno]);
}
/
create or replace procedure increase_salary_js(
in_deptno in number,
in_by_percent in number
) as mle module increase_salary_mod
signature 'increase_salary(number, number)';
/PL/SQL With SQL
Listing 9 shows the PL/SQL counterpart to the JavaScript code in Listing 8, using dynamic SQL with bind variables.
create or replace procedure increase_salary_dplsql(
in_deptno in number,
in_by_percent in number
) is
begin
execute immediate '
update emp
set sal = sal + sal * :by_percent / 100
where deptno = :deptno' using in_by_percent, in_deptno;
end increase_salary_dplsql;
/Experienced PL/SQL developers would not write it this way, as syntax and semantic errors are only thrown at runtime. In addition, it is more expensive for the Oracle Database to execute dynamic SQL, and the use of database objects is not stored in the Oracle Data Dictionary. Instead, experienced PL/SQL developers use static SQL whenever possible and sensible. The code is shorter and SQL injection is impossible. Listing 10 shows the static SQL variant.
create or replace procedure increase_salary_plsql(
in_deptno in number,
in_by_percent in number
) is
begin
update emp
set sal = sal + sal * in_by_percent / 100
where deptno = in_deptno;
end increase_salary_plsql;
/Performance of increase_salary_...
Figure 3 compares the runtimes of 100,000 procedure calls in a PL/SQL loop. Only increase_salary_jsloop uses a JavaScript loop. This avoids 100,000 context switches between PL/SQL and JavaScript. In other words, the difference between increase_salary_js and increase_salary_jsloop is the cost of 100,000 context switches.
In the Oracle Database version 23.5, JavaScript is about 5 to 6 times slower than PL/SQL in this example when we use dynamic SQL. In the Oracle Database version 23.3, the difference was a factor of 7, which makes me optimistic that we can expect further performance improvements in future versions.
Based on these experiments, it is difficult to make general statements about the performance differences between PL/SQL and JavaScript. However, it appears that PL/SQL code with SQL statements has an advantage over JavaScript.
MLE Environment
Accessing the network using the JavaScript Fetch API is possible, If the appropriate permissions have been granted using the PL/SQL package dbms_network_acl_admin. However, for security reasons, JavaScript cannot access the database server’s file system.
JavaScript runtime environments such as Node.js, Deno, Bun or web browsers, access the file system to import other JavaScript modules. For that, you need an MLE environment in the Oracle Database. Listing 11 shows how to create and use it.
create or replace mle env demo_env
imports(
'increase_salary' module increase_salary_mod,
'validator' module validator_mod,
'util' module util_mod
)
language options 'js.strict=true, js.console=false,
js.polyglot-builtin=true'
;
create or replace mle module increase_salary_loop_mod
language javascript as
import {increase_salary} from "increase_salary";
export function increase_salary_loop(deptno,by_percent,times){
for (let i=0; i<times; i++) {
increase_salary(deptno, by_percent);
}
}
/
create or replace procedure increase_salary_jsloop(
in_deptno in number,
in_by_percent in number,
in_times in number
) as mle module increase_salary_loop_mod
env demo_env
signature 'increase_salary_loop(number, number, number)';
/The MLE environment demo_env maps the import name increase_salary to the MLE module increase_salary_mod. This import is used in the MLE module increase_salary_loop_mod. However, the MLE environment is not assigned there. This is only done in the MLE call specification increase_salary_jsloop.
MLE environments allow JavaScript code to be structured in the same way inside and outside the database. In most cases, a single MLE environment will be sufficient for an application. Multiple MLE environments are required if different language options are used per module, or if different versions of a module are to be loaded with the same import name.
Is Tom Kyte’s Mantra Still Valid?
One of the things Tom Kyte is famous for is his mantra. There are several variations, but all have the same message. This variant is from Expert Oracle Database Architecture, Third Edition, 2014. On page 3 he writes:
I have a pretty simple mantra when it comes to developing database software, one that has been consistent for many years:
- You should do it in a single SQL statement if at all possible. And believe it or not, it is almost always possible. This statement is even truer as time goes on. SQL is an extremely powerful language.
- If you can’t do it in a single SQL Statement, do it in PL/SQL—as little PL/SQL as possible! Follow the saying that goes “more code = more bugs, less code = less bugs.”
- If you can’t do it in PL/SQL, try a Java stored procedure. The times this is necessary are extremely rare nowadays with Oracle9i and above. PL/SQL is an extremely competent, fully featured 3GL.
- If you can’t do it in Java, do it in a C external procedure. This is most frequently the approach when raw speed or using a third-party API written in C is needed.
- If you can’t do it in a C external routine, you might want to seriously think about why it is you need to do it.
With Oracle Database 23ai, I would put JavaScript on the same level as PL/SQL. Before Java, definitely. Furthermore, it is not just a question of whether something can be done in SQL or PL/SQL. If we need a functionality that already exists in the JavaScript ecosystem, we should consider using it rather than reimplementing it in SQL or PL/SQL just because it’s possible. Ultimately, it is also about the maintainability of the application and the technical debt we are incurring.
Conclusion
MLE was introduced as an experimental feature at Oracle Open World 2017. Since then, MLE and the underlying GraalVM technology have been continuously improved and have reached a good, production-ready state in Oracle Database 23ai. It is ideally suited for integrating existing, tested functionality from the JavaScript ecosystem into the Oracle Database.
We still need to figure out how to develop, test, debug and deploy JavaScript with SQL. In any case, JavaScript is a real alternative to PL/SQL, even if PL/SQL scores with static SQL and better performance.