Multilingual Engine, or MLE for short, allows developers to use the hugely popular JavaScript language to write server-side code with Oracle Database 23ai. With the introduction of MLE in Oracle Database 23ai you have 3 languages available to store business logic alongside the data:
- PL/SQL
- Java
- JavaScript
Background and Motivation
Backend developers frequently use tools to deploy their application changes, such as Liquibase, Flyway, and others. Liquibase is an open-source database-independent library for tracking, managing and applying database schema changes, and it has great support in the form of SQLcl. SQLcl is a lightweight tool allowing you to interact with the Oracle database without having to install a client, amongst many other things. I use the SQLcl/Liquibase combination for any database schema migration tasks I might have. The official documentation has all the details on Liquibase support.
In this short post I’d like to draw your attention to a feature I haven’t seen before: runOracleScript.
Please note that this post was written using Oracle Database 23ai Free (23.4) and SQLcl (version 24.2). Things may change in the future, always make sure to check the documentation!
Demo
Using the runOracleScript changeset-type allows me to execute anything that SQLcl would execute, which is exactly what I need for MLE modules. The standard Liquibase driver doesn’t yet “know” about MLE modules and environments. Here is a short example how this works. Initially I don’t have any Liquibase deployments in my schema:
SQL> lb history
--Starting Liquibase at 2024-08-15T10:33:52.488207604 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Liquibase History for jdbc:oracle:thin:@localhost:1522/freepdb1
No changesets deployed
Operation completed successfully.
Let’s change that. First of all I need a changelog to drive the schema migration. Think of a changelog as a release. The changelog contains changesets, or things to do. There are multiple formats available like SQL, XML, YAML, etc. to define changelogs and changesets. I am using the XML format in this article.
This particular changelog is rather simple, as it refers to a single changeset, but it should hopefully be sufficient to bring the point home:
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
<include file="example-module.xml"/>
</databaseChangeLog>
The contents of example-module.xml is an adaption of the runOracleScript example shown in the SQLcl documentation. The XML CDATA section contains the code I want SQLcl/Liquibase to execute. Note the runOracleScript tag in line 15:
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
<changeSet
id="mcb001"
author="martincarstenbach"
failOnError="true"
runOnChange="false"
runAlways="false">
<n0:runOracleScript objectName="example-module" ownerName="EMILY" sourceType="STRING">
<n0:source><![CDATA[
-- it is possible to add directives to the script
set define off
set verify off
-- and comments
create mle module if not exists demo_module language javascript as
/**
* A simple function returning a string with a nice greeting
* @param {string} who who do you want to greet?
* @returns {string} the completed greeting
*/
export function hello(who) {
return `hello, ${who}`;
}
/
]]>
</n0:source>
</n0:runOracleScript>
</changeSet>
</databaseChangeLog>
With the changelog and a changest in place, it’s time to perform the database migration:
SQL> lb update -changelog-file controller.xml -log
--Starting Liquibase at 2024-08-15T11:21:32.274730132 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Running Changeset: example-module.xml::mcb001::martincarstenbach
MLE module DEMO_MODULE compiled
UPDATE SUMMARY
Run: 1
Previously run: 0
Filtered out: 0
-------------------------------
Total change sets: 1
Liquibase: Update has been successful. Rows affected: 1
Produced logfile: sqlcl-lb-1723713692274.log
Operation completed successfully.
This looks promising, let’s see if the module was created (I’m pretty sure it was…)
SQL> select
2 module_name
3 from
4* user_mle_modules;
MODULE_NAME
______________
DEMO_MODULE
The module is present, and it contains the code specified in the Liquibase changeset
SQL> select
2 replace(text, chr(10), '') text_wo_nl
3 from
4 user_source
5 where
6* name = 'DEMO_MODULE';
TEXT_WO_NL
_______________________________________________________________
/**
* A simple function returning a string with a nice greeting
* @param {string} who who do you want to greet?
* @returns {string} the completed greeting
*/
export function hello(who) {
return `hello, ${who}`;
}
8 rows selected.
Liquibase knows about it, too:
SQL> lb history
--Starting Liquibase at 2024-08-15T11:25:23.315218209 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Liquibase History for jdbc:oracle:thin:@localhost:1522/freepdb1
- Database updated at 8/15/24, 9:21 AM. Applied 1 changeset(s), DeploymentId: 3713692836
example-module.xml::mcb001::martincarstenbach
Operation completed successfully.
Test
Let’s run a quick test, too. I added the following changeset to the changelog:
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
<changeSet
id="mcb002"
author="martincarstenbach"
failOnError="true"
runOnChange="false"
runAlways="false">
<n0:createOracleFunction objectName="HELLO" objectType="FUNCTION" ownerName="EMILY" replaceIfExists="false" >
<n0:source><![CDATA[
create function if not exists hello(
p_who varchar2
)
return varchar2 as
mle module demo_module
signature 'hello';
/
]]>
</n0:source>
</n0:createOracleFunction>
</changeSet>
</databaseChangeLog>
And voila, it works, too:
SQL> lb update -changelog-file controller.xml -log
--Starting Liquibase at 2024-08-15T11:27:35.449049342 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Running Changeset: hello-function.xml::mcb002::martincarstenbach
Function HELLO compiled
UPDATE SUMMARY
Run: 1
Previously run: 1
Filtered out: 0
-------------------------------
Total change sets: 2
Liquibase: Update has been successful. Rows affected: 1
Produced logfile: sqlcl-lb-1723714055448.log
Operation completed successfully.
SQL> select hello('mle')
2* /
HELLO('MLE')
_______________
hello, mle
A big shout out to Jeff Smith for his help. Read more about runOracleScript on his blog.
Happy automating!
Martins Blog 