JavaScript in Oracle Database 12c Release 2

JSR 223 introduced the idea of Scripting for the Java platform. Java 8 furnishes the Nashorn JavaScript engine as a more performant replacement for Rhino 

Per the Redmonk ranking (http://redmonk.com/sogrady/2016/07/20/language-rankings-6-16/) and the TIOBE index (http://www.tiobe.com/tiobe-index), JavaScript is one the most popular programming languages, disputing the top seat with Java.

This blog post is an update to a previous post which discussed the rationales and a proof of concept of Nashorn (JavaScript on the JVM) and Avatar.js (Node.js APIs on the JVM. This update discusses the real production implementation of Nashorn on the JVM in rhe database.

Rationales for JavaScript in the Database

The embedded JVM in Oracle Database 12c Release 2 (a.k.a. OJVM) supports Java 8 and the Nashorn JavaScript engine.Running JavaScript in the database stemmed from the same motivations for running Java in the database namely: reuse of skills, libraries and code for implementing data-bound modules or applications and performance.

However, JavaScript in the database makes sense only when it is processing data stored in the database thereby - and this is another major motivation -- yielding better performance by the virtue of running close to the data. The beauty of the Nashorn JavaScript engine on the JVM is the ability to call Java APIs from JavaScript e.g., calling JDBC APIs from JavaScript, for database access.

Let's create an EMPLOEES table storing employees data as JSON documents

CREATE TABLE employees (
  id    RAW(16) NOT NULL,
  data  CLOB,
  CONSTRAINT employees_pk PRIMARY KEY (id),
  CONSTRAINT employees_json_chk CHECK (data IS JSON)
);


INSERT INTO employees (id, data)
VALUES (SYS_GUID(),
        '{
          "EmpId"     : "100",
          "FirstName" : "Kuassi",
          "LastName"  : "Mensah",
          "Job"       : "Manager",
          "Email"     : "kuassi@oracle.com",
          "Address"   : {
                          "City" : "Redwood",
                          "Country" : "US"
                        }
         }');

...

...

INSERT INTO employees (id, data)
VALUES (SYS_GUID(),
        '{
          "EmpId"     : "300",
          "FirstName" : "Suresh",
          "LastName"  : "Mohan",
          "Job"       : "Developer",
          "Email"     : "Suresh@oracle.com",
          "Address"   : {
                          "City" : "Bangalore",
                          "Country" : "India"
                        }
         }');

COMMIT;

Here is a JavaScript code which uses JDBC for querying JSON documents stored in the EMPLOYEE table.

var selectQuery = function(id)
{
   var Driver = Packages.oracle.jdbc.OracleDriver;
   var oracleDriver = new Driver();
   var url = "jdbc:default:connection:";
   var output = "";
   var connection = oracleDriver.defaultConnection();
   var prepStmt;

   // Prepare statement    if(id == 'all') {
       prepStmt = connection.prepareStatement("SELECT a.data FROM employees a");
      } else {
       prepStmt = connection.prepareStatement("SELECT a.data FROM employees a WHERE a.data.EmpId = ?");
       prepStmt.setInt(1, id);
       }

   // execute Query   var resultSet = prepStmt.executeQuery();

   // display results   while(resultSet.next()) {
      output = output + resultSet.getString(1) + "
";
   }

   // cleanup    resultSet.close();
    prepStmt.close();
    connection.close();
    return output;
}

Fluent JavaScript API using SODA for Java with Nashorn

For accessing JSON Collections and documents, Oracle furnishes SODA for Java; this API allows No SQL and dot notation access to JSON.

Check out the code samples and scripts for running testSODA.java and testSODA.js in OJVM  

How to Run JavaScript in Oracle Database 

Your  schema (or database user) must be granted the DBJAVASCRIPT role (check with your DBA).

Your JavaScript source code (select.js) may originate from strings, files or Java resources.

function hello()
{
/*
*This is a sample Javascript file that prints "Hello World".
*/
var hellow = "Hello World";
return hellow;
}

