Support for In-Database JavaScript is one of the most exciting new features in Oracle Database 23ai. JavaScript is the first language supported by Oracle Multilingual Engine (MLE), powered by GraalVM. Oracle APEX in turn was one of the first platforms to support server-side JavaScript.

If you want to get started developing using MLE and APEX without having to concern yourself with the installation & configuration of either database and APEX , using an Always-Free Autonomous Database – Serverless (ADB-S) provides an easy, convenient way to go.

At the time of writing Always Free 23ai Databases were available in a subset of regions. Whilst this is undoubtedly going to change over time, review the documenation with regards to availability of release 23ai in your region.

This article assumes that you already have an Always Free Oracle Cloud Infrastructure (OCI) account, if not, have a look at the landing page for more information and instructions how to sign up.

Create an Always Free Autonomous Database – Serverless using the console

After the sign-up procedure has completed, head over to the https://www.oracle.com/cloud/sign-in.html and log in with your credentials. You will see the OCI console, which is your starting point to creating resources in OCI.

Now, follow these steps to create an Always Free Autonomous Database 23ai:

• Open the Oracle Cloud Infrastructure Console by clicking the navigation icon (the “hamburger” menu) next to Oracle Cloud.
• From the Oracle Cloud Infrastructure left navigation menu click Oracle Database and then Autonomous Transaction Processing.

You should be presented with an overview of your Autonomous Databases in your compartment. Please note that you must create your Always-Free ADB-S instance in your home region.

You can optionally choose a different Compartment for your ADB-S instance. An OCI compartment allows you to logically group resources. Whilst creating compartments is a great idea for production use, it is not required for this short demo.

Hit the Create Autonomous Database button to start provisioning your database. For the sake of this demo, make sure to create an Always Free instance as shown in the screenshot below:

As always, you should provide a strong ADMIN password. Keep it in a safe place, you will need it later. For this demo, you should also set the Secure access from allowed IPs and VCNs only. This prevents bad actors from accessing your database whilst at the same time allowing you to connect via the public Internet. You can hit the Add my IP Address button to allow access from your IP address only. It is also a good idea to tick the Require mutual TLS (mTLS) authentication checkbox to further strengthen your security posture.

The official documentation provides further detail about the creation of Autonomous Database.

Create an Always Free Autonomous Database – Serverless using the command line

If you are more of a command line person you can simplify the process of creating the database with the OCI CLI. The OCI CLI is available with Cloud Shell, which is great!

Using the cloud shell means you don’t have to install and configure the CLI for use with your tenancy. But first of all you need a compartment ID (see above for a discussion of compartments) for your database. Using the browser interface, navigate to Identity and Security > Identity > Compartments to get an overview of all your compartments. You should see at least 2: your root compartment and ManagedCompartmentForPaaS. Either create a new one or pick your root compartment, it doesn’t matter for this demo. If you’re planning something more serious than a playground environment you should read up on Compartments; they are essential for any cloud deployment. Hover the mouse over a compartment to copy its Oracle Cloud ID (OCID).

Create the ADB-S instance in cloud shell now; feel free to update values as you see fit, except for the admin-password which you really need to update.

oci db autonomous-database create \
--db-name apexmle \
--admin-password 'yoursupersecretadminpassword' \
--db-version 23ai \
--db-workload OLTP \
--display-name apexmle \
--is-free-tier true \
--is-mtls-connection-required true \
--whitelisted-ips '[ "your.numeric.external.ip.address" ]' \
--compartment-id 'your compartment ocid'

Please refer to the CLI documentation for all the options available to the create command.

Accessing APEX

As soon as the ADB-S instance has been provisioned, you can access APEX by clicking on the databases’s display name in the Autonomous Database Overview page (Hamburger Menu > Oracle Database > Autonomous Database)

You find the link under APEX instance. Click on the link, followed by a click on the Launch APEX button. You will be greeted with the administration login from where you can create a workspace for your application. Use the previously assigned ADMIN to log in and create the workspace.

Once the workspace has been created, sign out of the admin interface and log in to the workspace using the workspace administrator account you just created. You should have all the neccessary privileges to create and execute server-side JavaScript code.

Create a small APEX app using server-side JavaScript/MLE

The demo application uses the popular validator module to perform string validation.

