A set of functions is available with the mle-js-plsql-ffi API, each returning a JavaScript object that represents its database counterpart.

The following functions are available to resolve packages and top-level functions and procedures:

• resolvePackage('<pkg_name>')
• resolveProcedure('<proc_name>')
• resolveFunction('<func_name>')

If the object you want to resolve is in your own schema or has a public synonym, qualifying the object name with the owning schema is optional. If the object is in a different schema, you must have necessary permissions to access the object and must qualify its name with the owning schema. As with the MLE JavaScript SQL driver, all operations are performed in your own security context.

// call a procedure with case-sensitive name
myPkg['"MyProc"']();

// read a global variable with a case-sensitive name
console.log(myPkg['"MyVar"']);

Once a database object has been resolved, you can perform the following operations on the resulting object:

• Procedure: Execute
• Function: Execute
• Package:
  • Execute procedure
  • Execute function
  • Read and write public package variables
  • Read constants

With resolvePackage, variables, constants, procedures, and functions can be accessed directly through property reads of the resulting object. If the package does not have the member provided in the property read, a Reference error is thrown. When the accessed member is a PL/SQL function or procedure, the JavaScript object returns the same type of callable entity that is resolved for top level functions and procedures. Consider the following snippets for examples of the syntax:

// resolve a package
const myPkg = resolvePackage('my_package');

// call a procedure and function in the package
myPkg.my_proc();
let result = myPkg.my_func();

// read a global variable and constant in the package
console.log(myPkg.my_var);
console.log(myPkg.my_const);

// write a global variable in the package
myPkg.my_var = 42;

For package variables and constants, only non-named types are supported. The following types are not supported: PL/SQL record types, nested table types, associative arrays, vector types, and ADTs.

When resolving a procedure or function, you receive a callable object. With functions, the overrideReturnType instance method can optionally be used to specify the return type and change other metadata. Consider the following example that uses overrideReturnType to increase the maxSize attribute:

1. Start by creating a function that returns a string:

   CREATE OR REPLACE FUNCTION ret_string(
       MULTIPLIER NUMBER
   ) RETURN VARCHAR2 AS
   BEGIN
       return rpad('this string might be too long for the defaults ', MULTIPLIER, 'x');
   END;
   /

2. Create another function, ret_string_ffi, that uses FFI to resolve the function ret_string:

   CREATE OR REPLACE FUNCTION ret_string_ffi(
       MULTIPLIER NUMBER
   ) RETURN VARCHAR2
   AS MLE LANGUAGE JAVASCRIPT
   {{
       const retStrFunc = plsffi.resolveFunction('ret_string');
       return retStrFunc(MULTIPLIER);
   }};
   /

3. The ret_string_ffi function will work as long as the multiplier value is small enough, as in the following:

   SELECT ret_string_ffi(50);

   Result:

   RET_STRING_FFI(50)
   --------------------------------------------------------------------------------
   this string might be too long for the defaults xxx

4. With a larger multiplier value, the result can exceed the default buffer length of 200 bytes and raise an error:

   SELECT ret_string_ffi(900);

   Result:

   SELECT ret_string_ffi(900)
                            *
   ERROR at line 1:
   ORA-04161: Error: Exception during subprogram execution (6502): ORA-06502:
   PL/SQL: value or conversion error: character string buffer too small
   ORA-04171: at :=> (<inline-src-js>:3:12)

5. You can solve this problem by using the overrideReturnType instance method to increase the maxSize attribute of the returned message:

   CREATE OR REPLACE FUNCTION ret_str_ffi_override(
       MULTIPLIER NUMBER
   ) RETURN VARCHAR2
   AS MLE LANGUAGE JAVASCRIPT
   {{
       const retStrFunc = plsffi.resolveFunction('ret_string');
       
       // overrideReturnType accepts either an oracledb type constant
       // such as oracledb.NUMBER, or a string containing the name of a 
       // user defined database type. If more information is needed, as
       // in this example, a parameter of type ReturnInfo can be provided
       retStrFunc.overrideReturnType({
           maxSize: 1000
       });
       return retStrFunc(MULTIPLIER);
   }};
   /

