Recently, while working on a joint project with the APEX team, the need arose to rewrite a piece of PL/SQL code in MLE/JavaScript.

The PL/SQL code in question roughly looks like this.

declare
    l_result boolean;
begin
    l_result := apex_application.g_flow_id is not null;
    if l_result then
        dbms_output.put_line('true');
    else
        dbms_output.put_line('false');
    end if;
end;

It allows me to figure out if the code is running as part of an APEX application, or elsewhere. This neat code snippet has originally been written by my colleague Steve Muench from the APEX team.

How should it be converted to JavaScript? The first thing that came to my mind was to use a bind variable and an anonymous PL/SQL block. And that turned out to be correct 🎉

Long story short, this function represents the JavaScript equivalent of the above PL/SQL block, neatly wrapped into an inline JavaScript function:

create or replace function is_apex_call
return boolean
as mle language javascript
{{
    const result = session.execute(
        'begin :isApexApp := apex_application.g_flow_id is not null; end;',
        {
            isApexApp: {
                dir: oracledb.BIND_OUT,
                type: oracledb.DB_TYPE_BOOLEAN
            }
        }
    );

    return result.outBinds.isApexApp;
}};
/

Calling this function from SQLcl for example returns false. Run it from APEX’s SQL Commands, and it will return true.

The trick is to declare a bind variable, isApexApp, to hold the result of the PL/SQL computation, and add its metadata as the second argument to session.execute() (🔗 docs). It’s important to declare it as an OUT bind, and its type. If you omit the type argument, Oracle will throw an PLS-00382: expression is of wrong type at you at runtime, so make sure you provide the correct type. And while you’re at it, you may want to add a unit test, too 😀

You can access the value of the bind variable via the result object’s (of type IExecuteReturn) outBinds as shown in the code.

Happy coding!