Oracle Database 23ai introduces In-Database JavaScript for Linux x86-64 and aarch64. That’s Linux on Intel and Arm, respectively. Developers with a preference for Typescript can use it alternatively after transpilation to JavaScript. JavaScript support in Oracle Database is known as Multilingual Engine, or MLE for short.
One of the really interesting opportunities offered by MLE is the use of 3rd-party community modules accessible via Node Package Manager (NPM), opening the door for solutions that were previously unthinkable in server-side processing. Provided your security and compliance departments sign off on their use, you can save substantial amounts of time by not having to reinvent the wheel.
Polyfills in MLE/JavaScript
A question concerning JIMP, a native JavaScript library for image manipulation, came up recently.
Unlike some other image manipulation toolkits, JIMP is purely implemented in JavaScript, with zero dependencies, according to its documentation. Whilst this makes it potentially suitable for the use within the database, there are some serious drawbacks – most notably it’s probably not as performant as solutions using native libraries (unavailable in MLE), it’s also likely to be a bit of a memory hog.
Most importantly it cannot be used out-of-the box without some additional work.
If you start implementing JIIMP in MLE you are sooner or later going to encounter an error similar to this:
declare
*
ERROR at line 1:
ORA-04161: ReferenceError: setTimeout is not defined
ORA-06512: at line 67
ORA-04171: at l2 (DEMOUSER.JIMP_TEST_MODULE:7631:16)
ORA-06512: at "SYS.DBMS_MLE", line 447
ORA-06512: at line 57
The error indicates that my module, JIMP_TEST_MODULE, tries to use setTimeout(), a function that’s specific to browsers and node, and therefore not available in MLE/JavaScript at the time of writing.
As per its documentation, MLE/JavaScript started out as a pure ECMAScript implementation. The timeout and interval API is node specific, in other words, not part of the standard. Over time more and more web APIs are added to MLE, however timeouts and intervals aren’t present in Oracle Database 23ai Release Update 8, the most current version at the time of writing. Therefore, a little extra work is necessary: you have to write a polyfill. This isn’t limited to setTimeout() by the way, other functions such as those in node:fs also require a polyfill.
IMPORTANT: most JavaScript runtimes typically have 2 separate task queues. The microtask queue is used to execute promise reactions. There’s another one, referred to as the task queue that’s mostly used for I/O related work. Timers are usually part of the task queue, meaning that if you schedule a task it will run after all scheduled micro tasks. The polyfill introduced in this article does not behave that way which may break some packages that rely on this principle for event dispatching.
End-to-end example featuring JIMP
Let’s assume you want to use JIMP to convert colour images to greyscale. That’s a totally arbitrary use case but sufficient to demonstrate the concept of providing polyfills. You can find the complete example on my GitHub.
Let’s start by creating a very basic database table to store images as BLOBs (Binary Large Objects):
-- src/database/01_demo_table.sql
create table demo_images (
id number generated by default on null as identity
constraint pk_demo_images primary key,
image blob
);
Insert an image with a supported image type such as a JPEG or PNG (refer to the JIMP documentation for details) using Oracle SQL Developer Extension for VSCode or whichever tool you like into the table and commit. It is assumed that this new image is stored with an ID of 1.
The “application” consists of the following JavaScript function in src/javascript/demo-mle.js.
import { Jimp } from "jimp";
/**
* This function uses JIMP to convert a coloured image to greyscale and returns
* the converted image as a buffer suitable for storing in Oracle. The MIME
* type returned is hard-coded to PNG.
*
* @param {Uint8Array} myImage the image to convert, typically stored a a BLOB in a table
* @returns Uint8Array the converted image
*/
export async function image2Greyscale(myImage) {
let image;
if (!myImage) {
throw new Error("you did not provide a valid image to the function");
}
try {
image = await Jimp.read(myImage);
} catch (err) {
throw new Error("failed to open image the image");
}
// convert to greyscale and return the result as a UInt8Array
// suitable for storing in the database
image.greyscale();
// save the result as a PNG
return image.getBuffer("image/png");
}
Since JIMP is a mighty library it needs to be bundled with the “application”, demo-mle.js. The easiest way to do so is to install JIMP via NPM (Node Package Manager) like so: npm install jimp.
ESBuild is a great bundler, it can be used as follows after it has been installed via npm install --save-exact --save-dev esbuild
npx esbuild src/javascript/demo-mle.js --bundle --outfile=dist/bundle.js --format=esm
Have a look at the package.json associated with the project for version details.
You can use Oracle SQLcl to load the bundled JIMP + application module and the polyfill into the database like so:
mle create-module -replace -module-name jimp_test_module -filename dist/bundle.js
mle create-module -replace -module-name timers_polyfill -filename src/javascript/polyfill.js
The polyfill is too large to be posted here, you can find it on GitHub in src/javascript/polyfill.js.
The final step before the first test is to create an MLE Environment. Using the environment you tie import names to MLE modules. In this case both modules are mapped:
create mle env jimp_env imports (
'jimp' module jimp_test_module,
'polyfill' module timers_polyfill
);
Now it’s time for a test. DBMS_MLE provides the quickest way to run JavaScript code. You can alternatively use Database Actions, APEX, or any other tool that allows you to run JavaScript code in Oracle Database. The JavaScript code is provided as a CLOB to DBMS_MLE.eval(). More information about the MLE/JavaScript SQL driver and the use of Large Objects (CLOB, BLOB, BFILE) can be found in the MLE documentation.
-- test/convert-image.sql
set serveroutput on;
declare
l_ctx dbms_mle.context_handle_t;
l_source_code clob;
begin
-- Create execution context for MLE execution and provide an environment_
l_ctx := dbms_mle.create_context('JIMP_ENV');
-- using q-quotes to avoid problems with unwanted string termination
l_source_code :=
q'~
(async() => {
const { image2Greyscale } = await import ('jimp');
const { setTimeout, clearTimeout, setInterval, clearInterval } = await import ('polyfill');
globalThis.setTimeout = setTimeout;
globalThis.clearTimeout = clearTimeout;
globalThis.setInterval = setInterval;
globalThis.clearInterval = clearInterval;
let result = session.execute(
'select IMAGE from demo_images where id = :id',
[ 1 ],
{
fetchInfo: {
IMAGE: {
type: oracledb.UINT8ARRAY
}
}
}
);
console.log(`fetched ${result.rows.length} rows`);
const file = result.rows[0].IMAGE.buffer;
const transformedFile = await image2Greyscale(file);
console.log('file successfully transformed');
let theBLOB = OracleBlob.createTemporary(false);
console.log('new blob allocated');
theBLOB.open(OracleBlob.LOB_READWRITE);
console.log('blob opened r/w');
theBLOB.write(1, transformedFile);
result = session.execute(
`insert into demo_images(image) values(:theBLOB)`,
{
theBLOB:{
type: oracledb.ORACLE_BLOB,
dir: oracledb.BIND_IN,
val: theBLOB
}
}
);
theBLOB.close();
console.log('transformed image successfully saved');
})()
~';
dbms_mle.eval(
context_handle => l_ctx,
language_id => 'JAVASCRIPT',
source => l_source_code
);
dbms_mle.drop_context(l_ctx);
commit;
exception
when others then
dbms_mle.drop_context(l_ctx);
raise;
end;
/
The comments within the code should be sufficient to demonstrate what’s happening. The addition of the timeout and interval functions to the global scope are crucial for JIMP. However, image processing done this way is rather slow for reasons outlined earlier in the article. Nevertheless, eventually the prompt returns:
SQL> @test/convert-image
fetched 1 rows
file successfully transformed
new blob allocated
blob opened r/w
blob successfully populated
{"id":[65]}
transformed image successfully saved with ID 65
PL/SQL procedure successfully completed.
Commit complete.
If you check the database table next, you’ll see a second entry: the converted image! Voila
Martins Blog 