OUT and IN OUT Parameters

Use OUT and IN OUT parameters with MLE JavaScript functions.

MLE JavaScript functions support IN OUT and OUT parameters in addition to IN parameters, just as they are supported in PL/SQL functions and procedures. These are declared as IN OUT and OUT in the list of arguments of an MLE call specification.

Because JavaScript has no notion of output parameters, the JavaScript implementation instead accepts objects that wrap the parameter value. Concretely, the shape of these wrapper objects is described by the following generic TypeScript interfaces InOut and Out (for IN OUT and OUT parameters, respectively):

Interface InOut<T> {
    Value : T;
}

Interface Out<T> {
    Value : T;
}

Note that OUT and IN OUT parameters are passed to JavaScript functions as JavaScript objects whose only property, value, exposes the value of the argument. This means that, in order to read, write, and use the value of an OUT or IN OUT argument, it must first be unwrapped by accessing its value property. This is done in order to simulate a pass-by-reference implementation, which does not exist in JavaScript. For example, the substitute() function in Example 6-6 must first unwrap its IN OUT argument, sentence, by retrieving its value property before calling match() on it. Attempting to call match() on sentence directly would fail, as sentence is only the value wrapper. These wrapper classes are never needed in DBMS_MLE, which does not make use of OUT and IN OUT parameters.

Example 6-6 OUT and IN OUT Parameters with JavaScript

Consider an MLE function, substitute(), that takes a VARCHAR2 IN OUT parameter, sentence, and replaces all occurrences of the second parameter, replaceThis, with the third parameter, withThat, then returns the number of occurrences of replaceThis in sentence.

CREATE OR REPLACE MLE MODULE in_out_example_mod 
LANGUAGE JAVASCRIPT AS 

export function substitute (sentence, replaceThis, withThat) {
    /*
     *  substitute: substitutes `replaceThis` in `sentence` with
     *              `replaceThat`
     *
     * parameters: 
     * - sentence: the input sentence
     * - replaceThis: a word to be replaced in `sentence`
     * - withThat: the new word to be used instead of `replaceThis`
     */
    const occurrences = 
        (sentence.value.match(replaceThis) || []).length;
    sentence.value = sentence.value.replace(replaceThis, withThat);
    return occurrences;
}
/

CREATE OR REPLACE FUNCTION f_substitute(
    p_sentence       IN OUT VARCHAR2,
    p_replaceThis    IN VARCHAR2,
    p_withThat       IN VARCHAR2
)
RETURN NUMBER
AS MLE MODULE in_out_example_mod
SIGNATURE 'substitute(InOut<string>, string, string)';
/

The SIGNATURE clause of the call specification lists the parameter type of the JavaScript function's sentence parameter as InOut<string>. The input VARCHAR2 value is therefore converted to a JavaScript string, that is then wrapped in an object and passed to the JavaScript function substitute().

EXEC dbms_session.reset_package
SET SERVEROUTPUT ON

DECLARE
  l_sentence    varchar2(100) := 'people are enjoying the rain';
  l_replaceThis varchar2(100) := 'rain';
  l_withThat    varchar2(100) := 'sun';
  l_occurrences pls_integer;
BEGIN
  dbms_output.put_line('sentence before: ' || l_sentence);
  l_occurrences := f_substitute(
    l_sentence, l_replaceThis, l_withThat);
  if l_occurrences <> 0 then
    dbms_output.put_line('sentence after: ' || l_sentence);
  else
    dbms_output.put_line('no text replacement performed');
  end if;
END;
/

Result:

sentence before: people are enjoying the rain
sentence after: people are enjoying the sun

Parent topic: MLE JavaScript Functions