var output = hello();
print(output);

Oracle recommends loading your JavaScript source code from a file into the database, as Java resource, using the loadjava utility

loadjava -v -u yourschema hello.js

Upon loading your JavaScrit code as a Java resource you may invoke it using: (i) either the dbms_java.javascript.run() procedure from SQL or PL/SQL, or (ii) DbmsJavaScript.run() Java call or the javax.script package.
Make sure that your database schema has been granted the DBJAVASCRIPT role.

Running JavaScript in the Database using DBMS_JAVA.JAVASCRIPT.RUN Procedure

From SQL or PL/SQL

SQL>set serveroutput on
SQL>call dbms_java.set_output(20000);
SQL>call dbms_javascript.run("hello.js"); 

Running JavaScript in the Database using DbmsJavaScript.run Java call

From Java running in the database

import oracle.aurora.rdbms.DbmsJavaScript; 
…
DbmsJavaScript.run("hello.js");

Notes: 

• The direct invocation of Nashorn classes is restricted in Oracle JVM.
• All scripting mode extensions are disabled in Oracle JVM.

Running JavaScript in the Database using the javax.script API

Invoking JavaScript in the database using the javax.script API requires the following steps:

1. Instantiate a script manager
2. Create an engine
3. Pass your resource stream reader as the argument to the eval method of the engine

import javax.script.*;
import java.net.*;
import java.io.*;
...

// create a script engine manager
ScriptEngineManager factory = new ScriptEngineManager();
// create a JavaScript engine
ScriptEngine engine =
    factory.getEngineByName("javascript");
// create schema resource URL
URL url = Thread.currentThread()
    .getContextClassLoader().getResource("hello.js");
engine.eval(new InputStreamReader(url.openStream()));
...

These steps can be turned into a wrapper class (the signature may differ depending on your input parameters and return value(s).

Here is a javax.script wrapper for invoking select.js JavaScript function (shown earlier).




create or replace and compile java resource named "InvokeScript" as

import javax.script.*;
import java.net.*;
import java.io.*;

public class InvokeScript {    

public static String eval(String inputId) throws Exception {
 String output = new String();
    try {
        // create a script engine manager
        ScriptEngineManager factory = new ScriptEngineManager();

        // create a JavaScript engine
        ScriptEngine engine = factory.getEngineByName("javascript");

        //read the script as a java resource
     engine.eval(new InputStreamReader(InvokeScript.class.getResourceAsStream("select.js")));
     // Alternative approach
     //engine.eval(Thread.currentThread().getContextClassLoader().getResource("select.js"));

        Invocable invocable = (Invocable) engine;
        Object selectResult = invocable.invokeFunction("selectQuery", inputId);
        output = selectResult.toString();
    } catch(Exception e) {
  output =e.getMessage();
 }
        return output;
    }
}
/




Then we need to create a SQL wrapper for the eval function

-- Create function
CREATE OR REPLACE FUNCTION invokeScriptEval(inputId varchar2) return varchar2 as language java 
name 'InvokeScript.eval(java.lang.String) 

  return java.lang.String'; 

/

Allow and make easy to call invokeScriptEval() from SQL or PL/SQL  

CREATE OR REPLACE PROCEDURE sqldemo(id IN varchar2)
IS
 output varchar2(10000);
BEGIN
 SELECT invokeScriptEval(id) INTO output from dual;
 dbms_output.put_line(output);
END;
/
SHOW ERRORS;

  

Finally, invoke SelectQuery function (in select.js), using Javax API thru SQLDEMO procedure

In a SQLPlus session
set serveroutput on
call dbms_java.set_output(5000);
call sqldemo('100');

REST Enabling JavaScript in the Database

You may turn your JavaScript function into a REST service deployed in Oracle Cloud by wrapping it with the Oracle Rest Data Service (ORDS). See the related blog post and the Java Developers guide in the Oracle Database 12c Release 2 doc library for more details.