By Joel Kallman

The Oracle Database Multilingual Engine (MLE) is an exciting new feature, targeted for a future release in Oracle Database.  This feature will enable the inclusion of other programming languages for execution inside the database.  While the native procedural language of Oracle Database will remain the ever-powerful and elegant PL/SQL, extending the database with support for other languages opens up the creation of Oracle Database powered applications to a plethora of developers with other skill sets.

The Oracle Database Multilingual Engine is based on the work done for GraalVM, a high-performance virtual machine developed by the researchers at Oracle Labs.  GraalVM is an open source project which is already used with success at many high-profile companies around the world (Twitter, for example).

The Oracle Labs, Oracle Database & Oracle APEX teams have collaborated to provide an online, free preview of what this integration will look like.  Beginning today and running through December 2018, anyone can sign up for a free hosted preview ("early adopter") at https://apexea.oracle.com.  The purpose of this site is to offer our many Oracle Database and Oracle APEX customers a glimpse of what the future holds, as well as obtain your feedback and suggestions.

How do you get started?

To get started, you first need to sign up for a workspace at https://apexea.oracle.com.  Click the Request a Workspace button and complete the 3 short steps.  Within a minute, you should receive an email with a link to verify your account and create your workspace.  Upon first login, you'll also be prompted to setup a password for your associated APEX account credentials.  These credentials will only be necessary if you create or install an APEX application.

You don't know anything about APEX.  How can you test MLE?

Simple.  Once you have successfully logged into your workspace, click the green SQL Workshopicon and then click SQL Commands.  This is a SQL command processor, just like you would find with sqlcl or SQL*Plus.  Using this command interface, you can execute SQL & PL/SQL.  You can create and drop tables.  You can issue any SELECT statement.  You can now also execute JavaScript and Python from the command processor.  As a quick example, select Language = JavaScript and enter the following code:

console.log("hello world!");

and click the Run button.  The output should be displayed in the Results region at the bottom.

To try out Python (Python support is experimental, at this stage), select Language = Python and execute the following code:

from datetime import date
today = str(date.today())
print(today)

Let's try another simple example.  Enter the following code in the SQL Command processor with Language = JavaScript:

var currentDate = new Date(),
day = currentDate.getDate(),
month = currentDate.getMonth() + 1,
year = currentDate.getFullYear();
console.log(day + "/" + month + "/" + year)

click the Run button, and the current date will be printed in the results.

For the next example, you will need to create some sample database objects in your workspace.  Click the SQL Workshop tab, then go to Utilities -> Sample Datasets.  Install the EMP / DEPT sample dataset, which will create both tables and insert sample data into them.  After creating the sample tables, navigate back to SQL Commands.

To show the integration between the SQL engine and JavaScript, ensure that the Language select list is set to JavaScript and enter the following code:

for (var row of mle.sql.execute("SELECT EMPNO, ENAME FROM EMP ORDER BY EMPNO").rows) {
  console.log("[" + row[0] + ", " + row[1] + "]")
};
 

This will use the MLE engine to execute the SQL query, and then in JavaScript, iterate through the results and print them.  But what if you wanted to include a bind variable in your query?  It's actually quite easy with the SQL driver for JavaScript.  You simply need to provide an array of the bind values following the SQL statement, with the position in the array corresponding to the position of the bind variable in the SQL statement.  As a complete example:

for (var row of mle.sql.execute("SELECT EMPNO, ENAME FROM EMP WHERE SAL > :sal AND DEPTNO = :deptno ORDER BY EMPNO", [ 1000, 30 ]).rows) {
  console.log("[" + row[0] + ", " + row[1] + "]")
};

In this example, the value 1000 will be bound to bind variable :sal and 30 will be bound to bind variable :deptno.

For more information on the SQL driver for JavaScript in MLE, please refer to the Callouts section of the MLE documentation.

