#JoelKallmanDay

Every year in October, the Oracle Community shares technical (or non-technical) content to gain some attention. I began participating in 2021 with my (almost) first post on this blog. I remember feeling like I was part of something. A few years later, I am part of the Oracle ACE program (which is still unreal to me to be honest!), I work at Insum with many talented colleagues and I am still enjoy sharing content with the Oracle community, especially the Oracle APEX one. If you haven’t yet or are hesitant to start doing it 👇

Sharing knowledge is the most fundamental act of friendship. Because it is a way you can give something without loosing something.
- Richard Stallman -

This year, I decided to write about a topic that I believe fits perfectly with #MOCA (Make Oracle Cool Again) 🤓

Introduction

Unless you've been living in a cave recently, you've probably heard about the new Oracle database release 23ai. This release brings the ability to use JavaScript directly in your database, thanks to the Oracle Database Multilingual Engine (MLE) powered by GraalVM.

You're probably wondering what the point is since we already have PL/SQL. That's a valid question, and even though I love JavaScript, I won't use it for everything. However, we have to admit that the JavaScript ecosystem is much larger than the PL/SQL one. There aren't many PL/SQL libraries available, but it's easy to find a JavaScript library. This is simply because PL/SQL is only used with Oracle Database, while JavaScript is almost everywhere these days!

For example, a few months ago, I created a simple Oracle APEX application that reads EXIF data from photos and displays it nicely. It would have been possible to do this in PL/SQL, but I would have needed to write an entire parser. By using an existing JavaScript library, exifr, I was able to complete it in just a few hours 😍

Alright, I think that's enough for the introduction. Let's take a closer look at the implementation 🤓

Configuring the database objects

To use JavaScript code in the database, you need to set up two database objects: an MLE Module and an MLE Environment. But first, let's understand why this is necessary. The Oracle Database Multilingual Engine (MLE) uses execution contexts to run JavaScript in the database. The MLE Environment allows you to customize how these contexts are executed by providing two things:

• the language options, you can for example enforce strict mode using js.strict=true. The full list of options is available in the documentation

• the list of the modules to import

The good news is that you can do this in just a few clicks without leaving the Oracle APEX builder.

Configuring the MLE Module

To start configuring the MLE Module, go to the Object Browser in your Oracle APEX environment. Scroll down the list of available objects until you find the section labeled "MLE Modules - JavaScript." Right-click on it to open a context menu. From the options, choose "Create MLE Module - JavaScript" to begin creating a new JavaScript module.

A modal page will open, allowing you to create your module. You have several options for adding your JavaScript code to the module. You can upload a file directly from your computer, which is useful if you have already prepared your code in a file format. Alternatively, you can paste the source code directly into the provided text area, which is convenient for quick edits or smaller scripts. Lastly, you can use a URL to link to an external source where your JavaScript code is hosted. This flexibility in input methods makes it easy to integrate your JavaScript code into the MLE Module.

For this application, I choose to go with the URL and use one of the CDN available for the exifr library: https://cdn.jsdelivr.net/npm/exifr@7.1.3/dist/full.esm.js. Here is the screenshot of the configured MLE Module

Configuring the MLE Environment

At the bottom of the object list, right-click on the MLE Environments entry and then select the "Create MLE Environment" option.

In the dialog box, enter a name for your environment and click "Create MLE Environment."

The final step is to import the module you created into the new environment. To do this, select the MLE Environment and click the "Add Import" button.

In the dialog, you can select the Module Owner, the module Name and finally an Import Name. See the configuration below

Oracle APEX configuration

Now that the MLE Module and Environment are set up, you need to specify which environment your Oracle APEX application will use. To do this, go to the Security Attributes of your app under Shared Components. On that page, click on the Database Session tab and select the MLE Environment you created earlier (see screenshot below).

Writing server-side JS within Oracle APEX

After setting everything up, we still need to write JavaScript code. Let's look at a simple EXIF app as an example. The user interface is pretty simple, with only one file browser page item, and upon upload, the data is displayed. Here is what it looks like, and you can also test it yourself here.

Storing the file in a collection

The page is quite simple, with one page item P1_FILE of type File Upload. It uses the table APEX_APPLICATION_TEMP_FILES as the storage type and restricts it to images only.

On the change event of the page item, a Dynamic Action is submitting the page.

On page submit, a PL/SQL process stores the file content into a collection using the following code.

