X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

MLE and the Future of Server-Side Programming in Oracle APEX

Salim Hlayel
Principal Product Manager

Oracle APEX

Oracle Application Express (APEX) is a unique low code development platform that runs as a part of the great Oracle Database platform. Having the engine of APEX inside the Oracle Database grants it a premium privilege to access all the goodies and features that come with Oracle Database allowing the number one low code platform to be treated as a first-class citizen. No doubt this has opened the door so wide to unlimited possibilities.

Oracle Database 21c

The new Oracle Database 21c has been announced on the 13th of January 2021. With over 200 new innovations and features, Oracle Database is once again competing with itself.

One feature that makes its way in this release is the ability to execute JavaScript code inside the database. A great article titled “Multilingual Engine: Executing JavaScript in Oracle Database” written by my colleague Alina Yurenko explains the feature in more detail. It is called Multilingual Engine (MLE) and it is available in the database due to the great effort of embedding GraalVM Multilingual Engine inside the Oracle Database 21c. In this post, I will not dig deep into the MLE technology as it was nicely explained in Alina’s post, which is highly recommended to read, but as the MLE name suggests, the engine will support more languages in the future. In 21c, JavaScript is a supported language in the MLE engine. It is more commonly called In-Database JavaScript.

What does In-Database JavaScript mean for the developers? Having the JavaScript running inside the database gives the developer the following advantages:

  • Developers can easily execute SQL from within JavaScript
  • Eliminating expensive network round-trips. Something that PL/SQL had for a long time and now is available for JavaScript server-side code
  • JavaScript data types are automatically mapped to the Oracle Database types allowing the developer to concentrate on solving the business problem instead of spending time and effort to map the data type of two different technologies
  • Central code repository for your backend code inside the database. This will make it the single source of truth and the one-stop shop to enhance, scale, and secure your two most valuable aspects of any system, the data, and the business logic
  • This also means that younger generations and JavaScript professional developers who chosen that technology as a life career can now be part of the team and participate in the code base of your applications
  • Leverage a huge set of JavaScript libraries for tasks that are hard to solve in PL/SQL

In this post, I will concentrate on the way JavaScript code can be executed from the Oracle Database and APEX and how to achieve a simple use case scenario.

Prerequisites

In order to test the In-Database JavaScript feature, you need an Oracle Database 21c and Oracle APEX 20.2 or higher.

The Always Free Autonomous Database 21c is being rolled to different regions. At the time of writing this post, it is available in four regions, your home region should be one of the followings:

  • Germany Central (Frankfurt)
  • US East (Ashburn)
  • US West (Phoenix)
  • UK South (London)

My tenancy for example has the US East (Ashburn) Home Region. This can be checked in the upper right corner of the Oracle Cloud Console.

Create an Autonomous Database making sure that the version for the database is 21c.

Or you can use a previously created Autonomous Database that has the version 21c.

You can check the database version by visiting the Autonomous Database Details page.

Now that our database is ready, let's import the following Oracle APEX Application. Import the application into your APEX Workspace under the 21c Autonomous Database and run it.

You will notice that the application is quite similar to the generated Hands-On spreadsheet lab application. As a matter of fact, it is already built on top of the same dataset. Navigate to the "Project Tasks Report" page, which is Page 4.

You will find a select list item for listing the projects and a yellow button with the "Extend Project Tasks" label. Select the project "Email Integration" to view its tasks in the interactive report.

In-Database JavaScript in Actions

A couple of requirements arise for this application:

  1. We need to be able to extend all tasks for the selected project by one day where their status is not "Closed".
  2. It should not be possible to change the "Assigned To" value if the status of the task is "Closed". In other words, if the task is already closed, we would love to prevent the changes on the owner of the task and retain the old owner.

We have a newly hired JavaScript developer. The developer will be given the task of fulfilling the above requirements. To extend all the tasks in the "Project Tasks Report" page by one day, he needs to loop over all the rows of the selected project and perform a simple check for the status of the task. If the status is not "Closed", he should issue an update statement to extend the end_date by one day.

The Database way and APEX enhancements

Let's examine the following PL/SQL code:

DECLARE
   ctx DBMS_MLE.context_handle_t;
   user_code clob := q'~
      const oracledb = require("mle-js-oracledb");      
      function extendProjectTasks( status ) {
          if (status !== "Closed") {
              return true;
          }
          else {
              return false;
          }
      }      
      const conn = oracledb.defaultConnection();
      for (var row of conn.execute("select id, status from project_tasks where project = 'Email Integration'").rows) {              
      if ( extendProjectTasks( row[1] )) {
        oracledb.defaultConnection().execute( "update project_tasks set end_date = end_date + 1 where id = :id", { id: row[0] } );
        console.log("The task with the ID: " + row[0] + " and the status " + row[1] + " has been extended successfully!")
        }      
      }; 
   ~';
BEGIN
   ctx := DBMS_MLE.create_context();
   DBMS_MLE.eval(ctx, 'JAVASCRIPT', user_code);
   DBMS_MLE.drop_context(ctx);
END;

