Oracle Multi-Lingual Engine

PI-Day example

Here is a very quick and easy test of the Oracle MLE, the engine that let you run JavaScript or Python stored procedures in the Oracle Database (currently in beta).

The MLE beta is provided as a docker image containing the Oracle Database 12.2 with the additional MLE libraries. I have created a VM on the Oracle Cloud in order to test it and show an end-to-end demo on Oracle Linux 7.

Get software

Here is where to download the database server with MLE beta:

Oracle Database MLE Download

and the SQLcl client

SQLcl Downloads

I’ll download them from my VM where I have no browser,

but downloading Oracle software requires a human intervention to accept the license terms. Then, what I do is start the download from my laptop (where I can sign-in with my Oracle account and accept the license), suspend the download and copy the link which contains an ‘’AuthParam value. Then I can use this URL to download it with wget in my VM, in the /tmp directory:

Once I have this in the /tmp directory, I install and start docker, unzip SQLcl and create the MLE container:

sudo su
# install docker and java from OL7 Add-Ons
yum-config-manager --enable ol7_addons
yum install -y docker-engine docker-compose java
#start docker
systemctl start docker
docker info | grep -A2 ^Storage
# install SQLcl, the client to connect to the database
unzip -d /tmp /tmp/sqlcl-18.4.0.007.1818.zip*
# start the MLE beta
docker load --input /tmp/mle-docker-0.3.0.tar.gz*
cat > /tmp/mle.yml <<'CAT'
version: '3.1'
services:
  ora122MLE:
    image: mle-docker:0.3.0
    restart: always
    environment:
      ORACLE_SID: CDB1
      ORACLE_PDB: PDB1
      ORACLE_PWD: oracle
    ports:
      - 1521:1521
    volumes:
      - "/var/tmp/oradata:/opt/oracle/oradata"
      - "/tmp:/home/oracle/myproject"
CAT
mkdir -p /var/tmp/oradata
docker-compose -f /tmp/mle.yml up

I could have built a docker image containing SQLcl but I’m not a container junkie. My OS is a virtual machine, SQLcl runs in a Java VM, do I really need to containerize this in between two VMs? Only the database runs in a container because this is how the beta is shipped.

[Update 17-MAR-2019] This is ok only with few round-trips between client and server. The implementation of docker-proxy to connect from host to container is not efficient at all, and then better install SQLcl in the container itself or another linked container.

The creation of the container, at docker-compose up, is long. Be patient. This is how database docker images work: they have to create the database when creating the container.

The reason is that a database stores persistent data, and then can be created only once the container started. And an Oracle database is a big beast to create. Here, I put the database in /var/tmp and the ‘myproject’ directory is mapped to /tmp.

The MLE beta is based on the official docker images build scripts provided by Oracle. Everything is fine when you see “DATABASE IS READY TO USE”

Let’s check with the password I defined in the docker-compose file, and create a DEMO user in the pluggable database:

/tmp/sqlcl/bin/sql sys/oracle@//localhost/pdb1 as sysdba
 connect sys/oracle@//localhost/pdb1 as sysdba
 grant dba to demo identified by demo;
 connect demo/demo@//localhost/pdb1

The documentation of the MLE beta is at: https://oracle.github.io/oracle-db-mle/docker. As I’m doing this on 14th or March, which is known as PI-Day in the countries which use the MM/DD format, I’ll create a PI function which can round to 2 decimals:

connect demo/demo@//localhost/pdb1
create javascript source named "test1.js" as
module.exports.PI=function (d) {
 return Math.round(10**d*Math.PI)/10**d;
}
/
create function PI(decimals in number)
return number as language javascript
name 'test1\.js.PI(decimals number) return number';
/
select PI(2) from dual;

In the previous beta, we had to use an external utility (dbjs) to load the JavaScript code. It is now much easier with the CREATE SOURCE statement.

I’m now ready with a MLE environment for further tests…