One of the important features of Java 8 is the Nashorn JavaScript engine. Starting from Oracle Database 12c Release 2 (12.2.0.1), Oracle JVM supports Java 8, so you can execute JavaScript in the database using the Nashorn JavaScript engine, built into Oracle JVM running Java 8 codebase. Executing JavaScript directly in the database provides the following benefits:

1. Create a table in the database and populate it using JSON, as shown in the following example:

   DROP TABLE employees PURGE;
   
   CREATE TABLE employees (
     id    RAW(16) NOT NULL,
     data  CLOB,
     CONSTRAINT employees_pk PRIMARY KEY (id),
     CONSTRAINT employees_json_chk CHECK (data IS JSON)
   );
   
   TRUNCATE TABLE employees;
   
   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"     : "200",
             "FirstName" : "Nancy",
             "LastName"  : "Greenberg",
             "Job"       : "Manager",
             "Email"     : "Nancy@oracle.com",
             "Address"   : {
                             "City" : "Boston",
                             "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"
                           }
            }');
   
   INSERT INTO employees (id, data)
   VALUES (SYS_GUID(),
           '{
             "EmpId"     : "400",
             "FirstName" : "Nirmala",
             "LastName"  : "Sundarappa",
             "Job"       : "Manager",
             "Email"     : "Nirmala@oracle.com",
             "Address"   : {
                             "City" : "Redwood",
                             "Country" : "US"
                           }
            }');
   
   INSERT INTO employees (id, data)
   VALUES (SYS_GUID(),
           '{
             "EmpId"     : "500",
             "FirstName" : "Amarnath",
             "LastName"  : "Chandana",
             "Job"       : "Test Devloper",
             "Email"     : "amarnath@oracle.com",
             "Address"   : {
                            "City" : "Bangalore", 
                            "Country" : "India"
                           }
            }');

2. Write a JavaScript application using JDBC, as shown in the following example and load the JavaScript code into a schema:

   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) + "<br>";
       }
   
      // cleanup
       resultSet.close();
       prepStmt.close();
       connection.close();
       return output;
   }

3. Create a Java resource, as shown in the following example:

   create or replace and compile java source 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;
       }
   }
   /

4. Create a wrapper for the eval method of the engine, as shown in the following example:

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

5. Invoke the invokeScriptEval JavaScript function from SQL, as shown in the following example:

   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;

6. Invoke the sqldemo procedure from SQL, as shown in the following example:

   SQL> set serveroutput on
   SQL> call dbms_java.set_output(5000);
   SQL> call sqldemo('100');

1. Download the Oracle REST Data Services (ORDS) installer file from the following location:

   http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html

   Note:

   • You can perform similar steps for making your Java in the Database application a cloud-enabled service using Oracle REST Data Services (ORDS).

   • For installing ORDS, you must have a USERS tablespace with at least 40 MB space.

2. Install ORDS by selecting the default options, except the following:

   • When it prompts whether you want to use PL/SQL Gateway or not, select 2 for skipping the step.

   • When it prompts you to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER), select 2 for skipping the step.

   • When it prompts whether you wish to start the service in standalone mode or exit, select 1 for starting the service in standalone mode.

3. Assign a listening port to the service. By default, port 8080 is used, but you may change this value in the ords_params.properties file under the .../params directory. Then, use the following command to bounce the jersey server:

   java -jar ords.war

4. Create a procedure similar to the following as a wrapper to the invokeScriptEval method:

   Rem Create a procedure for select
   CREATE OR REPLACE PROCEDURE selectproc(id IN varchar2)
   IS
    output varchar2(10000);
   BEGIN
    SELECT invokeScriptEval(id) INTO output from dual;
    htp.prn(output);
   END;
   /
   SHOW ERRORS;

5. Create a procedure similar to the following for executing an external JavaScript SELECT query:

   begin
     ords.create_service(
       p_module_name => 'load.routes' ,
       p_base_path   => '/load/routes/',
       p_pattern     => 'nashorn/selectbyid/:id',
       p_source_type => 'plsql/block',
       p_source      => 'begin selectproc(:id); end;'
   );
      commit;
   end;
   /

6. Invoke the service from your browser, where the URL will be of the following format:

   http://<server>:<port>/ords/<workspace>/load/routes/nashorn/<your_JSON_query>/<input>

   For example:

   http://localhost:8090/ords/ordstest/load/routes/nashorn/selectbyid/100

   Here, 100 specifies the employee ID of the employee, whose details need to be fetched. The following image illustrates the output:

   Figure 5-1 Output of the JavaScript Application

   
   Description of "Figure 5-1 Output of the JavaScript Application"