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:
/**
* 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
}
}
);
// Their value 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:
- Perform a lookup of a database subprogram (function, procedure, package)
- 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.
- 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 a stand-alone PL/SQL function 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:
/* code omitted for brevity ... */
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 first code example invoking DBMS_APPLICATION_INFO 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!
So far you saw how to work with PL/SQL IN and OUT parameters.
OK, for the sake of completeness, tell me how to deal with PL/SQL IN OUT variables
Let’s go back to the function providing you with the answer to all questions you might have. Remember how it accepted a variable (question, an IN parameter) and returned the answer? Let’s embark on an academic exercise, because the following example is syntactically correct but not suitable for real-life. So, if, hypothetically, for some reason, someone rewrote answer() as a procedure with an IN OUT parameter (definitely not recommended!), the end result might look like this:
create procedure answer (
p_question_and_answer in out varchar2
) as
begin
p_question_and_answer := 'the answer to '
|| DBMS_ASSERT.ENQUOTE_LITERAL (p_question_and_answer)
|| ' is 42';
end;
/
If you were to refer to that procedure using the FFI, you’d have to write the following:
/* a lot more JavaScript code */
const answer = plsffi.resolveProcedure('ANSWER');
const q_and_a = plsffi.argOf('life, the universe, and everything');
answer(q_and_a);
console.log(q_and_a.val);
As you can see, rather than using arg() for PL/SQL OUT variables, you use argOf() for IN OUT parameters. These tricks are necessary to introduce the concept of output variables to JavaScript and Typescript where these things don’t exist.
Hopefully you’ll find the FFI useful and it saves you some time.
Summary
Looking at the number of lines of code required, the version using the FFI beats the first example hands-down. You also need to worry less about the definition of named binds, and their types. An overall much more enjoyable developer experience, and there is to hope that it becomes as much second nature as the select without “from dual”.
There is of course a lot more to the FFI than what you read in this post, it also supports named parameters, details about which you can find in the JavaScript Developer’s Guide, chapter 6. You can also read about the API definition on GitHub.
Happy coding!
Martins Blog 