Skip to content

JavaScript Stored Procedures

Introduction

A stored procedure assigns a name to a parameterizable sequence of interactions with the database. It stores these interactions in the database such that a user can use a CALL SQL statement to execute complex business logic. The stored procedure is then executed once with input provided at invocation time.

Stored procedures are primarily used to implement business logic. Typically, stored procedures use callouts to interact with the database, e.g., querying data, updating or creating tables, or deleting data.

Oracle Database MLE enables us to use regular JavaScript or TypeScript functions as stored procedures. We demonstrate how to develop and deploy stored procedures in JavaScript and TypeScript based on simple examples.

A Simple Example

Any JavaScript or TypeScript function that (a) does not return a value and (b) expects only arguments of types supported by Oracle Database MLE (see the conversion documentation) can be used as a stored procedure. Here, we start with a very simple example that demonstrates the basics of defining stored procedures: we develop a stored procedure in JavaScript that prints a greeting, a JavaScript stack trace, and timing information when it is executed in the database.

Real-world stored procedures should be able to interact with the database by issuing queries, INSERT and UPDATE operations and other SQL statements. The documentation on callouts describes how a JavaScript stored procedure can execute SQL statements via the SQL driver. At the end of this document you will find a more complete example of a JavaScript stored procedure that interacts with the database.

We will reuse a database server buffer provided by DBMS_OUTPUT to print our messages. To use this, we can simply use the database drop-in replacement of the node.js console.