DBMS_MLE package will be used to create a context and evaluate a JavaScript code. Finally, you need to close the created context.

Let's run the above PL/SQL script in SQL Workshop.

Open the SQL Workshop => SQL Commands. Select "PL/SQL" for the Language. Copy and Paste the above PL/SQL code and click the "Run" button.

Although the code looks neat. Oracle APEX team made it even simpler to work with JavaScript Server-Side code. Let's examine the following code:

function extendProjectTasks( status ) {
    if (status !== "Closed") {
        return true;
    }
    else {
        return false;
    }
}

for ( var row of apex.conn.execute( "select id, status from project_tasks where project = :project", { project: "Email Integration" } ).rows ) {
    if ( extendProjectTasks( row.STATUS )) {
      apex.conn.execute( "update project_tasks set end_date = end_date + 1 where id = :id", { id: row.ID } );
      console.log("The task with the ID: " + row.ID + " and the status " + row.STATUS + " has been extended successfully!")
    }
}

With the SQL Commands still open, copy and paste the above code. This time change the "Language" to "JavaScript (MLE)" and then click the "Run" button.

The APEX engine will take care of all those context handlings for you. It will also provide native JavaScript code editing support, provides easier parameters binding and database column mapping.

Our developer is so close to initiating the above code once the "Extend Project Tasks" button is clicked. He just needs to pass the page item of the currently selected project to the select statement. Simply replacing the 'Email Integration' with apex.env.P4_PROJECT will do the job. Open Page 4 and check the code available under the "Processing" tab for the "Extend Project Tasks" process.

Note the language selected for that process and the JavaScript Code text area. This is where the JavaScript developer can use his developed code. The final JavaScript code can be something similar to the following:

function extendProjectTasks( status ) {
    if (status !== "Closed") {
        return true;
    }
    else {
        return false;
    }
}

for ( var row of apex.conn.execute( "select id, status from project_tasks where project = :project", { project: apex.env.P4_PROJECT } ).rows ) {
    if ( extendProjectTasks( row.STATUS )) {
      apex.conn.execute( "update project_tasks set end_date = end_date + 1 where id = :id", { id: row.ID } );
    }
}

Notice also the usage of row.ID and row.STATUS. Oracle APEX makes it a lot easier to interact with the JavaScript code in a more native way. APEX exposes the namespace apex with the pre-defined variables apex.oracledb and apex.conn which automatically map to the Oracle SQL driver and oracledb.defaultConnection() to avoid the same bootstrapping code over and over again. In addition, all application and page items are exposed in the apex.env namespace, which can be used like apex.env.ITEM_NAME to read and write current session state. This is equal to the bind variable syntax in PL/SQL.

For example, you can use the following code to get/set page item P4_PROJECT value:

// getting page item P4_PROJECT value
const project = apex.env.P4_PROJECT;
// setting page item P4_PROJECT value
apex.env.P4_PROJECT = 'Email Integration';

On the page "Project Tasks Report" of the application, clicking the "Extend Project Tasks" button will extend a couple of tasks. The tasks where the status is "Closed" will not be extended as required. 

One might think, that this can be easily handled as part of the where statement. Something similar to this query will simply let us avoid the extra call to extendProjectTasks JavaScript function:

select id, status from project_tasks where project = :project and status != 'Closed'

That's actually true. I left it like that intentionally to show the ability to call a JavaScript function. The requirement might have been more complicated requiring more logic performed in that function. The point, it is now easy and native to our JavaScript developer to use his knowledge and contribute to extending our APEX application.

The second requirement, asks us to develop a validation mechanism. The validation should be performed on Page 5, the Project Task form page.

The validation will be applied to the P5_ASSIGNED_TO page item. The JavaScript code is a very simple statement returning a boolean when the status is not Closed.

apex.env.P5_STATUS !== "Closed"

Trying to change the owner of a closed task will lead to an error. 

Further MLE with APEX

You can do a lot more with this feature. The following are some points you might be interested to explore.

  • Utilizing Regular Expression in JavaScript to provide powerful validation (e.g. Validating Email Address)
  • Using JavaScript JSON Capabilities in your processing
  • Load External JavaScript Libraries

Although the last point is not yet directly supported by Oracle 21c, my colleague Stefan Dobre is providing a workaround to use some cool external JavaScript libraries. In his post, he shows all the above capabilities and their possible usage in Oracle APEX. 

Conclusion

Oracle Application Express (APEX) continues to use the gems that come with Oracle Database. Oracle 21c has a lot of those innovations embedded in the database and available to use out of the box with Oracle APEX. In this post, we have seen a glimpse of how you utilize the knowledge of your JavaScript in Oracle APEX. Starting from executing and evaluating your JavaScript in SQL Workshop Commands, to using JavaScript to perform a process, and finally, fulfilling a requirement by using JavaScript code in validation process.

The future of MLE has much more to offer for APEX developers. The inclusion of other server-side languages such as Python and the native loading of external NPM libraries will provide huge advantages to your team.

For further details watch this special edition for APEX OfficeHours.

Stay tuned, and happy APEXing.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.