Creating an Inline MLE Call Specification

Inline MLE call specifications embed JavaScript code directly in the CREATE FUNCTION and CREATE PROCEDURE DDLs.

If you want to quickly implement simple functionality using JavaScript, inline MLE call specifications can be a good choice. With this option, you don't need to deploy a separate module containing the JavaScript code. Rather, the JavaScript function is built into the definition of the call specification itself.

The MLE LANGUAGE clause is used to specify that the function is implemented using JavaScript. The JavaScript function body must be enclosed by a set of delimiters. Double curly braces are commonly used for this purpose, however, you also have the option to choose your own. The beginning and ending delimiter must match and they cannot be reserved words or a dot. For delimiters such as {{...}}, <<...>>, and ((...)), the ending delimiter is the corresponding closing symbol, not an exact match.

The string following the language name is treated as the body of a JavaScript function that implements the functionality of the call specification. When the code is executed, PL/SQL parameters are automatically converted to the default JavaScript type and passed to the JavaScript function as parameters of the same name. Note that unquoted parameter names are mapped to all-uppercase JavaScript names. The value returned by a JavaScript function is converted to the return type of the PL/SQL call specification, just as with call specifications for MLE modules.

The syntax of the function is checked at compile time. If the JavaScript code includes syntax errors, the function or procedure will be created but it will exist in an invalid state. While syntax errors will be caught before runtime, it is still recommended to use a linting tool of your choice to perform analysis of your code before executing the inline call specification.

See Also:

Oracle AI Database PL/SQL Packages and Types Reference for information about DBMS_MLE subprograms for inline call specifications

Example 6-2 Simple Inline MLE Call Specification

CREATE OR REPLACE FUNCTION date_to_epoch (
  "theDate" TIMESTAMP WITH TIME ZONE
)
RETURN NUMBER
AS MLE LANGUAGE JAVASCRIPT
{{
  const d = new Date(theDate);

  //check if the input parameter turns out to be an invalid date
  if (isNaN(d)){
    throw new Error(`${theDate} is not a valid date`);
  }

  //Date.prototype.getTime() returns the number of milliseconds
  //for a given date since epoch, which is defined as midnight
  //on January 1, 1970, UTC
  return d.getTime();
}};
/

You can call the function created in the preceding inline call specification using the following SQL statement:

SELECT
  date_to_epoch(
    TO_TIMESTAMP_TZ(
      '29.02.2024 11.34.22 -05:00',
      'dd.mm.yyyy hh24:mi:ss tzh:tzm'
    )
  ) epoch_date;

Result:

EPOCH_DATE
----------
1.7092E+12

Example 6-3 Inline MLE Call Specification Returning JSON

Note the use of double quotation marks in the function parameter name, strArgs, in Example 6-2. The inclusion preserves the parameter's letter case. Without quotation marks, the parameter name is mapped to an all-uppercase JavaScript name, as seen in this example.

CREATE OR REPLACE FUNCTION p_string_to_json(inputString VARCHAR2) RETURN JSON
AS MLE LANGUAGE JAVASCRIPT 
{{
  if ( INPUTSTRING === undefined ) {
    throw `must provide a string in the form of key1=value1;...;keyN=valueN`;
  }

  let myObject = {};
  if ( INPUTSTRING.length === 0 ) {
    return myObject;
  }

  const kvPairs = INPUTSTRING.split(";");
  kvPairs.forEach( pair => {
    const tuple = pair.split("=");
    if ( tuple.length === 1 ) {
      tuple[1] = false;
    } else if ( tuple.length != 2 ) {
      throw "parse error: you need to use exactly one '=' between key and value and not use '=' in either key or value";
    }
    myObject[tuple[0]] = tuple[1];
  });

  return myObject;
}};
/

The function created in the preceding inline call specification can be called using the following SQL statement:

SELECT p_string_to_json('Hello=Greeting');

Result:

P_STRING_TO_JSON('HELLO=GREETING')
------------------------------------------------------------
{"Hello":"Greeting"}

Components of an Inline MLE Call Specification
The elements of an inline MLE call specification are listed along with descriptions.
Accessing Built-in Modules Using JavaScript Global Variables
Rather than importing MLE built-in modules in the same way as call specifications for MLE modules, inline MLE call specifications utilize prepopulated JavaScript globals to access built-in module functionality.

Parent topic: Call Specifications for Functions

Components of an Inline MLE Call Specification

The elements of an inline MLE call specification are listed along with descriptions.

Figure 6-5 MLE Inline Call Specification Syntax

Description of "Figure 6-5 MLE Inline Call Specification Syntax"

Table 6-2 Components of an Inline MLE Call Specification

Element Name	Description
`OR REPLACE`	Specifies that the function should be replaced if it already exists. This clause can be used to change the definition of an existing function without dropping, recreating, and re-granting object privileges previously granted on the function. Users who had previously been granted privileges on a recreated function or procedure can still access the function without being re-granted the privileges.
`IF NOT EXISTS`	Specifies that the function should be created if it does not already exist. If a function by the same name does exist, the statement is ignored without error and the original function body remains unchanged. Note that SQL*Plus will display the same output message regardless of whether the command is ignored or executed, ensuring that your DDL scripts remain idempotent. `IF NOT EXISTS` cannot be used in combination with `OR REPLACE`.
`schema`	Specifies the schema that will contain the call specification. If the schema is omitted, the call specification is created in the schema of the current user.
`call_spec_name`	Specifies the name of the call specification to be created. Call specifications are created in the default namespace, unlike MLE modules and environments, which use dedicated namespaces.
`param_declaration`	Specifies the call specification's parameters. If no parameters are specified, parentheses must be omitted.
`RETURN datatype`	Only used for functions and specifies the data type of the return value of the function. The return value can have any data type supported by MLE. Only the data type is specified; length, precision, or scale information must be omitted.
`invoker_rights_clause`	Specifies whether a function is invoker's or definer's rights. `AUTHID CURRENT_USER` creates an invoker's rights function or procedure. `AUTHID DEFINER` creates a definer's rights function or procedure. If the `AUTHID` clause is omitted, the call specification is created with definer's rights by default. The `AUTHID` clause on MLE call specifications has the exact same semantics as on PL/SQL functions and procedures.
`deterministic_clause`	Only used for functions and indicates that the function returns the same result value whenever it is called with the same values for its parameters. As with PL/SQL functions, this clause should not be used for functions that access the database in any way that might affect the return result of the function. The results of doing so will not be captured if the database chooses not to re-execute the function.
`MLE LANGUAGE`	Specifies the language of the following code, for example, JavaScript. The string following the language name is interpreted as MLE language code implementing the desired functionality. For JavaScript, this embedded code is interpreted as the body of a JavaScript function.
`PURE`	The `PURE` keyword specifies that the function or procedure should be created in a restricted execution context. During PURE execution, access to database state is disallowed, providing an additional layer of security for user-defined functions that do not require access to database state. For more information, see About Restricted Execution Contexts.

Parent topic: Creating an Inline MLE Call Specification

Accessing Built-in Modules Using JavaScript Global Variables

Rather than importing MLE built-in modules in the same way as call specifications for MLE modules, inline MLE call specifications utilize prepopulated JavaScript globals to access built-in module functionality.

Inline MLE call specifications cannot import MLE modules, both built-in and custom. Instead, JavaScript global variables, such as the session variable, provide access to the functionality of built-in modules like the JavaScript MLE SQL driver. For more information about the availability of objects in the global scope, see Working with the MLE JavaScript Driver.