The console replacement module provides us with a couple of [functions]((https://nodejs.org/docs/latest-v7.x/api/console.html) which are implemented on top of the database. For us, the interesting functions are:

  • info to log to the server buffer,
  • time to start a named timing,
  • timeEnd to end a named timing and print the duration into the server buffer, and

You can freely choose the name of the timing component. It will be used as a prefix to the output produced by timeEnd.

We use the name 'stored procedure test' for our timing of the main part of our stored procedure and create a file simplesp.js with the following content:

function sayHello() {
  var name = 'stored procedure test';
  console.time(name);
  console.info('Hello from Oracle MLE');
  console.timeEnd(name);
}

The stored procedure will print Hello from Oracle MLE and the time it took to execute this stored procedure into the server buffer.

We finish the file by appending the code to export our function as a CommonJS module which can then be loaded by Oracle Database MLE:

module.exports.hello = sayHello;

This line exports the function sayHello under the name hello by assigning it to property hello of the module.exports object. In general, a module may export multiple stored procedures or UDFs by assigning them to properties of the module.exports object.

Deploying the Example

As before, we need to create a source object via CREATE SOURCE and create a call specification via CREATE PROCEDURE.

In User-Defined Functions we show how everything can be done in JavaScript from any client. Here, we will show an alternative way. We use SQL*Loader to upload the file simplesp.js into the CLOB column of a table that is created as follows:

CREATE TABLE my_source (id NUMBER, text CLOB);

We use a SQL*Loader control file sourceloader_simplesp.ctl to load our source file into the table my_source:

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

We can now run SQL*Loader as follows:

sqlldr scott/tiger@ORCLPDB1 control=sourceloader_simplesp.ctl

We now can deploy the source via CREATE SOURCE. So we have to connect via SQL*Plus to the database:

$ sqlplus scott/tiger@ORCLPDB1

First, we need to decide which schema should own our procedure. We will use the scott schema in this example:

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

Next, we need to create the call specification for the procedure.

We can do this in SQL*Plus as follows:

CREATE OR REPLACE PROCEDURE sayHello AS LANGUAGE JAVASCRIPT 
NAME 'simplesp\.js.hello()';
/

We export in the source code a function hello which does not take any parameters, and does not return any results. Therefore, the call specification has no return type and we need to use CREATE PROCEDURE instead of CREATE FUNCTION.

The JavaScript procedure is exported using the name hello (module.exports.hello = sayHello); the PL/SQL callspec above uses the name sayHello.

Executing the Stored Procedure

To execute our stored procedure, we first connect to the database. Since we deployed the procedure into the scott schema, again we use SQL*Plus to connect to the database with this schema:

$ sqlplus scott/tiger@ORCLPDB1

SQL*Plus will then show us a command prompt which we can use to issue further commands. To execute our stored procedure we first have to activate the server output. This is needed since our stored procedure uses a buffer provided by DBMS_OUTPUT and we actually want to see the output in SQL*Plus.

SQL> set serveroutput on

With the server output activated, we simply call the function and - upon completion - we will get the server output:

SQL> call sayHello();
Hello from Oracle Database MLE
stored procedure test: 8ms

Call completed.

We get the output only after the call has terminated. This is a restriction we inherit from the DBMS_OUTPUT.

A Bigger Example

Stored procedures usually use callouts to interact with the database. We now provide an example where we use callouts to raise the salary of an employee. We implement a simple business logic which forbids non-managers from having a salary bigger than 10000 Dollar.

We write this function into a file salraise.js. We will structure the business logic used in the stored procedure in a set of small functions. Only the stored procedure will be exposed to the database. The helper functions used to structure our business logic cannot be called from the database directly. We start with requiring the driver for callouts:

var sql = _dbRequire("@oracle/sql");

Next we define a helper function that updates the salary of an employee and returns the new value. We identify an employee by their (unique) employee number. Please see the documentation on callouts for an explanation of the API of the callout driver.

function updateGetSal(empno, raise) {
    if (sql.execute("UPDATE emp SET sal = sal + :1 WHERE empno = :2",
                    [raise, empno]).rowsAffected != 0) {
        for (let row of sql.execute("SELECT sal FROM emp WHERE empno = :1",
                                    [empno]).rows) {
            return row[0];
        }
    }
    return false;
}

Next, we need a function to check if an employee (identified by an employee number) is a manager. The following code snippet uses a callout to see if there is an employee whose manager has the given employee number:

function isManager(empno) {
    for (let row of sql.execute(
        "SELECT count(*) FROM emp WHERE mgr = :1", [empno]).rows) {
        return row[0] > 0;
    }
    return false;
}

We can now implement our business logic using these two helper functions and export the resulting stored procedure to the database. In this example, we unify the definition of our stored procedure and the assignment to module.exports to export it in one single assignment:

module.exports.salraise = function (empno, raise) {
    var newsal = updateGetSal(empno, raise);
    if (newsal && newsal > 10000 && !isManager(empno)) {
        sql.execute("ROLLBACK");
    } else {
        sql.execute("COMMIT");
    }
};

The stored procedure takes two arguments, both of the type number: the employee number of the employee, and the raise amount.

Then, we deploy the script into the scott schema. As before, we load the source code from the file salraise.js into the CLOB column of our table my_source, deploy the source code via CREATE SOURCE, and create the call specification via an CREATE PROCEDURE statement.

We load the source code into the previously created table my_source via SQL*Loader. We use the updated SQL*Loader control file sourceloader_salraise.ctl:

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

We then trigger SQL*Loader as follows:

sqlldr scott/tiger@ORCLPDB1 control=sourceloader_salraise.ctl

We now deploy the source code into the database in SQL*Plus as follows:

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

Next, we need to create the call specification for the stored procedure salraise.

We can do this in SQL*Plus as follows:

CREATE OR REPLACE PROCEDURE salraise(empno IN number, raise IN number) AS LANGUAGE JAVASCRIPT 
NAME 'salraise\.js.salraise(empno number, raise number)';
/

Now we can use the stored procedure SALRAISE to increase the salary of an employee:

$ sqlplus scott/tiger@ORCLPDB1
SQL> select count(*) from emp where mgr = 7369;

  COUNT(*)
----------
     0

SQL> select sal from emp where empno = 7369;

       SAL
----------
       800

SQL> call salraise(7369, 200);

Call completed.

SQL> select sal from emp where empno = 7369;

       SAL
----------
      1000

SQL> call salraise(7369, 10000);

Call completed.

SQL> select sal from emp where empno = 7369;

       SAL
----------
      1000

SQL> select count(*) from emp where mgr = 7839;

  COUNT(*)
----------
     3

SQL> select sal from emp where empno = 7839;

       SAL
----------
      5000

SQL> call salraise(7839, 10000);

Call completed.

SQL> select sal from emp where empno = 7839;

       SAL
----------
     15000

We can restore the values by running

$ sqlplus scott/tiger@ORCLPDB1
SQL> update emp set sal = 800 where empno = 7369;
SQL> update emp set sal = 5000 where empno = 7839;

Exercise: Improvement

The function updateGetSal can be improved using RETURNING ... INTO ... (see the update command documentation for details) and OUT bind variables (see the callout documentation). This is left as an exercise to the user.

OUT Parameters

Database stored procedures may take IN parameters and return OUT parameters. We have already seen how a JavaScript procedure can accept IN arguments when invoking the procedure using the CALL statement. OUT parameters can be used to have a procedure return one or more values. Here is how OUT parameters can be used with JavaScript procedures.

Call Specification for OUT Parameters

We need to redefine sayHello in the file simplesp.js and have it return an object with the said field.

function sayHello() {
  return {
      answer: 'Hello from Oracle Database MLE'
  };
}

module.exports.hello = sayHello;

The modified module can be deployed using the steps from above.

Note that the SQL*Loader control file sourceloader_simplesp.ctl uses the keyword REPLACE to replace the source code in the table my_source when SQL*Loader is executed again via:

sqlldr scott/tiger@ORCLPDB1 control=sourceloader_simplesp.ctl

We now can re-deploy the source again as follows:

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

Next, we need to update the call specification for the stored procedure sayHello.

We can do this in SQL*Plus as follows:

CREATE OR REPLACE PROCEDURE sayHello("answer" OUT varchar2) AS LANGUAGE JAVASCRIPT 
NAME 'simplesp\.js.hello(answer string)';
/

Executing the Stored Procedure

Right now, MLE caches the source code internally so that the old code of simplesp.js will be executed when the procedure sayHello is called. For this reason, we need to flush the internal code cache before calling the procedure.

Using SQL*Plus, we flush the internal cache via:

call dbms_session.reset_package();

Now the procedure can be called as follows:

SQL> variable answer varchar2(200);
SQL> call sayHello(:answer);

Call completed.

SQL> print :answer

ANSWER
--------------------------------------------------------------------------------
Hello from Oracle Database MLE

OUT parameters can also be used when calling the function through a driver such as Oracle NodeDB.

IN OUT Parameters

If an IN and OUT parameter share the same name, they become a so called IN OUT parameter.

Here is the updated code that uses an IN OUT parameter.

function sayHello(answer) {
  return {
      answer: 'Hello from Oracle Database MLE' + answer
  };
}

module.exports.hello = sayHello;

To deploy the updated code, we load the source into the table my_source again:

sqlldr scott/tiger@ORCLPDB1 control=sourceloader_simplesp.ctl

and re-deploy it in SQL*Plus as follows:

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

The call specification for the stored procedure sayHello now looks as follows.

We can do this as follows:

CREATE OR REPLACE PROCEDURE sayHello("answer" IN OUT varchar2) AS LANGUAGE JAVASCRIPT 
NAME 'simplesp\.js.hello(answer string)';
/

We flush the internal cache again:

call dbms_session.reset_package();

Using SQL*Plus, a function with IN OUT parameters can be invoked as follows.

SQL> variable answer VARCHAR2(200);
SQL> exec :answer := ' - Hello';
SQL> call sayHello(:answer);

Call completed.

SQL> print :answer;

ANSWER
--------------------------------------------------------------------------------
Hello from Oracle Database MLE - Hello