Skip to content

Deploying JavaScript using the CREATE SOURCE Clause

Before a JavaScript function can be used as a stored procedure or user defined functions (UDF for short), two things need to done:

  1. the JavaScript source code must be deployed to the database in the form of a UMD module and
  2. the PL/SQL call specifications for all functions exported by the deployed JavaScript module must be created.

There are different ways to create a JavaScript module and its call specifications inside the database. In the following, we describe the CREATE SOURCE statement that comes with this preview release. It allows deploying JavaScript modules or any other MLE-supported language source into the database.

To create or replace a schema object containing the JavaScript/MLE-language source in your own schema, you must have the CREATE PROCEDURE system privilege. To create or replace such a schema object in another user's schema, you must have CREATE ANY PROCEDURE system privilege.

CREATE SOURCE Clause

There are basically two ways to deploy a source file via the CREATE SOURCE statement:

  1. Provide the source code directly with the statement using the AS clause
  2. Store the source code into a LOB and use this LOB in the statement

We will now discuss the two options in more detail.

Source via AS clause

As shown in the getting started guide, the source code can be provided with the statement directly.

Consider the following statement:

CREATE OR REPLACE JAVASCRIPT SOURCE NAMED scott."math.js" AS
  module.exports.mul = function(a, b) {
    return a * b; }
  module.exports.nothing = function(a) {
  }
  module.exports.zero = function() {
    return 0; }
  module.exports.mul = function(a, b) {
    return a * b; }
/

The statement creates a Javascript module math.js in the schema of the user scott.

The CREATE clause is followed by OR REPLACE, which re-creates the schema object containing the JavaScript/MLE-language source if it already exists. In this case, the definition of an existing object is changed without dropping, re-creating, and regranting object privileges previously granted.

In the example, we provide scott as name of the schema object in which the JavaScript/MLE-language module will be held. If no schema is provided, then the object is created in the schema of the current user. There are a few rules for the schema name, for example, the length of the source name <name> must not exceed 128 bytes. Also the schema and module name are automatically converted to uppercase if not enclosed with double quotation marks.

The source text that follows the AS clause must not be encapsulated in quotes and is delimited by the end of the command only. Note that the end-of-line delimiter / is only needed in SQL*Plus. If the statement is executed via node, for example, it is not required.

Load source from a LOB

The second way to deploy a source file is to store the source within a LOB (i.e., BLOB, CLOB) and select the LOB in the CREATE SOURCE statement.

In the following example, we want to deploy the source of the file math.js, which looks as follows:

module.exports.nothing = function(a) {
  // do nothing
}
module.exports.zero = function() {
  return 0;
}
module.exports.mul = function(a, b) {
  return a * b;
}

We first need to store the source into a LOB column of a table. For that, we create a table my_source, which has a column id and a column text holding the CLOBs, as follows:

CREATE TABLE my_source (id NUMBER, text CLOB);

Now, we store the source in the text column of the table my_source. We can use SQL*Loader to do that.

The following file sourceloader.ctl can be used to control a SQL*Loader session, which loads the file math.js into our table my_source:

LOAD DATA
INFILE *
  INTO TABLE my_source
  FIELDS terminated by ','
   ( 
     id CHAR(1),
     lob_file FILLER char,
     text LOBFILE(lob_file) TERMINATED BY EOF
   )
BEGINDATA
1,math.js

The file instructs SQLLOADER to create one row in the table my_source where id set to 1 and text holds the content of the file math.js*.

We now run SQL*Loader as follows:

sqlldr scott/tiger@ORCLPDB1 control=sourceloader.ctl

Now the source from math.js is loaded into our table my_source. The following statement deploys the source from the CLOB column:

CREATE OR REPLACE JAVASCRIPT SOURCE NAMED "math.js"
USING CLOB SELECT text FROM my_source WHERE id = 1;

Note that the selection clause WHERE id = 1 is actually not needed in this example since the table has only a single row. However, it must be specified when the table that holds the CLOB has more than one row.

We can similarly deploy sources that are in BLOB or BFILE columns.

CREATE Call Specification

Once the source code is deployed in the database via the AS clause or from a CLOB, we need to create call specifications for all functions that we want to make available. We create such specifications via CREATE FUNCTION and CREATE PROCEDURE statements.

We first create a call specification for the procedure nothing as follows:

CREATE OR REPLACE PROCEDURE scott.nothing(a IN varchar2) 
AS LANGUAGE JAVASCRIPT 
NAME 'math\.js.nothing(a string)';
/

We use OR REPLACE after CREATE to allow re-creation of the procedure if it already exists. This also allows users who had previously been granted privileges on a redefined function or procedure to access the function without being re-granted the privileges.

We specify that the schema scott is used. This could also be omitted, which means that the procedure is created in the schema of the current user. In case we want to create the function in a different schema, we have to fully qualify the name of the procedure, i.e., specifiy the schema name in front of the procedure name.

The data type of the one parameter a of the procedure is varchar2. Since a procedure has no return value, we do not need to specify a return type for it. Parameters as well as the return value can have any PL/SQL data type that is currently supported by MLE (cf. Database Types).

Lastly, we provide the function's JavaScript signature as part of the NAME clause. This includes the name of the source, the function name in the source, and names and types of the parameters.

We similarly create the call specification for the function zero as follows:

CREATE OR REPLACE FUNCTION zero RETURN NUMBER
AS LANGUAGE JAVASCRIPT 
NAME 'math\.js.zero() return number';
/

Note that there are no parentheses after the name of the function. Furthermore, we need to escape dots in the module name because there is only one dot allowed that separates the module name from the JavaScript function name.

Finally, the call specification of the function mul looks as follows:

CREATE OR REPLACE FUNCTION mul(a IN NUMBER, b IN NUMBER) RETURN NUMBER
AS LANGUAGE JAVASCRIPT 
NAME 'math\.js.mul(a number, b number) return number';
/

Executing the deployed functions

We can now execute each of the three deployed functions in SQL*Plus.

sqlplus scott/tiger@ORCLPDB1

Running the procedure nothing:

call nothing('none');

Returns:

Call completed.

Running the function zero:

select zero() from dual;

Returns:

    ZERO()
----------
     0

Finally, running the function mul via:

select mul(4,5) from dual;

Returns:

  MUL(4,5)
----------
    20