Calling SQL and PL/SQL from JavaScript

Oracle Database allows executing user-provided JavaScript code since version 21c by using the Multilingual Engine (MLE). This documentation describes the JavaScript module mle-js-plsql-ffi that ships with the database as of Oracle 23.7.

This module offers a simple, straight-forward way for interacting with PL/SQL code from within JavaScript as we will show with some examples.

Calling PL/SQL Package

The following example demonstrates how to resolve the PL/SQL package my_pkg within the schema my_schema and subsequently call functions and procedures on it as well as access package variables easily from JavaScript.

 const myPkg = resolvePackage('my_schema.my_pkg');
 
 // Call a procedure
 myPkg.my_proc(23, 42, 'Hello World!');
 
 // Call a function
 const myVal = myPkg.my_function('foo', 'bar');
 
 // Read a variable/constant
 const myVar = myPkg.my_attribute;
 
 // Write variable
 myPkg.my_variable = 2;

Calling PL/SQL Subprograms

The following example demonstrates how to resolve the top-level PL/SQL function my_func (returning NUMBER) within the current schema and subsequently call it once with default type conversion and once with explicit type conversion:

 // Resolve top-level function
 const exampleFunc = resolveFunction('my_func');

 // Execute the PL/SQL function and use default conversion to JavaScript number
 const numberResult = exampleFunc(42);

 // Execute the PL/SQL function and convert to JavaScript OracleNumber
 const oracleNumberResult = exampleFunc.overrideReturnType(oracledb.ORACLE_NUMBER)(42);

Similarly, one can resolve and call a top-level procedure like this:

const exampleProcedure = resolveProcedure('my_procedure');
exampleProcedure(42, 23);

Handling PL/SQL Subprograms with OUT binds

If a PL/SQL subprogram has OUT binds, those can be dealt with by using the arg function. Similarly, the argOf function can be used to handle IN OUT binds. Consider the existence of the following PL/SQL procedure my_procedure_inout:

CREATE OR REPLACE PROCEDURE my_procedure_inout (
    arg1 IN varchar2,
    arg2 IN varchar2,
    arg3 IN OUT varchar2,
    arg4 OUT varchar2
 )

Here's how arg and argOf can be used to construct the parameter values before calling it. Calls to PL/SQL subprograms always can either be done with positional arguments or with an object that captures the named arguments. Please note that positional and named arguments cannot be mixed in a function call.

// Resolve top-level procedure
const myProc = resolveProcedure('my_procedure_inout');
 
// Prepare (IN) OUT parameters
const myArg3 = argOf('baz');
const myArg4 = arg();

// Call procedure with positional arguments
myProc('foo', 'bar', myArg3, myArg4);

// Call procedure with named arguments
myProc({
    arg1: 'foo',
    arg2: 'bar',
    arg3: myArg3,
    arg4: myArg4
});

// Resulting Outbind values are retrieved from .val
console.log(myArg3.val, myArg4.val);