Please refer to validator’s GitHub project site for more details about the project’s license and implications of use. The article assumes your legal and IT Security departments (as well as any other party) agreed that it’s safe to use the module in your code. Using 3rd party code in your application typically requires certain compliance steps to be completed which are out of scope of this article.

Creating the MLE module and other schema objects

Congratulations! The hard work is over. Let’s create the app and use some JavaScript goodness. Start by clicking on SQL Workshop > SQL Commands and enter the following DDL statement:

create table person (
    id                number generated by default on null as identity
                      constraint person_id_pk primary key,
    first_name        varchar2(255 char) not null,
    middle_name       varchar2(255 char),
    last_name         varchar2(255 char) not null,
    address_line_1    varchar2(255 char) not null,
    address_line_2    varchar2(255 char),
    post_code         varchar2(32 char) not null,
    town              varchar2(255 char) not null,
    email_address     varchar2(255 char) not null
);

This isn’t a very good data model for storing people’s addresses in the database for reasons not in the scope of this article. It does provide the bare minimum for this blog post, though.

Submit the create table statement.

Next, let’s create the MLE module. Click on SQL Workshop > Object Browser.

You see MLE Modules – JavaScript in the tree on the left. Right-click it and select Create MLE Module – JavaScript. Switch to the URL tab and provide the URL for the ESM module, as shown in the screenshot. Rather than using latest, you see the exact version specified in the URL, which is generally a good idea to avoid nasty surprises should a newer version introduce breaking changes.

Click on Create Module to create the MLE module in the database. In addition to the MLE module, you also need an MLE environment allowing APEX to resolve import statements used in a later part of this article. Right-click on MLE Environments and select Create MLE Environment to start the creation of one.

• Name the environment VALIDATOR_ENV
• Leave language options blank
• Click the Create button

Expand the MLE Environments node in the object browser to the left and click VALIDATOR_ENV to open the environment’s details page. Currently, the environment is empty; you must add an import name by clicking Add Import. Complete the dialogue by setting the following properties:

• Module owner to your workspace schema
• Module name to validator_module
• Import name to validator

A click on Create creates the import.

You can read more about MLE Modules and Environments in Chapter 2 of the MLE JavaScript Developer’s Guide.

Creating the APEX application

The stage is set to finally use the validator module’s isEmail() function in a validation. Click on App Builder > Create to start creating the app. Just give it a name and hit Create Application. You find yourself in the App Builder interface.

Defining the MLE Environment

APEX must be told which MLE environment to use by default, so let’s set that now. Navigate to Shared Components > Security Attributes > Security. Scroll down until you find a section titled Database Session. Set the MLE Environment to the newly created VALIDATOR_ENV as shown in this screenshot:

Don’t forget to apply the changes.

Creating the user interface

Let’s create the UI. Scroll all the way back up, then click on your application name in the top left corner (application nnn). Create the report and form by clicking on Create Page, followed by Classic Report.

• Name the page Person
• Select Include Form Page (which you should name Edit Person)
• Assign the Person table as the data source
• Leave the remaining settings at their defaults
• Finally, click Next.

The Primary Key column should automatically be detected as ID. Hit Create Page.

APEX will create two pages for you now: the classic report (should be page #2) and the form (page #3). The classic report displays information; the form page is used for creating new rows in the table or editing existing ones. Both creating and editing can benefit from the validation.

Navigate to page number 3 using the page finder at the top. You should see the form named Edit Person in the components tree on the left.

Right-click on P3_EMAIL_ADDRESS and select Create Validation. Create the validation as shown:

• Name the validation check email
• Add an error message
• Set the validation properties to

  • Type: function body (returning boolean)

  • Language: JavaScript (MLE)

  • JavaScript function body to:

    const { default: v } = await import ('validator');
    return v.isEmail( apex.env.P3_EMAIL_ADDRESS );
    

Don’t forget to save.

The completed validation can be seen in this screenshot:

Validations in action

Navigate to page number 2 (the classic report) and run the page (Alt-F8). Click on the create button to create a new entry to the table and see the validation in action.

In case of an invalid email address, the validation fires:

As soon as you enter a valid email address, the record can be created and is shown:

Summary

JavaScript modules available on NPM are a great time saver, provided their licenses are compatible with your project. This post showed how to create MLE modules in APEX and use their functionality in a page item validation. Using an Always Free Autonomous 23ai Database instance greatly simplified the setup work.