New features are introduced to JavaScript/Multilingual Engine (MLE) in Oracle Database 23ai constantly, even though they might not always be end-user visible. With the availability of Release Update 5 (Oracle Database 23.5.0) and later, a few important changes worth reporting have made it, and you can use them, too 🙂
This blog post demonstrates how to use the popular sql-template-tag library, hosted on GitHub, in Oracle Database 23ai on Linux x86-64.
Community-Driven
This example has been made possible by a couple of community enhancement requests to sql-template-tag and node-oracledb. Beginning with Oracle Database 23ai Release Update 5 (23.5.x) the MLE JavaScript SQL Driver accepts a JavaScript object as the first parameter to IConnection.execute(), as documented on GitHub. Up until then, the first argument had to be a string, indicating the SQL text to execute. The enhancement requests are listed at the end of the article in the references section if you want to learn more.
Setting up
You require an Oracle Database 23ai Database for Linux x86-64 with RU 5 or later applied. The easiest way to get one is to use Oracle Database 23ai Free and run it in a container. Alternatively, use an Always-Free Autonomous Database-Serverless (ADB-S) instance. Make sure to select Release 23ai when creating it, everything else is taken care off.
Once the Database is up and running you need to create a user account, the details on how to create it can be found in Chapter 9 of the JavaScript Developer’s Guide. Once your user is created (it’s named emily in the following example) you can create the sql-template-tag module. At the time of writing, version 5.2.1 was current. It can be found on jsdelivr for example.
CAUTION: Please refer to module’s GitHub project site for more details about the project’s license and implications of use. The article assumes your legal and IT Security departments (as well as any other party) agreed that it’s safe to use the module in your code. Using 3rd party code in your application typically requires certain compliance steps to be completed which are out of scope of this article.
Once you are happy that using sql-template-tag is fine under the constraints mentioned above, use your favourite tool to create SQL_TEMPLATE_TAG_MODULE using the ECMA Script (ESM) version of the module, available for example from https://cdn.jsdelivr.net/npm/sql-template-tag@5.2.1/+esm. Using Database Actions is a particularly easy way to load a module from a URL.
Using the sql-template-tag library
As soon as SQL_TEMPLATE_TAG_MODULE has been created, it is ready to use in your own code. You need to tell the database how to resolve the import name, which is done with the help of an MLE Environment. The environment maps an import name like sql-template-tag to a module saved in the database. In this example, the mapping is defined as follows:
create or replace mle env sql_template_tag_env
imports (
'sql-template-tag' module SQL_TEMPLATE_TAG_MODULE
)
/
Let’s create a small MLE module using the sql-template-tag. The code can be found on my Github, the module uses a couple of tables that were defined earlier.
create or replace mle module template_example_module
language javascript as
import sql, { bulk } from "sql-template-tag";
/**
* use the SQL template tag library to query the books table
*/
export function queryExample(id) {
const query = sql`SELECT title, year FROM books WHERE id = ${id}`;
console.log('about to query the database; the query object contains the following fields');
console.log(`query.sql: ${query.sql}`);
console.log(`query.text: ${query.text}`);
console.log(`query.statement: ${query.statement}`);
console.log(`query.values: ${query.values}`);
const result = session.execute(query);
for (const row of result.rows) {
console.log(`found a book: ${row.TITLE} written in ${new Date(row.YEAR).getFullYear()}`);
}
}
/**
* use the SQL template tag library to perform an insertMany() call against the author table
*/
export function bulkExample() {
const query = sql`INSERT INTO author (first_name, last_name) VALUES ${bulk([
["Jeff", "Blake"],
["Sponge", "Bob"],
["GI", "Joe"],
])}`;
console.log('about to insert into the database; the query object contains the following fields');
console.log(`query.sql: ${query.sql}`);
console.log(`query.values: ${query.values}`);
const result = session.execute(query);
console.log(`the query affected ${result.rowsAffected} rows`)
}
/
The books table is created based on all_objects, which is why you’ll see funny looking book titles. Before the above code can be used in SQL and PL/SQL you need a call specification. It’s shown here:
create or replace package template_example_pkg as
procedure query_example(p_id number) as
mle module template_example_module
env sql_template_tag_env
signature 'queryExample';
procedure bulk_example as
mle module template_example_module
env sql_template_tag_env
signature 'bulkExample';
end template_example_pkg;
/
Note how both the module name and the MLE environment are specified.
That’s it, you can use the functionality, as shown in the examples below:
SQL> begin
2 template_example_pkg.QUERY_EXAMPLE(1);
3 end;
4 /
about to query the database; the query object contains the following fields
query.sql: SELECT title, year FROM books WHERE id = ?
query.text: SELECT title, year FROM books WHERE id = $1
query.statement: SELECT title, year FROM books WHERE id = :1
query.values: 1
found a book: $VSN_1 written in 2019
PL/SQL procedure successfully completed.
SQL> begin
2 template_example_pkg.BULK_EXAMPLE;
3 end;
4 /
about to insert into the database; the query object contains the following fields
query.sql: INSERT INTO author (first_name, last_name) VALUES (?,?),(?,?),(?,?)
query.values: Jeff,Blake,Sponge,Bob,GI,Joe
the query affected 3 rows
PL/SQL procedure successfully completed.
SQL> select
2 id,
3 first_name,
4 last_name
5 from
6 author
7 order by
8 id desc
9 fetch first 3 rows only;
ID FIRST_NAME LAST_NAME
_____ _____________ ____________
35 GI Joe
34 Sponge Bob
33 Jeff Blake