The availability of Oracle Database 23ai Release Update 7 marks the introduction of a number of new features for Multilingual Engine (MLE) and JavaScript. As always you find the details in Oracle’s JavaScript Developers Guide and the MLE Module API documentation on GitHub.

This post demonstrates one of the most exciting features: the Foreign Function Interface, or FFI for short.

Motivation

Working together closely with the wonderful APEX development organisation, the MLE team wanted to provide a more JavaScript-like developer experience when interacting with PL/SQL in MLE/JavaScript. Oracle provides a lot of functionality with the database, made available in form of PL/SQL packages. Therefore, writing anonymous PL/SQL blocks comes almost as second nature to someone who worked with the Oracle Database for a while. It does not necessarily do so for JavaScript developers. But this might change with the introduction of the FFI.

Let’s assume you would like to use DBMS_APPLICATION_INFO to instrument your JavaScript code. Previously, you would have done something along these lines in MLE/JavaScript.

/**
     * read the current values for module and action from the session and
     * return them in an object.
     * 
     * @returns {object} the current values for module and action
     */
    export function getModuleAction() {
    
        // invoke an anonymous PL/SQL block, reading module and action
        // for the current session and returning them as OUT binds.
        const result = session.execute(
            `BEGIN
                DBMS_APPLICATION_INFO.READ_MODULE(
                    :l_module,
                    :l_action
                );
            END;`,
            {
                l_module: {
                    dir: oracledb.BIND_OUT,
                    type: oracledb.STRING
                },
                l_action: {
                    dir: oracledb.BIND_OUT,
                    type: oracledb.STRING
                }
            }
        );
    
        // The OUT bind variables' values can be assigned
        // to JavaScript variables
        const currentModule = result.outBinds.l_module;
        const currentAction = result.outBinds.l_action;
    
        // ... and returned to the caller
        return {
            module: currentModule,
            action: currentAction
        }
    }

This might not come quite as natural to a JavaScript developer. Wouldn’t it be nicer to write something that looked and felt more like JavaScript? Most likely yes, enter the Foreign Function Interface!

Foreign Function Interface

The FFI aims at simplifying the way MLE/JavaScript developers write code interacting with PL/SQL. It does not matter whether the PL/SQL you want to use is provided by the database (documented in the PL/SQL Packages and Types Reference), or some in-house code. The principles are all the same.

The following steps are typically required to use the FFI:

  1. Perform a lookup of a database subprogram (function, procedure, package)
  2. If needed, declare and initialise out and in-out variables. JavaScript doesn’t have an equivalent to out and in-out variables, which is why a little sorcery is needed.
  3. Invoke the appropriate function or procedure

Note that the FFI object, plsffi, is available in the global scope. Although you can import mle-js-plsql-ffi, it often isn’t necessary.

Let’s look at a few examples.

Using stand-alone PL/SQL functions with the FFI

Assuming the following PL/SQL function is defined:

create or replace function answer(
        p_question varchar2
    ) return varchar2 as
    begin
        return 'the answer to ' 
            || DBMS_ASSERT.ENQUOTE_LITERAL (p_question)
            || ' is 42';
    end;
    /

Using the FFI you use it as follows:

/* javascript code ... */
    const answer_f = plsffi.resolveFunction('answer');
    const theAnswer = answer_f('life, the universe, and everything');
    console.log(theAnswer);

This is admittedly a very simple example, let’s have a look at how the initial code could be refactored to use the FFI.

Using a package with the FFI

The first example from this post can easily be refactored to use the FFI. Instead of using the anonymous PL/SQL block you could rewrite the function to this:

/**
     * read the current values for module and action from the session and
     * return them in an object.
     * 
     * @returns {object} the current values for module and action
     */
    export function getModuleAction() {
    
        const dbmsAppInfo = plsffi.resolvePackage('DBMS_APPLICATION_INFO');
    
        // out and in-out parameters to PL/SQL need to be declared
        const currentModule = plsffi.arg();
        const currentAction = plsffi.arg();
    
        dbmsAppInfo.read_module(currentModule, currentAction)
    
        return {
            module: currentModule.val,
            action: currentAction.val
        }
    }

That’s it! Hopefully you’ll find the FFI useful and it saves you some time. The JavaScript Developer’s Guide details all the nuts and bolts in chapter 6. You can also read about the API definition on GitHub.

Happy coding!