Creating a Call Specification for an MLE Module

MLE call specification creation uses the generic CREATE FUNCTION RETURNS AS or CREATE PROCEDURE AS syntax, followed by MLE specific syntax.

Example 6-1 Creating MLE Call Specifications

This example walks you through the creation of an MLE module that exports two functions, and the creation of call specifications to publish those functions.

CREATE OR REPLACE MLE MODULE jsmodule
LANGUAGE JAVASCRIPT AS

    export function greet(str){
        console.log(`Hello, ${str}`);
    }
    export function concat(str1, str2){
        return str1 + str2;
    }
/

The MLE module jsmodule exports two functions. The function greet() takes an input string argument and prints a simple greeting, while the function concat() takes two strings as input and returns the concatenated string as the result.

Because greet() does not return a value, you must create a PL/SQL procedure to publish it, as follows:

CREATE OR REPLACE PROCEDURE
    GREET(str in VARCHAR2)
    AS MLE MODULE jsmodule
    SIGNATURE 'greet(string)';
/

The above call specification creates a PL/SQL procedure named GREET() in the schema of the current user. Running the procedure executes the exported function greet() in the JavaScript module jsmodule.

Note that it is not a requirement that the call specification has the same name (GREET) as the function being published (greet).

The MLE specific clause MLE MODULE <module name> specifies the JavaScript MLE module that exports the JavaScript function to be called.

The SIGNATURE clause specifies the name of the exported function to be called (in this case, greet), as well as, optionally, the list of argument types in parentheses. JavaScript MLE functions use TypeScript types in the SIGNATURE clause. In this example, the function accepts a JavaScript string. The PL/SQL VARCHAR2 string is converted to a JavaScript string before invoking the underlying JavaScript implementation. The SIGNATURE clause also allows the list of argument types to be omitted, in which case only the MLE function name is expected and MLE language types are inferred from the types given in the call specification's argument list.

The other exported function, concat(), can similarly be used to create a PL/SQL function:

CREATE OR REPLACE FUNCTION CONCATENATE(str1 in VARCHAR2, str2 in VARCHAR2)
    RETURN VARCHAR2
    AS MLE MODULE jsmodule
    SIGNATURE 'concat(string, string)';
/

The call specification in this case additionally specifies the PL/SQL return type of the created function. The value returned by the JavaScript function concat() (of type string) is converted to the type VARCHAR2.

The created procedure and function can be called as shown below with the result:

SQL> CALL GREET('Peter');
Hello, Peter

Call completed.

SQL> SELECT CONCATENATE('Hello, ','World!');

CONCATENATE('HELLO','WORLD!')
-------------------------------------------------
Hello, World!

Topics

• Components of an MLE Call Specification
  The elements of an MLE call specification are listed along with descriptions.
• MLE Module Clause
  The MLE MODULE clause specifies the MLE module that exports the underlying JavaScript function for the call specification. The specified module must always be in the same schema as the call specification being created.
• ENV Clause
  The optional ENV clause specifies the MLE environment for module contexts in which this call specification will be executed.
• SIGNATURE Clause
  The SIGNATURE clause contains all the information necessary to map the MLE call specification to a particular function exported by the specified MLE module.

Components of an MLE Call Specification

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

Figure 6-1 MLE Call Specification Syntax

Description of "Figure 6-1 MLE Call Specification Syntax"

Table 6-1 Components of an 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 Module Clause

The MLE MODULE clause specifies the MLE module that exports the underlying JavaScript function for the call specification. The specified module must always be in the same schema as the call specification being created.

An ORA-04103 error is thrown if the specified MLE module does not exist. Likewise, an ORA-01031 error is raised if the specified module is in a different schema from the created call specification.

ENV Clause

The optional ENV clause specifies the MLE environment for module contexts in which this call specification will be executed.

An ORA-04105 error is thrown if the specified environment schema object does not exist.

If this clause is omitted, the default environment is used. The default environment is simply an environment in its most basic state, with no module imports and no specified language options.

SIGNATURE Clause

The SIGNATURE clause contains all the information necessary to map the MLE call specification to a particular function exported by the specified MLE module.

Specifically, it includes two pieces of information:

• The name of the exported function in the specified MLE module.
• The MLE language parameter types (as opposed to the PL/SQL parameter types) for the function (Optional).

The SIGNATURE clause must be in the following form:

Figure 6-2 signature_clause ::=

Description of "Figure 6-2 signature_clause ::="

Figure 6-3 path_spec ::=

Description of "Figure 6-3 path_spec ::="

Figure 6-4 import_spec ::=

Description of "Figure 6-4 import_spec ::="

The path specification describes the function to be called and can have the following two forms:

• A path specification can consist only of an import specification.

  • An import specification can be a JavaScript identifier that identifies a named export of the module, which must be a function. Alternatively, an import specification can be the reserved word, default. In this case, the default export of the module is used, which must be a function.

• A path specification can be a composite form consisting of an import specification, followed by a dot and a JavaScript identifier.

  • In this case, the import specification must refer to an object that has a property whose name matches the identifier listed after the dot. The value of the property needs to be a function.

The language-type can either be a built-in JavaScript type (e.g. string or number) or a type provided by MLE (e.g. OracleNumber or OracleDate) that is compatible with the corresponding PL/SQL argument. Note that JSON data maps to the MLE ANY type. For an example covering how to pass JSON from PL/SQL to MLE, see Working with JSON Data. For more information about what types are provided by MLE through the built-in module mle-js-plsqltypes, see Server-Side JavaScript API Documentation.

function-name can include any alphanumeric characters as well as underscores and periods.

When the call specification is a function, the type of the return value is not specified in the SIGNATURE clause. Rather, the function can return any JavaScript type that is compatible with the PL/SQL type specified in the call specification's RETURN clause.

Note:

The parsing and resolution of the SIGNATURE clause happens lazily when the MLE function is executed for the first time. It is only at this point that any resolution or syntax errors in the SIGNATURE clause are reported, and not when the call specification is created.

Simplified SIGNATURE Clause

CREATE FUNCTION and CREATE PROCEDURE DDL statements also accept a simplified form of the SIGNATURE clause that only specifies the name of the exported function and leaves out the JavaScript language types of the parameters. The default PL/SQL-MLE language type mappings are used in this case.

This example demonstrates the creation of a call specification with a simplified SIGNATURE clause.

CREATE OR REPLACE FUNCTION concat
    RETURN VARCHAR2
    AS MLE MODULE jsmodule
    SIGNATURE 'concat';
/

When the function concat is called from PL/SQL, the input VARCHAR2 parameters are converted to JavaScript string (the default type mapping for VARCHAR2) before calling the underlying JavaScript function.

See Also:

MLE Type Conversions for more information about type mappings