Developing Applications for Oracle Database: Client & Server
The Oracle Database is renown for its rich support of programming languages. In addition to support for many client-side development languages the Oracle database has supported server-side programs for a very long time. Sometimes these have been referred to as "stored procedures", although the name doesn't give the feature the credit it deserves: apart from writing "procedures" a great many other possibilities exist to work with data.
The most common programmatic server-side interface to the Oracle database is PL/SQL. By using PL/SQL it is possible to keep business logic and data together, often offering significant improvements of performance and efficiency. Developers also benefit from a unified processing pattern for data, regardless of the client interface in use. And last but not least using a programmatic interface to the application decouples the frontend from the backend, crucial for modern application development techniques.
In addition to PL/SQL it is possible to create stored procedures using the Java Programming Language in the database. Even more languages are supported via External Procedures. That was the situation before the release of Oracle 21c.
Introducing JavaScript in Oracle Database 21c
Oracle 21c for Linux x86-64 added another language for server-side development to the mix: JavaScript. JavaScript is one of the most popular programming languages today. It has come a long way since its inception as a browser-based solution for interactive web pages. Whilst its popularity for front-end development remains strong, it has found its way into backend development as well: the node.js and deno projects for example are very popular in that space.
JavaScript support further enhances Oracle's already strong message about the Converged Database. A Converged Database is a multi-model, multi-tenant, multi-workload database. It effortlessly supports the data model and access method each development team wants, simplifying the development process. With its high popularity the JavaScript language fits right into this concept. Under the hood the JavaScript engine is based on GraalVM, a polyglot runtime that can execute several programming languages with high performance. The component powering the JavaScript engine in Oracle 21c and later is known as Multilingual Engine (MLE).
Oracle release 21c focused on dynamic execution of JavaScript snippets, and integration into Oracle's low-code application framework: Application Express (APEX). The DBMS_MLE package allows developers to execute code snippets written in JavaScript inside the database, both on-premises and in the cloud, for Linux x86-64.
Enhanced JavaScript Support in Oracle Database 23c Free - Developer Release
The availability of Oracle Database 23c Free - Developer Release on Linux x86-64 provides a wealth of new features to developers. In the context of JavaScript in Oracle database the following two concepts are introduced:
◉ JavaScript modules and environments
◉ Inline JavaScript procedures
The following sections discuss these in more detail.
JavaScript modules and environments
These are stored as schema objects and can be created in-line with the module header, based on Character Large Objects (CLOBs), or BFILEs stored in the file system. The following example demonstrates how to create a MLE JavaScript module with the actual JavaScript code provided in-line with the declaration:
create mle module if not exists example_module
language javascript as
/**
* convert a delimited string into key-value pairs and return JSON
* @param {string} inputString - the input string to be converted
* @returns {JSON}
*/
function string2obj(inputString) {
if ( inputString === undefined ) {
throw `must provide a string in the form of key1=value1;...;keyN=valueN`;
}
let myObject = {};
if ( inputString.length === 0 ) {
return myObject;
}
const kvPairs = inputString.split(";");
kvPairs.forEach( pair => {
const tuple = pair.split("=");
if ( tuple.length === 1 ) {
tuple[1] = false;
} else if ( tuple.length != 2 ) {
throw "parse error: you need to use exactly one '=' between key and value and not use '=' in either key or value";
}
myObject[tuple[0]] = tuple[1];
});
return myObject;
}
/**
* Perform a simple string concatenation
* @param {string} str1 - the first input string
* @param {string} str2 - the second input string
* @returns {string}
*/
function concat(str1, str2) {
return str1 + str2;
}
export { string2obj, concat }
/
Just as with client-side node.js development, modules can import other modules to allow for a divide-and-conquer programming model. It is also possible to use existing JavaScript modules, leveraging the huge ecosystem the community created, provided they adhere to the rules laid out by the MLE runtime.
JavaScript in Oracle Database 23c Free - Developer Release is based on ECMAScript 2022, exporting and importing functionality is based on the export and import keywords. Since there is no file system where modules reside, a new helper-entity named MLE environment is introduced. These MLE environments are the second major new innovation in database 23c Free - Developer Release and like MLE modules they are schema objects. MLE environments define import names to be used with the import keyword, pointing to a module. MLE environments and dependencies between MLE modules are not in scope of this introduction and will be covered extensively in future posts.
It is of course possible to call JavaScript code from SQL and PL/SQL as well. A JavaScript specific so-called Call Specification exposes a function exported from the MLE Module, for example:
create function if not exists string_to_JSON_module_example(
p_str varchar2
) return JSON
as mle module example_module
signature 'string2obj(string)';
/
Each PL/SQL function or procedure wishing to invoke JavaScript code needs to reference the module and (JavaScript) function, along with the correct number of parameters both in the PL/SQL argument list as well as the mapped JavaScript function using the signature clause. With all requirements satisfied it is possible to execute JavaScript code via the PL/SQL module call:
select
json_serialize(
string_to_JSON_module_example('a=1;b=2;c=3;d')
PRETTY
) as result;
RESULT
--------------------
{
"a" : "1",
"b" : "2",
"c" : "3",
"d" : false
}
Inline JavaScript Functions and Procedures
In cases where you just need a JavaScript function, you can use inline JavaScript procedures instead of a module. The previous example can be rewritten as an inline function as follows:
create function if not exists string_to_JSON_inline_example(
"inputString" varchar2
) return JSON
as mle language javascript
q'~
if ( inputString === undefined ) {
throw `must provide a string in the form of key1=value1;...;keyN=valueN`;
}
let myObject = {};
if ( inputString.length === 0 ) {
return myObject;
}
const kvPairs = inputString.split(";");
kvPairs.forEach( pair => {
const tuple = pair.split("=");
if ( tuple.length === 1 ) {
tuple[1] = false;
} else if ( tuple.length != 2 ) {
throw "parse error: you need to use exactly one '=' between key and value and not use '=' in either key or value";
}
myObject[tuple[0]] = tuple[1];
});
return myObject;
~';
/
Inline JavaScript functions offer high convenience at a slight expense of functionality but in many cases the trade-off is negligible. The function in the listing above can be executed as if it were a PL/SQL function, a nice productivity boost.
Prerequisites for using JavaScript in Oracle Database 23c Free - Developer Release
Before you can run the examples in this post make sure you meet the following prerequisites:
- Ensure that the compatible initialization parameter is set to 23.0.0 or higher
- The new initialization parameter multilingual_engine is set to enable
- The following system privileges have been granted to your user:
- create mle
- create procedure
- any other privileges such as creating tables, indexes, etc. The db_developer_role might prove to be useful
- The execute on javascript object privilege has been granted to the user
- Your platform is Linux x86-64
Source: oracle.com
Posts