declare
    l_blob_content apex_application_temp_files.blob_content%type;
    l_mime_type apex_application_temp_files.mime_type%type;
    l_filename apex_application_temp_files.filename%type;
    l_id apex_application_temp_files.id%type;
begin
    apex_collection.create_or_truncate_collection(
        p_collection_name => 'C_UPLOADED_FILE'
    );

    select blob_content, mime_type, filename, id
      into l_blob_content, l_mime_type, l_filename, l_id
      from apex_application_temp_files
     where name = :P1_FILE;

    apex_collection.add_member(
        p_collection_name => 'C_UPLOADED_FILE',
        p_n001 => l_id,
        p_c001 => l_filename,
        p_c002 => l_mime_type,
        p_blob001 => l_blob_content
    );
end;

Extracting the EXIF data

Right after storing the file in the collection, another process uses the exifr library to extract the data. The process looks the same as a regular PL/SQL page process, except that the Language attribute is set to JavaScript (MLE) and the code is written in JavaScript 🤩

But, let’s see in detail the JS code there

// First we have to load the module
const { default: exifr } = await import ('exifr');

// Getting the file name from the page item P1_FILE
var fileName = apex.env.P1_FILE;

// Getting the blob from the database
// the blob is converted to an Uint8Array
// For more info about type conversion: https://docs.oracle.com/en/database/oracle/oracle-database/23/mlejs/mle-type-conversions.html
var result = apex.conn.execute(`
        select blob_content
          from apex_application_temp_files
         where name = :name`,
    {
        name: fileName
    }, {
        fetchInfo: {
            BLOB_CONTENT: {
                type: oracledb.UINT8ARRAY
            }
        }
    });

// Call the parse function of the exifr library
// The output is then assign to the page item P1_DATA
exifr.parse(result.rows[0].BLOB_CONTENT.buffer).then(output => apex.env.P1_DATA = JSON.stringify(output) );

Displaying the data

The collection is used to display the image using the built in Image template, for the tabular data, a simple json_table is used to parse the JSON stored in the P1_DATA page item. For example, here is the query used to display the Camera details

select 
    make,
    model,
    lens_make ||' ' || lens_model as lens,
    focal_length,
    '1/' || round(1 / exposure_time) || ' seconds' as exposure,
    aperture,
    iso,
    flash
  from json_table(:P1_DATA, '$'
    columns(
        make          varchar2(150) path '$.Make',
        model.        varchar2(150) path '$.Model',
        lens_make     varchar2(150) path '$.LensMake',
        lens_model    varchar2(150) path '$.LensModel',
        focal_length  varchar2(150) path '$.FocalLength',
        exposure_time number        path '$.ExposureTime',
        aperture      number        path '$.ApertureValue',
        iso           number        path '$.ISO',
        flash         varchar2(150) path '$.Flash'
    )
)

Conclusion

In this blog post, we have seen how to configure the required pieces to get started using server-side JavaScript code within an Oracle APEX application. The example demonstrates the power of leveraging the JS ecosystem, where open-source libraries can do pretty much everything. However, I recommend you think carefully before integrating any dependency within your project. As for APEX plug-ins, you have to consider the risk in terms of security and obsolescence. The second thing, which is really important and so often forgotten, is licensing. You have to ensure that the licensing of the third-party library is compatible with your own license.

That said, I am really excited to see how we can use this power in the near future, especially with Python coming soon. Well done Oracle: this is an amazing feature!-

References

• MLE documentation: https://docs.oracle.com/en/database/oracle/oracle-database/23/mlejs/introduction-to-mle.html

• Martin Bach blog: https://martincarstenbach.com/

• Oracle samples GitHub repository: https://oracle-samples.github.io/mle-modules/

• APEX + Server-Side JavaScript (MLE) workshop: https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=822

• Say hello to JavaScript for server-side development in APEX!: https://youtu.be/ipNLvrwcCj0?si=EwBgKagomm2_tosg

• APEX 23.1 and server-side JavaScript: Explore the power of GraalVM/MLE: https://youtu.be/fpRfB67VlxQ?si=nIZs8r3S9S8lwKhX

• APEX + Server-side JavaScript = Awesome!: https://youtu.be/voolgTBoPyE?si=Kw-7FNSq2wk_o0GJ

• JavaScript comes to Database server side code!: https://youtu.be/gTvIZUBekKE?si=NXJfmoKavfwA1Tvs