6. Using the new ret_str_ffi_override function, a call with a larger multiplier will now work:

   SELECT ret_str_ffi_override(900);

Use the arg and argOf functions to handle IN OUT and OUT parameters with the Foreign Function Interface (FFI).

JavaScript and PL/SQL handle parameters differently. For instance, JavaScript doesn't allow for named parameters in the same way that PL/SQL does. Neither does JavaScript have an equivalent for OUT and IN OUT parameters, nor is there an option for overloading functions. Last, but not least, JavaScript types are different from the database's built-in type system. To be able to call PL/SQL from JavaScript, the FFI must accommodate these differences.

For more information about PL/SQL subprogram parameters, see Oracle Database PL/SQL Language Reference.

CREATE OR REPLACE PROCEDURE my_proc_w_args(
    p_arg1      IN NUMBER,
    p_arg2      IN NUMBER,
    p_arg3      IN OUT JSON,
    p_arg4      OUT TIMESTAMP,
    p_arg5      OUT VARCHAR2
) AS
BEGIN
  
  SELECT
    JSON_TRANSFORM(p_arg3,
      SET '$.lastUpdate' = systimestamp,
      SET '$.value' = p_arg1 + p_arg2
    )
    into p_arg3;
    
  p_arg4 := systimestamp;

  -- the length of the string will exceed the default
  -- length of 200 characters for the out bind, mandating
  -- the use of maxSize in args().
  p_arg5 := rpad('x', 255, 'x');
    
END;
/

Parameters passed using the IN mode do not require any special treatment. The FFI provides the arg() and argOf() functions to handle OUT and IN OUT parameters, respectively. Remember that all parameters provided using the FFI are essentially bind parameters and thus their behavior can be influenced using the same dir, val, type, and maxSize properties you use if you call PL/SQL directly using session.execute().

The arg function generates an object that represents an argument. It optionally accepts the same object as the MLE JavaScript SQL driver, including any combination of the dir, val, type, and maxSize properties.

The argOf function generates an object that represents an argument of the given value.

Based on the function created in the preceding example, my_proc_w_args, you can invoke the function with the FFI using positional arguments as follows:

CREATE OR REPLACE PROCEDURE my_proc_w_args_positional(
    "arg1" NUMBER,
    "arg2" NUMBER
) AS MLE LANGUAGE JAVASCRIPT
{{
    const myProc = plsffi.resolveProcedure('my_proc_w_args');

    // arg3 is an IN OUT parameter of type JSON. my_proc_with_args
    // will modify it in place and return it to the caller
    const arg3 = plsffi.argOf({id: 10, value: 100});

    // arg4 is a pure OUT parameter
    const arg4 = plsffi.arg();

    // arg5 represents an OUT parameter as well but due to the
    // length of the return string, it must be provided with additional
    // metadata
    const arg5 = plsffi.arg({
        maxSize: 1024
    });

    myProc(arg1, arg2, arg3, arg4, arg5);

    console.log(`the updated JSON looks like this: ${JSON.stringify(arg3.val)}`);
    console.log(`the calculation happened at ${arg4.val}`);
    console.log(`the length of the string returned is ${arg5.val.length} characters`);
}};
/

The second option is to use named arguments, provided as a single, plain JavaScript object. The FFI API then maps each property to the argument that matches the name of the property.

CREATE OR REPLACE PROCEDURE my_proc_w_args_named(
    "arg1" NUMBER,
    "arg2" NUMBER
) AS MLE LANGUAGE JAVASCRIPT
{{
    const myProc = plsffi.resolveProcedure('my_proc_w_args');

    // arg3 is an IN OUT parameter of type JSON. my_proc_with_args
    // will modify it in place and return it to the caller
    const arg3 = plsffi.argOf({id: 10, value: 100});

    // arg4 is a pure OUT parameter
    const arg4 = plsffi.arg();

    // arg5 represents an OUT parameter as well but due to the
    // length of the return string must be provided with additional
    // metadata
    const arg5 = plsffi.arg({
        maxSize: 1024
    });

    myProc({
        p_arg1: arg1,
        p_arg2: arg2,
        p_arg3: arg3,
        p_arg4: arg4,
        p_arg5: arg5
    });

    console.log(`the updated JSON looks like this: ${JSON.stringify(arg3.val)}`);
    console.log(`the calculation happened at ${arg4.val}`);
    console.log(`the length of the string returned is ${arg5.val.length} characters`);
}};
/

