Using server-side JavaScript in APEX processes

You may have read how to use server-side JavaScript code powered by Multlingual Engine (MLE) to validate page items in an APEX application in a previous article on this blog. The summary section promised more content, and here it is.

In this installment of the mini-series you will learn how to write more complex server-side logic using MLE/JavaScript ECMAScript modules.

Sample App

The sample application for this article is a tiny bit more realistic than the one from the previous example. It is assumed that you have access to APEX 23.2.x hosted by an Oracle Database 23c Free database. Have a look at the earlier article on this blog for more details how to spin such an environment up with little to no effort involved.

The application to be created records working hours, and keeps track of breaks. Since typing timestamps into a form is a little tedious, a quick time-entry page allows me to enter when I start my working day, take/finish a break, and finally when it’s time to close the laptop’s lid and call it a day. The page I created is shown here:

Since this is a pet project of mine I didn’t bother with TLS security and a few other things you’d certainly do for a production build. I haven’t seen a need to deploy this app outside my APEX lab either, I hope you’ll excuse these shortcoming and avoid them in your applications.

Tables

Let’s start by creating the necessary tables. The easiest way to do this is via the SQL Workshop in APEX. Switch to SQL Commands and run the following DDL to create table WORKING_TIME

create table "WORKING_TIME" (
	"ID"          number
		      generated by default on null as identity
	              not null enable,
	"START_TIME"  timestamp(6) not null enable,
	"FINISH_TIME" timestamp(6)
);
alter table "WORKING_TIME"
	add constraint "WORKING_TIME_ID_PK" 
            primary key ( "ID" ) using index enable;
alter table "WORKING_TIME" add constraint "WORKING_TIME_START_TIME_UNQ" 
        unique ( "START_TIME" )
	  using index enable;

The next table tracks breaks during the day

create table "BREAK_TIME" (
    "ID"              number
                      generated by default on null as identity
                      not null enable,
    "WORKING_TIME_ID" number,
    "START_TIME"      timestamp(6) not null enable,
    "FINISH_TIME"     timestamp(6)
);
alter table "BREAK_TIME"
    add constraint "BREAK_TIME_ID_PK" primary key ( "ID" )
        using index enable;
alter table "BREAK_TIME"
    add constraint "BREAK_TIME_WORKING_TIME_ID_FK" 
        foreign key ( "WORKING_TIME_ID" )
        references "WORKING_TIME" ( "ID" )
    enable;

create index "BREAK_TIME_I1" on
    "BREAK_TIME" (
        "WORKING_TIME_ID"
    );

With the tables created it’s time to build the application.

Quick Time Entry

Switch to the App Builder and create a new application. Sticking with the most basic options will suffice, using the Redwood theme is my only deviation from the defaults.

Next, create a new blank page. The intention of this page is to allow for a push-a-button time entry. Four buttons are eventually added, each pointing to an APEX process backed by JavaScript/MLE:

start the working day
start a break
finish a break
wrap up the working day

As you would have imagined, these operations are directly related to inserting and updating data in the previously created tables.

Use the following steps to create the main region:

Create a new static content region in the page’s body and name it time entry
Add a new hidden page item to the new region’s body and name it PN_TODAY, replacing N with your page number (it’s P2_TODAY for me)
Assign a default value to the page item
- select your page item
- use the “defaults” box to set the type to expression
- language to pl/sql
- expression to to_char(sysdate,'dd.mm.yyyy')
Now you can set the static region’s Source/HTML Code attribute to Time entry for &P2_TODAY!HTML.

Rather than typing down a long list of instructions for all 4 buttons to be added I’d like to focus on the one ending the work day as it’s easiest to explain and showcase how to use MLE/JavaScript. The other 3 buttons behave in the same way. Before MLE code provided as ES Modules can be added to the page, it must be created.

Adding MLE code to the page

Adding MLE code stored as a MLE module requires a few steps to be completed first:

Creation of the MLE module
Creation of an MLE environment referencing the new MLE module
Assigning the new MLE environment to the application

These steps are discussed next.

Create the MLE module

Switch to SQL Workshop > Object Browser and right-click on MLE Modules – JavaScript. Select Create MLE Module – JavaScript, then name the module TIMEKEEPING_MODULE.

Enter the following JavaScript code into the editor:

/**
 * Finish the working day. No checks are performed if the working day has been
 * closed yet.
 * @param when a timestamp indicating when the working day has finished.
 */
export function finishWorkingDay(when) {
    if (when === null) {
        throw new Error('cannot pass an empty date to finishWorkingDay()');
    }
    // get working_time.id for later, bail out if no start time has been recorded for today
    let result = session.execute(`select
        id,
        start_time,
        start_time < :when as sanity_check
      from
        working_time
      where
          trunc(start_time) = trunc(:when)
      `, [when, when]);
    if (result.rows === undefined || result.rows.length === 0) {
        throw new Error('cannot finish the working day, it has not yet been started');
    }
    if (!result.rows[0].SANITY_CHECK) {
        throw new Error('cannot finish a working day before it started');
    }
    const id = result.rows[0].ID;
    result = session.execute(`update working_time
      set
        finish_time = :when
      where
        id = :id`, [when, id]);
    session.commit();
}

Before you can use the MLE module you have to create an MLE environment first. Still in the object browser, create a new MLE env and name it TIMEKEEPING_ENV. Assign timekeeping as the import name, pointing to the TIMEKEEPING_MODULE you just created.

The final step is to assign the newly created environment to the APEX application. To do so, navigate to Shared Components. Click on Security Attributes and scroll all the way down until you see the Database Session tab. Enter the new MLE environment right underneath Parsing Schema. as shown in this screenshot:

Defining a global MLE env for the application

Adding the MLE code to the page

If you haven’t done so already, navigate to the Page Builder. Switch to the Processing Tab on the left-hand side of the Page Builder. Create a new process with the following properties:

Identification

name: finish_working_day
type: execute code

Source

location: local database
language: JavaScript/MLE
JavaScript code:

const { finishWorkingDay } = await import ('timekeeping')

finishWorkingDay(new Date())

Translated into plain English this snippet instructs the MLE runtime to dynamically import finishWorkingDay() from the module pointed to by the timekeeping import name. Earlier on you defined this to be the TIMEKEEPING_MODULE MLE module. Thanks to the MLE environment assigned earlier, APEX knows how to resolve the import name. If you get an ORA-04161: Error: Cannot load ES module: timekeeping something is wrong with

the MLE environment itself (check your import names)
or you didn’t assign the module as the application’s module in the shared components section.

Let’s add a new button to the page calling the process when clicked:

name: Finish_working_day
button position: close
Server Side Condition > when button pressed: finish_working_day

That’s it! If you run the page you should be able to click on the button – which is going to throw an error at the moment but that’s exactly what the code does. Go on, add the other buttons and experiment with MLE code in APEX, it’s fun ;)

Summary

Adding MLE modules to pages in APEX is really quite straight forward once you adhere to the rules laid out in this article.