It is also possible to dynamically execute JavaScript or Python from PL/SQL, using the PL/SQL package DBMS_MLE.  Data can be exchanged both ways, between JavaScript and PL/SQL.  In SQL Commands, set the Language to "SQL and PL/SQL" and execute the following code:

declare
  l_script_source clob;
  l_script_handle dbms_mle.script_handle_t;
  l_script_result number;
begin
  l_script_source := q'~
  mle.binds.totalsal = 0;
  for (var row of mle.sql.execute("SELECT SAL FROM EMP WHERE SAL > " + mle.binds.salary).rows) {
  mle.binds.totalsal += row[0];
  }
  ~';
  l_script_handle := dbms_mle.create_script( l_script_source, 'JS' );
  dbms_mle.bind_variable( l_script_handle, 'salary', 1000 );
  dbms_mle.execute_script( l_script_handle );
  dbms_mle.variable_value( l_script_handle, 'totalsal', l_script_result );
  dbms_mle.drop_script( l_script_handle );
  dbms_output.put_line( 'Total Salary: ' || l_script_result );
end;

In this block of PL/SQL, you are creating a script handle for the JavaScript code, binding a value for salary (represented as mle.binds.salary in the JavaScript code), executing the script, which will execute the query and then iterate through the results, computing a sum of the salaries, and then retrieving the output value which was assigned in the script (i.e., mle.binds.salary).

Where can you use JavaScript or Python in an APEX application?

If you are knowledgable with APEX, you'll be able to appreciate the combination of APEX + MLE, as this is where the integration of these two technologies really shines.  JavaScript is available in the APEX application definition, in page processes, validations, computations.  You can author JavaScript functions returning SQL queries as the dynamic source for classic reports, calendars & charts.  And you can use Python for page processes and validations.

When used within an APEX context, you are able to reference the value of page items (to both get and set the item values) using mle.binds.PAGE_ITEM.  For example, the following code could be used in a page validation of type "JavaScript Function Body (returning Boolean)":

var l = mle.binds.P3_NAME.length;
return l >= 3 && l <= 20;

The length of the value of page item P3_NAME is referenced via mle.binds.P3_NAME.length.  The function body then returns the boolean result of the expression, if the length is greater than or equal to 3 and less than or equal to 20.

Binding of page item values to bind variables in a SQL statement is similar to the PL/SQL example above, but you have to remember that the page item values will be available via mle.binds.PAGE_ITEM.  Assuming you had page 2 in your APEX application with page items for salary and department number, an example would be:

for (var row of mle.sql.execute("SELECT EMPNO, ENAME FROM EMP WHERE SAL > :sal AND DEPTNO = :deptno ORDER BY EMPNO", [ mle.binds.P2_SAL, mle.binds.P2_DEPTNO ]).rows) {
  console.log("[" + row[0] + ", " + row[1] + "]")
};

For APEX developers, the easiest way to get started is to install the APEX + MLE Sample Application.  To do this:

1. Login to your workspace
2. Click App Gallery
3. Click MLE + APEX Sample Application
4. Click Install App, Next and Install App.
5. Click the Run button.

It's that simple.  You'll need to login with the credentials of your Application Express account, which you would have setup when you first logged into your workspace.  The sample application is unlocked so you can examine how the JavaScript & Python code is used within the app.

Where can you access additional documentation?

The Github MLE repository contains documentation for the SQL driver which is built into MLE. It is automatically exposed in the mle.sql JavaScript namespace within APEX. The Help tab of the APEX Page Designer contains additional code examples, when you're editing the JavaScript or Python code attribute in the Property Editor.

How do you provide feedback?  Where can you get support?

This hosted early adopter program is not supported by Oracle Support.  It is not intended for production applications.  You will not be able to import the APEX applications developed on this site into any other APEX installation.  It is only intended for evaluation and to get your feedback and suggestions.  We welcome any and all feedback, in the MLE discussion forum in the Oracle Database Developer community.

If you have any questions, please let us know in the MLE discussion forum.  We look forward to your feedback.  We are excited about the future with Oracle Database, APEX and MLE!