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.

JavaScript Code

We will reuse a database server buffer provided by DBMS_OUTPUT to print our messages. To use this, we have to require the in database drop-in replacement of the node.js console. We don't need to install anything via npm for the console module since the replacement is built into our deployment tool. We create a file simplesp.js with this content:

var console = require('console');

The console replacement module provides us with a couple of functions 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
  • trace to print a stack trace.

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. We append our function to the end of simplesp.js:

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

The stored procedure will print Hello from Oracle Database MLE followed by a stack trace (produced by console.trace()), 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.

Call Specification using TypeScript

With the JavaScript sources in place we only need a declaration of the call specifications that should be created during deployment. To do this, we write a TypeScript declaration file simplesp.d.ts with these lines:

export function hello();

We export a function hello which does not take any parameters, and does not return any results. Since we do not specify the return type, dbjs will create a stored procedure instead of a UDF.

Note that the name of the function in our Typescript declaration file has to match the name of the exported JavaScript function. In our case, we exported the function sayHello under the name hello. So we have to declare types for the function hello which gets exported.

Deploying the Example

If we have both the JavaScript source and the TypeScript source in the current folder, we can deploy them using dbjs. We need to provide the connection string for the database to dbjs. In case of the OTN release VM this is

localhost:1521/DBML

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

$ dbjs deploy simplesp.js -u scott -p tiger -c localhost:1521/DBML
+ simplesp.js
└─┬ hello
  └── SCALAR PROCEDURE SIMPLESP.HELLO 

On success, dbjs will tell us the created call specification. This tells us that we created a stored procedure with the name HELLO.

Executing the Stored Procedure

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

$ sqlplus scott/tiger

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 simplesp.hello();
Hello from Oracle Database MLE
Trace
    at Object.trace (JS/SCOTT/simplesp.js/1:1410:15)
    at Object.sayHello
(JS/SCOTT/simplesp.js/1:1433:13)
    at .js_HELLO68779360 (wqp:10:11)
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.

Simplification Using TypeScript

Instead of using two files, we can write both the stored procedure and the type information for the call specification in one TypeScript file. We first install the types for the Node.js builtins using npm:

$ npm install @types/node

/home/oracle
└── @types/node@8.0.25

npm WARN enoent ENOENT: no such file or directory, open
'/home/oracle/package.json'
npm WARN oracle No description
npm WARN oracle No repository field.
npm WARN oracle No README data
npm WARN oracle No license field.

Note that we are not building a full-fledged npm module here, which would require us to run npm init before and then save the dependency in package.json. Our deployment tool does not require this; the presence of the module at the time of deployment is sufficient. The warnings about missing package.json file can therefore safely be ignored.

Now, in the same folder we just executed npm, we create the TypeScript file tssimplesp.ts with the content:

var console = require('console')

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

This script provides one exported function called sayHello (in contrast to the JavaScript example where the function was exported as hello) which does exactly the same thing as the JavaScript version developed above. We only have to deploy it using

$ dbjs deploy tssimplesp.ts -u scott -p tiger -c localhost:1521/DBML

To call the function, we again connect with SQL*Plus to the database using the scott schema. Then activate the server output and call the stored procedure tssimplesp.sayHello.

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.

JavaScript Version

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 = require("@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");
    }
};

We create the file salraise.d.ts with the content

export function salraise(e: number, r: number);

to export the function salraise as a stored procedure. 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 using dbjs into the scott schema:

$ dbjs deploy salraise.js -u scott -p tiger -c localhost:1521/DBML

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

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

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

SQL> select sal from emp where empno = 7369;

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

SQL> call salraise.salraise(7369, 200);

Call completed.

SQL> select sal from emp where empno = 7369;

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

SQL> call salraise.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.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
SQL> update emp set sal = 800 where empno = 7369;
SQL> update emp set sal = 5000 where empno = 7839;

TypeScript Version

We can do the same example completely in TypeScript. We only need the file salraise.ts with the content:

import * as sql from "@oracle/sql";

function updateGetSal(empno: number, raise: number): number|boolean {
    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;
}

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

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

Before we can deploy this file, we need to locally install the types for the SQL driver. These types are currently not published. Instead, they are located in the folder /home/oracle/@types. To install them, please use

$ npm install /home/oracle/@types/oracle__sql

/home/oracle
└── @types/oracle__sql@0.0.1

npm WARN enoent ENOENT: no such file or directory, open
'/home/oracle/package.json'
npm WARN oracle No description
npm WARN oracle No repository field.
npm WARN oracle No README data
npm WARN oracle No license field.

Note that we are not building a full-fledged npm module here, which would require us to run npm init before and then save the dependency in package.json. Our deployment tool does not require this; the presence of the module at the time of deployment is sufficient. The warnings about missing package.json file can therefore safely be ignored.

After deploying the module with

$ dbjs deploy salraise.ts -u scott -p tiger -c localhost:1521/DBML

we can use the same tests we used for the JavaScript version above.

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.