Wednesday, November 09, 2016

REST Enable Java or JavaScript in the Database

REST Enable Java or JavaScript in the Database

The Oracle REST Data Service (ORDS) allows you to turn Java stored procedures or JavaScript stored procedures into REST Web Services that you may publish these in the Oracle REST Data Service.
See more details @ http://www.oracle.com/technetwork/developer-tools/rest-data-services/overview/index.html

Prerequisite

This article assumes you've already loaded your Java or JavaScript code in the Oracle database 12c Release 2 (for JavaScript); for Java, any supported database release will do (Oracle has been supporting Java in the database since version 8i.)
This blog post describes how to load and run JavaScript in Oracle database 12c Release 12.2.

Download and Configure ORDS

1) First step: download the latest ORDS  @ http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html

2) Extract the zip file in a directory say ../ords.3.0.8

3) Configure and Install

$ ls 
docs logs params examples ords.war readme.html

Navigate into params directory and edit the ords_params.properties file.
Modify the standalone.http.port value to a desired port # (e.g., 8090) and save the file.

$vi params/ords_params.properties
#Tue Jul 26 05:23:16 UTC 2016
db.hostname=
db.port=
db.servicename=
db.sid=
db.username=APEX_PUBLIC_USER
migrate.apex.rest=false
rest.services.apex.add=
rest.services.ords.add=true
schema.tablespace.default=SYSAUX
schema.tablespace.temp=TEMP
standalone.http.port=8090
standalone.static.images=
user.tablespace.default=USERS
user.tablespace.temp=TEMP
~                                                                               
~                                                                               
~                                                                                                                                                              
~                                                                               
~                                                                               
"params/ords_params.properties" 17L, 467C                     1,1           All

Note: Do not modify any other property

$ java -jar ords.war
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data:/u01/oracle/ords.3.0.6/ordsi
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:pdb1
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema.

Enter the username with SYSDBA privileges to verify the installation [SYS]:
Enter the database password for SYS:Welcome1
Confirm password:Welcome1
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Sep 07, 2016 3:53:19 AM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults, apex_pu
Installing Oracle REST Data Services version 3.0.6.176.08.46
... Log file written to /u01/oracle/ords.3.0.6/logs/ords_install_core_2016-09-07_035319_00534.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /u01/oracle/ords.3.0.6/logs/ords_install_datamodel_2016-09-07_035342_00050.log
Completed installation for Oracle REST Data Services version 3.0.6.176.08.46. Elapsed time: 00:00:23.840 

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1
Enter 1 if using HTTP or 2 if using HTTPS [1]:1
2016-09-07 03:54:28.867:INFO::main: Logging initialized @158428ms
Sep 07, 2016 3:54:29 AM oracle.dbtools.standalone.StandaloneJetty setupDocRoot
INFO: Disabling document root because the specified folder does not exist: /u01/oracle/ords.3.0.6/ordsi/ords/standalone/doc_root
2016-09-07 03:54:29.525:INFO:oejs.Server:main: jetty-9.2.z-SNAPSHOT
Sep 07, 2016 3:54:29 AM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: No encryption key found in configuration, generating key
Sep 07, 2016 3:54:29 AM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: No mac key found in configuration, generating key
Sep 07, 2016 3:54:29 AM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults
Sep 07, 2016 3:54:29 AM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: Updated configuration with generated keys
2016-09-07 03:54:29.793:INFO:/ords:main: INFO: Using configuration folder: /u01/oracle/ords.3.0.6/ordsi/ords
2016-09-07 03:54:29.793:INFO:/ords:main: FINEST: |ApplicationContext [configurationFolder=/u01/oracle/ords.3.0.6/ordsi/ords, services=Application Scope]|
Sep 07, 2016 3:54:29 AM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Validating pool: |apex|pu|
Sep 07, 2016 3:54:29 AM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: |apex|pu| is correctly configured
config.dir
2016-09-07 03:54:30.298:INFO:/ords:main: INFO: Oracle REST Data Services initialized|Oracle REST Data Services version : 3.0.6.176.08.46|Oracle REST Data Services server info: jetty/9.2.z-SNAPSHOT|
2016-09-07 03:54:30.305:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@429bd883{/ords,null,AVAILABLE}
2016-09-07 03:54:30.346:INFO:oejs.ServerConnector:main: Started ServerConnector@b7f23d9{HTTP/1.1}{0.0.0.0:8090}
2016-09-07 03:54:30.348:INFO:oejs.Server:main: 
Started @159913ms   

4) Allow your schema to use ORDS 

SQL> exec ords.enable_schema;
SQL> COMMIT;     

Define and Configure Your ORDS Service

Let's use the JavaScript procedure defined in an earlier blog post @
http://db360.blogspot.in/2016/11/javascript-in-oracle-database-12c.html

Rem Create a procedure based on the select.js and it's javax.script wrapper (see the previous blog post)
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;

-- delete load.routes module
begin
  ords_services.delete_module(
   p_name => 'load.routes');
  commit;
end;
/
SHOW ERRORS;

-- External JS select query
-- URL: load/routes/nashorn/select
-- procedure: selectproc
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;
/
SHOW ERRORS;  

Let's use the JavaScript procedure defined in an earlier blog post 
Open your  web browser and navigate to  http://localhost:8090/ords/ordstest/load/routes/nashorn/selectbyid/100.
 You must see the JSON document of the employee with empid as 100 displayed.
That's it! You have just created your first ORDS service
The same process can be used tor Java stored procedures or other JavaScript procedures in the database.

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.