Note the edge case where you have a PL/SQL subprogram that has a single argument that is represented in JavaScript as an object. Intuitively, you may want to pass it as a single positional argument, however, in that case, the FFI will interpret it as a named arguments object.

Consider the following example that demonstrates these options:

-- PL/SQL subprogram we want to call
CREATE OR REPLACE PROCEDURE my_proc(my_arg JSON) AS
BEGIN
    -- Process my_arg
END;

-- JavaScript function that calls my_proc
CREATE OR REPLACE PROCEDURE my_javascript_proc
AS MLE LANGUAGE JAVASCRIPT
{{
    const myProc = plsffi.resolveProcedure('my_proc');
    const myArg = { prop1: 10, prop2: 'foo' };

    // Catch the exception that will happen if the FFI tries
    // to interpret this as a call with named arguments
    try {
        myProc(myArg);
    } catch (err) {
        console.log(`if uncaught, this would have been a ${err}`);
    }

    // Option 1: Make it into a real named argument call.
    myProc({ my_arg: myArg });

    // Option 2: Wrap with argOf() to let the FFI know that it's a 
    // positional argument list call.
    myProc(plsffi.argOf(myArg));
}};

PL/SQL allows developers to overload signatures of functions and procedures that are defined in PL/SQL packages. The FFI does not perform overload selection, however, it still needs to decide what PL/SQL type to use for binding each argument. Unfortunately, it cannot make this decision on its own in all cases. In particular, in the following instances:

• No JavaScript value was given for an argument that is needed to determine the correct signature to call. Without a value, the FFI has no way of knowing the set of matching PL/SQL types.
• When one JavaScript type is viable for multiple PL/SQL types.

If you are specifying the type of your argument in order to help with type resolution, it is best to use one of the DB_TYPE_* constants.

Consider the following PL/SQL package:

CREATE OR REPLACE package overload_pkg AS

    FUNCTION my_func(
        p_arg1 IN BINARY_FLOAT
    ) RETURN VARCHAR2;
    
    FUNCTION my_func(
        p_arg1 IN INTEGER
    ) RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY overload_pkg AS
    
    FUNCTION my_func(
        p_arg1 IN BINARY_FLOAT
    ) RETURN VARCHAR2 AS
    BEGIN
        RETURN 'binary_float';
    END;
    
    FUNCTION my_func(
        p_arg1 IN INTEGER
    ) RETURN VARCHAR2 AS
    BEGIN
        RETURN 'integer';
    END;
END;
/

As you can see, my_proc is overloaded, accepting both a BINARY_FLOAT as well as an INTEGER. In JavaScript, both of these types are represented as the number data type and as such, multiple possible overloads are valid. If the FFI API cannot select the correct resolution, it is possible to force a particular overloaded PL/SQL function by providing the PL/SQL type.

CREATE OR REPLACE PROCEDURE force_overload
AS MLE LANGUAGE JAVASCRIPT
{{
    const myPkg = plsffi.resolvePackage('overload_pkg');

    let result = 'not yet called';

    // Catch error ORA-04161: Error: Exception during subprogram execution 
    // (4161): Multiple subprograms match the provided signature
    try {
        result = myPkg.my_func(42);
    } catch (err) {
        console.log(`if uncaught, this would have been a ${err}`);
    }

    // Solution: use argOf to make this work
    result = myPkg.my_func(plsffi.argOf(42, {type: oracledb.DB_TYPE_BINARY_FLOAT}))
    console.log(`and the result is: ${result}`);
}};
/

An error can also occur if the type is user-defined. For example, all JavaScript objects are considered viable for all PL/SQL records. In this case, it is enough to provide the name of the desired type.