Performing Oracle Database Integration with Java, Hadoop, Spark and JavaScript


Options



Before You Begin

Purpose

In this tutorial, you will learn how to develop and and deploy fast, scalable, highly available and secure applications using new features in Oracle 12c Database OJVM. You will experience Cloud Data Service with Java 8 Nashorn, Market Basket Analysis with Oracle In-Database Container for Hadoop, and Oracle JVM Web Services Call-Out Utility here.

Time to Complete

Approximately 2 hours.

Prerequisites

Before starting this tutorial, you should have:

  1. Installed Oracle Database 12c Release 2

  2. Start the database instance. login to SQL*PLUS as the SYS user and execute the following command.

    sqlplus sys/Welcome1 as sysdba
startup

    Note: The startup must be executed every time you restart the machine.

  3. Installed and unzipped the ords.3.0.6.176.08.46.zip or the latest version file into /u01/oracle directory.

  4. Unzipped the ords.zip file into /u01/oracle directory.

  5. Unzipped the oc4hadoop.zip file into $ORACLE_HOME directory.

  6. Unzipped the ojvmwcu_oow.zip file into /u01/oracle directory.

  7. Copy the /u01/oracle/ojvmwcu_oow/ojvmwcu directory into ORACLE_HOME/javavm directory.

  8. Install the jersey files that are required for running the Web Call Utility for REST based web services. Open a terminal. Navigate to $ORACLE_HOME/javavm/ojvmwcu/install directory. Execute the install_ojvmwcu.sql file.

    cd $ORACLE_HOME/javavm/ojvmwcu/install
sqlplus sys/Welcome1@pdb1 as sysdba @install_ojvmwcu.sql
ojvmwcu

Cloud Data Service with Java 8 Nashorn

Nashorn is a Java Script engine which is present inside the Oracle database. It a lightweight high-performance JavaScript runtime in Java with a native JVM. Nashorn implements a Javascript engine and run JavaScript on the JVM inside the Oracle Database. This section describes about a working example of Oracle REST Data Services

  1. Start the Database.

  2. Open a new terminal and navigate to the u01/oracle/ords.3.0.6 directory. Execute ls to see the contents inside this directory.

    cd /u01/oracle/ords.3.0.6

    $ cd /u01/oracle/ords.3.0.6
$ ls
docs      logs      params
examples  ords.war  readme.html

  3. Navigate into params directory and edit the ords_params.properties file. Modify the standalone.http.port value to 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, as they will be populated automatically by the program.

  4. Execute the ords.war file. It will prompt for various values. Please provide the values as given below.

    java -jar ords.war

    $ 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: Enter any desired password
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

    Observe the execution. There must not be any errors during execution. Minimize the terminal.

  5. Open a new terminal and navigate to the /u01/oracle/ords directory. Execute ls to see the contents inside this directory.

    cd /u01/oracle/ords

    $ cd /u01/oracle/ords
$ ls
employees.sql  ords_latest_demo.sql  select.js  sql_demo.sql

  6. Open and examine employees.sqlthe file.

    cat employees.sql

    $ cat employees.sql 
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"
                        }
         }');

  7. Open and examine select.jsthe file.

    cat select.js

    $ cat select.js 
var selectQuery = function(id)
{
   var Driver = Packages.oracle.jdbc.OracleDriver;
   var oracleDriver = new Driver();
   var url = "jdbc:default:connection:";
   var query = "";
   var output = "";

   if(id == 'all') {
	query ="SELECT a.data FROM employees a";
   } else {
	query ="SELECT a.data FROM employees a WHERE a.data.EmpId=" + id;
   }
   var connection = oracleDriver.defaultConnection();

   // Prepare statement
	var preparedStatement = connection.prepareStatement(query);
   
   // execute Query
	var resultSet = preparedStatement.executeQuery();   

   // display results
	while(resultSet.next()) {
		output = output + resultSet.getString(1) + "<br>";
	}
   
   // cleanup
    resultSet.close();
    preparedStatement.close();
    connection.close();
    return output;
}

  8. Open and examine ords_latest_demo.sqlthe file.

    cat ords_latest_demo.sql

    $ cat ords_latest_demo.sql 
SET und off
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 128
set verify off

alter session set container=pdb1;

CREATE user ORDSTEST identified by ORDSTEST;

GRANT
     dbjavascript,
     connect,
     resource
to ORDSTEST;

CALL DBMS_JAVA.GRANT_PERMISSION(UPPER('ORDSTEST'), 'SYS:java.sql.SQLPermission', 'deregisterDriver', '');
COMMIT;

CALL DBMS_JAVA.LOADJAVA('-v -r /u01/oracle/ords/select.js','((* ORDSTEST) (* SYS) (* PUBLIC))');

ALTER USER ORDSTEST QUOTA UNLIMITED ON USERS;
ALTER DATABASE DEFAULT TABLESPACE USERS;

CONNECT ORDSTEST/ORDSTEST@PDB1;

@/u01/oracle/ords/employees.sql

exec ords.enable_schema;
COMMIT;

-- create a java source
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")));
        Invocable invocable = (Invocable) engine;
        Object selectResult = invocable.invokeFunction("selectQuery", inputId);
        output = selectResult.toString();
    } catch(Exception e) {
		output =e.getMessage();
	}
        return output;
    }
}
/

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

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;

-- 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;

  9. Login to sqlplus as SYSDBA and execute the ords_latest_demo.sql file.

    sqlplus / as sysdba 

    $ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 29 07:05:41 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @ords_latest_demo.sql
SQL> SET FEEDBACK ON
SQL> SET LINESIZE 128
SQL> set verify off
SQL> 
SQL> alter session set container=pdb1;

Session altered.

SQL> 
SQL> CREATE user ORDSTEST identified by ORDSTEST;

ORDTEST user created.

SQL> GRANT
  2  	  dbjavascript,
  3  	  connect,
  4  	  resource
  5  to ORDSTEST;

Grant succeeded.

SQL> 
SQL> CALL DBMS_JAVA.GRANT_PERMISSION(UPPER('ORDSTEST'), 'SYS:java.sql.SQLPermission', 'deregisterDriver', '');

Call completed.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> CALL DBMS_JAVA.LOADJAVA('-v -r /u01/oracle/ords/select.js','((* ORDSTEST) (* SYS) (* PUBLIC))');

Call completed.

SQL> 
SQL> ALTER USER ORDSTEST QUOTA UNLIMITED ON USERS;

User altered.

SQL> ALTER DATABASE DEFAULT TABLESPACE USERS;

Database altered.

SQL> 
SQL> CONNECT ORDSTEST/ORDSTEST@PDB1;
Connected.
SQL> 
SQL> @/u01/oracle/ords/employees.sql
SQL> DROP TABLE employees PURGE;

Table dropped.

SQL> 
SQL> CREATE TABLE employees (
  2    id	RAW(16) NOT NULL,
  3    data	CLOB,
  4    CONSTRAINT employees_pk PRIMARY KEY (id),
  5    CONSTRAINT employees_json_chk CHECK (data IS JSON)
  6  );

Table created.

SQL> 
SQL> TRUNCATE TABLE employees;

Table truncated.

SQL> 
SQL> INSERT INTO employees (id, data)
  2  VALUES (SYS_GUID(),
  3  	     '{
  4  	       "EmpId"	   : "100",
  5  	       "FirstName" : "Kuassi",
  6  	       "LastName"  : "Mensah",
  7  	       "Job"	   : "Manager",
  8  	       "Email"	   : "kuassi@oracle.com",
  9  	       "Address"   : {
 10  			       "City" : "Redwood",
 11  			       "Country" : "US"
 12  			     }
 13  	      }');

1 row created.

SQL> 
SQL> INSERT INTO employees (id, data)
  2  VALUES (SYS_GUID(),
  3  	     '{
  4  	       "EmpId"	   : "200",
  5  	       "FirstName" : "Nancy",
  6  	       "LastName"  : "Greenberg",
  7  	       "Job"	   : "Manager",
  8  	       "Email"	   : "Nancy@oracle.com",
  9  	       "Address"   : {
 10  			       "City" : "Boston",
 11  			       "Country" : "US"
 12  			     }
 13  	      }');

1 row created.

SQL> INSERT INTO employees (id, data)
  2  VALUES (SYS_GUID(),
  3  	     '{
  4  	       "EmpId"	   : "300",
  5  	       "FirstName" : "Suresh",
  6  	       "LastName"  : "Mohan",
  7  	       "Job"	   : "Developer",
  8  	       "Email"	   : "Suresh@oracle.com",
  9  	       "Address"   : {
 10  			       "City" : "Bangalore",
 11  			       "Country" : "India"
 12  			     }
 13  	      }');

1 row created.

SQL> 
SQL> INSERT INTO employees (id, data)
  2  VALUES (SYS_GUID(),
  3  	     '{
  4  	       "EmpId"	   : "400",
  5  	       "FirstName" : "Nirmala",
  6  	       "LastName"  : "Sundarappa",
  7  	       "Job"	   : "Manager",
  8  	       "Email"	   : "Nirmala@oracle.com",
  9  	       "Address"   : {
 10  			       "City" : "Redwood",
 11  			       "Country" : "US"
 12  			     }
 13  	      }');

1 row created.

SQL> 
SQL> INSERT INTO employees (id, data)
  2  VALUES (SYS_GUID(),
  3  	     '{
  4  	       "EmpId"	   : "500",
  5  	       "FirstName" : "Amarnath",
  6  	       "LastName"  : "Chandana",
  7  	       "Job"	   : "Test Devloper",
  8  	       "Email"	   : "amarnath@oracle.com",
  9  	       "Address"   : {
 10  			      "City" : "Bangalore",
 11  			      "Country" : "India"
 12  			     }
 13  	      }');

1 row created.

SQL> 
SQL> 
SQL> exec ords.enable_schema;

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> -- create a java source
SQL> create or replace and compile java source named "InvokeScript" as
  2  import javax.script.*;
  3  import java.net.*;
  4  import java.io.*;
  5  
  6  public class InvokeScript {
  7  	 public static String eval(String inputId) throws Exception {
  8  	     String output = new String();
  9  	 try {
 10  	     // create a script engine manager
 11  	     ScriptEngineManager factory = new ScriptEngineManager();
 12  	     // create a JavaScript engine
 13  	     ScriptEngine engine = factory.getEngineByName("javascript");
 14  	     //read the script as a java resource
 15  	     engine.eval(new InputStreamReader(InvokeScript.class.getResourceAsStream("/select.js")));
 16  	     Invocable invocable = (Invocable) engine;
 17  	     Object selectResult = invocable.invokeFunction("selectQuery", inputId);
 18  	     output = selectResult.toString();
 19  	 } catch(Exception e) {
 20  		     output =e.getMessage();
 21  	     }
 22  	     return output;
 23  	 }
 24  }
 25  /

Java created.

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

Function created.

SQL> Rem Create a procedure for select
SQL> CREATE OR REPLACE PROCEDURE sqldemo(id IN varchar2) IS output varchar2(10000);
  2  BEGIN
  3  SELECT invokeScriptEval(id) INTO output from dual;
  4  dbms_output.put_line(output);
  5  END;
  6  /

Procedure created.

SQL> Rem Create a procedure for select
SQL> CREATE OR REPLACE PROCEDURE selectproc(id IN varchar2)
  2  IS
  3  	output varchar2(10000);
  4  BEGIN
  5  	SELECT invokeScriptEval(id) INTO output from dual;
  6  	htp.prn(output);
  7  END;
  8  /

Procedure created.

SQL> SHOW ERRORS;
No errors.
SQL> 
SQL> -- delete load.routes module
SQL> begin
  2    ords_services.delete_module(
  3  	p_name => 'load.routes');
  4    commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> SHOW ERRORS;
No errors.
SQL> 
SQL> -- External JS select query
SQL> -- URL: load/routes/nashorn/select
SQL> -- procedure: selectproc
SQL> begin
  2    ords.create_service(
  3  	 p_module_name => 'load.routes' ,
  4  	 p_base_path   => '/load/routes/',
  5  	 p_pattern     => 'nashorn/selectbyid/:id',
  6  	 p_source_type => 'plsql/block',
  7  	 p_source      => 'begin selectproc(:id); end;'
  8  );
  9  	commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> SHOW ERRORS;
No errors.

  10. Run the following command in the terminal.

    cd /u01/oracle/ords

    loadjava -u ORDSTEST/ORDSTEST@PDB1 -v -f -r select.js
    $ cd /u01/oracle/ords
$ loadjava -u ORDSTEST/ORDSTEST@PDB1 -v -f -r select.js
arguments: '-u' 'ORDSTEST/***@PDB1' '-v' '-f' '-r' 'select.js' 
creating : resource select.js
loading  : resource select.js
skipping : resource select.js
Classes Loaded: 0
Resources Loaded: 1
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
  11. Run the following commands to call the JavaScript from sql.
    $ sqlplus ORDSTEST/ORDSTEST@pdb1
    set serveroutput on
    call dbms_java.set_output(80000);
    call sqldemo(100);

    SQL> set serveroutput on
    
SQL> call dbms_java.set_output(80000); 
    
Call completed.

SQL> call sqldemo(100);
{
	  "EmpId"     : "100",
	  "FirstName" : "Kuassi",

"LastName"  : "Mensah",
	  "Job"       : "Manager",
	  "Email"
: "kuassi@oracle.com",
	  "Address"   : {

"City" : "Redwood",
			  "Country" : "US"

}
	 }
    

Call completed.

  12. Open Firefox web browser and navigate to the http://localhost:8090/ords/ordstest/load/routes/nashorn/selectbyid/100url. You must be able to see the details if the employee with empid as 100 displayed. Notice that 100 at the end of the URL with fetch details of the employee with empid 100.

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

    Oracle REST Data Services

  13. If you want to see details of all the employees, then navigate to the http://localhost:8090/ords/ordstest/load/routes/nashorn/selectbyid/all in the web browser. Notice that all at the end of the URL will fetch details about all the employees.

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

    Oracle REST Data Services

    Notice that the details of employees are displayed in JSON format.

Market Basket Analysis with Oracle In-Database Container for Hadoop

Oracle In-Database Container for Hadoop (oc4h) is a set of APIs that lets you apply MapReduce analysis to data residing in Oracle Database. It provides MapReduce techniques using both SQL and Java. Using oc4h, you can avoid moving data residing in Oracle Database to a separate infrastructure, without the need to invest in a Hadoop cluster, and at the same time, you can have enterprise-class security, flexibility, and efficiency.

  1. Open a new terminal. Navigate to ORACLE_HOME directory. Execute ls to see the contents inside this directory.

    cd $ORACLE_HOME

    $ cd $ORACLE_HOME
    $ls
addnode      dbjava         inventory  oc4j         perl           sqldeveloper
apex         dbs            javavm     odbc         plsql          sqlj
assistants   dc_ocm         jdbc       olap         plugins        sqlpatch
bin          deinstall      jdk        OPatch       precomp        sqlplus
ccr          demo           jlib       opmn         QOpatch        srvm
cdata        diagnostics    ldap       oracore      R              suptools
cfgtoollogs  dmu            lib        oraInst.loc  racg           sysman
clone        dv             log        ord          rdbms          ucp
crs          env.ora        md         ordim        relnotes       usm
css          has            mgw        ords         root.sh        utl
ctx          hs             network    oss          schagent.conf  wwg
cv           install        nls        oui          scheduler      xdk
data         instantclient  oc4hadoop  owm          slax

  2. Navigate to oc4hadoop directory. This directory contains the files necessary to configure oc4h in the database.

    cd oc4hadoop

    $ cd oc4hadoop 
$ ls
configure_oc4hadoop.sql       install_log_gen_catcon_kill_sess_32309.sql
demo                          install_oc4hadoop.sql
doc                           lib
export_oc4hadoop_classes.sql  load_oc4hadoop.pl
inputformats                  load_oc4hadoop.sql
install_log0.log              perl
install_log_catcon_32094.lst  README
install_log_catcon_32188.lst  uninstall_oc4hadoop.sql
install_log_catcon_32309.lst

  3. Open and Examine the install_oc4hadoop.sql file. This contains the code to invoke various other .sql files which will configure oc4h in your database.

    cat install_oc4hadoop.sql

    $  cat install_oc4hadoop.sql  

Rem Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
Rem
Rem    NAME
Rem      install_oc4hadoop.sql
Rem
Rem    DESCRIPTION
Rem      Creates installation procedures, loads oc4hadoop jars from filesystem
Rem      on server and grants execute to required classes
Rem
Rem    VERSION
Rem     1.0.1

@configure_oc4hadoop.sql &1
@load_oc4hadoop.sql
@export_oc4hadoop_classes.sql

    Note: Also examine the configure_oc4hadoop.sql , load_oc4hadoop.sql , export_oc4hadoop_classes.sqlfiles.

  4. Execute the install_oc4hadoop.sql file. 

    $ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -b install_log install_oc4hadoop.sql --pdbhadoop
    $  $ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -b install_log install_oc4hadoop.sql --pdbhadoop 
catcon: ALL catcon-related output will be written to [/u01/app/oracle/database/12.2.0/dbhome_1/oc4hadoop/install_log_catcon_32309.lst]
catcon: See [/u01/app/oracle/database/12.2.0/dbhome_1/oc4hadoop/install_log*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/database/12.2.0/dbhome_1/oc4hadoop/install_log_*.lst] files for spool files, if any
reset_seed_pdb_mode: output produced in exec_DB_script [
    
    SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 28 10:06:30 2016
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    SQL> Connected.
    SQL>   2  
    Session altered.
    
    SQL>   2  
    Pluggable database altered.
    
    SQL>   2  
    Pluggable database altered.
    
    SQL> SQL> 
    SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
  ] end of output produced in exec_DB_script
catcon.pl: completed successfully

    Note: Examine the output. Verify that there are no sql errors during execution.

  5. Login to SQLPLUS as SYSDBA to grant the hr user with the oc4hadoop permissions, which was internally generated in the previous step. 

    sqlplus sys/Welcome1@pdb1 as sysdba
    grant oc4hadoop to hr;
    exit
    $ sqlplus sys/Welcome1@pdb1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 29 07:40:48 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> grant oc4hadoop to hr;

Grant succeeded.

SQL> exit;

    Note: Examine the output. Verify that there are no sql errors during execution.

  6. Navigate to demo/MarketBasketAnalysis/perl directory. Execute ls to see the contents inside this directory.

    cd demo/MarketBasketAnalysis/perl

    $ cd demo/MarketBasketAnalysis/perl
$ ls
run.pl

  7. Open and examine the run.pl file.

    cat run.pl

    $ cat run.pl

#!/usr/local/bin/perl
# Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
#
#    NAME
#      run.pl
#
#    DESCRIPTION
#     This script setup the demo and run it. 
#
#    VERSION
#     1.0.1
#
use Term::ReadKey;

print "Enter schema name where Market Basket Analysis will be run\n";
$username = <STDIN>;
chomp($username);

print "Enter password\n";
ReadMode('noecho');
$password = <STDIN>;
chomp($password);
ReadMode(0);

print "Loading data\n";
chdir "../sql";
system("sqlplus $username/$password @ createTable.sql");

chdir "../data";
system("sqlldr $username/$password CONTROL=retail.ctl LOG=retail_insert.log BAD=retail_insert.bad");

print "Loading jar file\n";
chdir "../lib";
system("loadjava -u $username/$password -v -r -f -resolver \"((* $username)(* OC4H_INSTALL)(* SYS)(* -))\" json-parse.jar;");

print "Loading java file\n";
chdir "../src";
system("loadjava -u $username/$password -v -r -f -resolver \"((* $username)(* OC4H_INSTALL)(* SYS))\" MarketBasket.java;");

chdir "../sql";
print "Running example\n" ;
system("sqlplus $username/$password @ MarketBasket.sql");

chdir "../src";
print "Droping javaFiles\n";
system("dropjava -u $username/$password -r -f -v MarketBasket.java;"); 
chdir "../lib";
system("dropjava -u $username/$password -r -f -v json-parse.jar;");

  8. Execute the following command in the terminal.

    export TWO_TASK=pdb1

  9. Execute the run.pl perl script. Provide the below given value when prompted.

    perl run.pl

    Enter schema name where Market Basket Analysis will be run        hr
Enter password       hr                                                           
    $ perl run.pl
 
Enter schema name where Market Basket Analysis will be run
hr
Enter password
Loading data

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 28 10:23:00 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Thu Jul 28 2016 10:22:42 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

  10. Examine the output.

    
Table dropped.


Table created.


Table dropped.


Table created.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Jul 28 10:23:00 2016

Copyright (c) 1982, 2016, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Commit point reached - logical record count 448
Commit point reached - logical record count 512
Commit point reached - logical record count 576
Commit point reached - logical record count 640
Commit point reached - logical record count 704
Commit point reached - logical record count 768
Commit point reached - logical record count 832
Commit point reached - logical record count 896
Commit point reached - logical record count 960
Commit point reached - logical record count 1000

Table INTABLE:
  1000 Rows successfully loaded.

Check the log file:
  retail_insert.log
for more information about the load.
Loading jar file
arguments: '-u' 'hr/***' '-resolver' '((* hr)(* OC4H_INSTALL)(* SYS)(* -))' '-v' '-r' '-f' 'json-parse.jar' 
creating : class org/codehaus/jettison/json/JSONArray with resolver ((* hr)(* OC4H_INSTALL)(* SYS)(* -))
loading  : class org/codehaus/jettison/json/JSONArray
created  : CREATE$JAVA$LOB$TABLE
creating : class org/codehaus/jettison/json/JSONException with resolver ((* hr)(* OC4H_INSTALL)(* SYS)(* -))
loading  : class org/codehaus/jettison/json/JSONException
creating : class org/codehaus/jettison/json/JSONObject$1 with resolver ((* hr)(* OC4H_INSTALL)(* SYS)(* -))
loading  : class org/codehaus/jettison/json/JSONObject$1
creating : class org/codehaus/jettison/json/JSONObject$Null with resolver ((* hr)(* OC4H_INSTALL)(* SYS)(* -))
loading  : class org/codehaus/jettison/json/JSONObject$Null
creating : class org/codehaus/jettison/json/JSONObject with resolver ((* hr)(* OC4H_INSTALL)(* SYS)(* -))
loading  : class org/codehaus/jettison/json/JSONObject
creating : class org/codehaus/jettison/json/JSONTokener with resolver ((* hr)(* OC4H_INSTALL)(* SYS)(* -))
loading  : class org/codehaus/jettison/json/JSONTokener
resolving: class org/codehaus/jettison/json/JSONArray
skipping : class org/codehaus/jettison/json/JSONException
skipping : class org/codehaus/jettison/json/JSONObject$1
skipping : class org/codehaus/jettison/json/JSONObject$Null
skipping : class org/codehaus/jettison/json/JSONObject
skipping : class org/codehaus/jettison/json/JSONTokener
Classes Loaded: 6
Resources Loaded: 0
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
Loading java file
arguments: '-u' 'hr/***' '-resolver' '((* hr)(* OC4H_INSTALL)(* SYS))' '-v' '-r' '-f' 'MarketBasket.java' 
creating : source oracle/sql/hadoop/examples/MarketBasket with resolver ((* hr)(* OC4H_INSTALL)(* SYS))
loading  : source oracle/sql/hadoop/examples/MarketBasket
resolving: source oracle/sql/hadoop/examples/MarketBasket
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
Running example

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 28 10:23:06 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Thu Jul 28 2016 10:23:02 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

DROP PROCEDURE MarketBasket
*
ERROR at line 1:
ORA-04043: object MARKETBASKET does not exist



Procedure created.


Call completed.

Elapsed: 00:00:05.66

ITEM_PAIR   FREQUENCY
---------- ----------
1, 14              19
24, 26             19
4, 6               19

3 rows selected.

Elapsed: 00:00:00.00
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Droping javaFiles
dropping: source oracle/sql/hadoop/examples/MarketBasket
dropping: class org/codehaus/jettison/json/JSONArray
dropping: class org/codehaus/jettison/json/JSONException
dropping: class org/codehaus/jettison/json/JSONObject$1
dropping: class org/codehaus/jettison/json/JSONObject$Null
dropping: class org/codehaus/jettison/json/JSONObject
dropping: class org/codehaus/jettison/json/JSONTokener

    Note: Some files are dropped after the execution of code.

Oracle JVM Web Services Call-Out Utility

Starting from Oracle Database 12c Release 2 (12.2.0.1), you can use the Oracle JVM Web Services Call-Out Utility to call the operations from the Web services running in the network, from Oracle Database. This utility accepts the SOAP Web services specified in WSDL format and REST Web services specified in WADL format, generates the client stubs as well as PL/SQL wrapper functions and PL/SQL wrappers for granting and revoking basic permissions required for running the Web services.

  1. Open a new terminal. Navigate to /u01/oracle/ojvmwcu_oow directory. Execute ls to see the contents inside this directory.

    cd /u01/oracle/ojvmwcu_oow

    $ cd /u01/oracle/ojvmwcu_oow/
$ ls
demo                jdk1.7.0_79  README
ojvmwcu             wadl-dist-1.1.6     glassfish3.2.2OLE6

  2. Set the JAVA_HOME to the jdk 1.7.0 which is present inside the current directory.

    export JAVA_HOME=/u01/oracle/ojvmwcu_oow/jdk1.7.0_79

    Note: The scope of the command is within the current terminal only. We are setting the JAVAHOME to jdk1.7, such that it is compatible with the glassfish server.

  3. Start the glassfish server.

    glassfish3.2.2OLE6/bin/asadmin start-domain domain2

    Note: Examine the output. Command start-domain executed successfully in the output signifies that the glassfish had started successfully.

  4. Open the Firefox web browser and navigate to http://localhost:47483/OOW_REST/Company/application.wadl. The XML file that is displayed is the WADL file for RESTful web service.

    http://localhost:47483/OOW_REST/Company/application.wadl

    Oracle JVM Web Services Call-Out Utility

  5. Open the Firefox web browser and navigate to http://localhost:47483/OOW_SOAP/Employee?WSDL. The XML file that is displayed is the WSDL file for SOAP based web service.

    http://localhost:47483/OOW_SOAP/Employee?WSDL

    Oracle JVM Web Services Call-Out Utility

REST based WebServices

You can use Oracle JVM Web Services Call-Out Utility to access REST based Web Services.

  1. Open a new terminal. Navigate to /u01/oracle/ojvmwcu_oow directory. Execute ls to see the contents inside this directory.

    cd /u01/oracle/ojvmwcu_oow

    $ cd /u01/oracle/ojvmwcu_oow/
$ ls
demo                jdk1.7.0_79        README
ojvmwcu             wadl-dist-1.1.6    glassfish3.2.2OLE6

  2. Navigate to ojvmwcu/install directory. Execute ls to see the contents inside this directory.

    cd ojvmwcu/install/

    $ ls
configure_ojvmwcu.sql       grant_ojvmwcu.sql    load_ojvmwcu.sql
export_ojvmwcu_classes.sql  install_ojvmwcu.sql  uninstall_ojvmwcu.sql

  3. Set the TWO_TASK variable.

    $ export TWO_TASK=pdb1;

  4. Run the grant command to grant permission to hr.

    $ sqlplus sys/Welcome1 as sysdba @grant_ojvmwcu.sql hr

  5. Navigate to demo/rest directory. Observe the files present in it.

    cd demo/rest

    $ cd demo/rest
$ ls
demo.sql   run_REST_demo.sh

  6. Open and examine the demo.sql file. Notice that the REST web service is invoked and result is printed.

    cat demo.sql

    $ cat demo.sql
set serveroutput on;
set linesize 1000;
declare 
response varchar2(1000);
begin
response := restws.Employee_getAsJson_class();
dbms_output.put_line('-----------------------');
dbms_output.put_line('Response REST:');
dbms_output.put_line('-----------------------');
dbms_output.put_line( response );
dbms_output.put_line('-----------------------');
end;
/

  7. Open and examine the run_REST_demo.sh file. Notice that the restws_wrapper.sql file and demo_rest.sql file are invoked.

    cat run_REST_demo.sh

    $ run_REST_demo.sh
#!/bin/sh
perl /u01/app/oracle/product/12.2.0/dbhome_1/javavm/ojvmwcu/bin/ojvmwcu.pl -wadl "http://localhost:47483/OOW_REST/Company/application.wadl" -p com.oracle.rest -v -keepsrc -name restws -cp $ORACLE_HOME/javavm/ojvmwcu/lib/javax.ws.rs-api-2.0.1.jar -out rest -t ../../wadl-dist-1.1.6;
cd rest;
loadjava -r -f -v -u hr/hr restws.jar ;
sqlplus hr/hr <<EOF
@ restws_wrapper.sql;
exit;
EOF
cd -;
sqlplus hr/hr <<EOF
@ demo.sql;
exit;
EOF

  8. Run the run_REST_demo.sh file and observe the output. You could notice that person details are printed with address, id , name

    sh run_REST_demo.sh

    $ sh run_REST_demo.sh
OjvmWCU : Using JAVA_HOME as /usr/java/jdk1.8.0_91
OjvmWCU INFO: Starting OjvmWCU
OjvmWCU INFO: Generating Client Stubs
OjvmWCU INFO: java -jar ../../wadl-dist-1.1.6/lib/wadl-cmdline-1.1.6.jar -o rest -p com.oracle.rest -s jaxrs20 http://localhost:47483/OOW_REST/Company/application.wadl 
OjvmWCU INFO: JAX-RS Output
OjvmWCU INFO: Finished JAX-RS
OjvmWCU INFO: Generating Wrappers
OjvmWCU INFO: Parsing Generated Class Files
OjvmWCU INFO: Interpreting Webservice Operations
OjvmWCU INFO: Generating Static Java Class
OjvmWCU INFO: Generating PLSQL Wrapper
OjvmWCU INFO: Generating Jar files
OjvmWCU INFO: Adding Entry - CatalogManager.properties
OjvmWCU INFO: Adding Entry - Localhost_OOW_RESTCompany.class
OjvmWCU INFO: Adding Entry - Localhost_OOW_RESTCompany$1.class
OjvmWCU INFO: Adding Entry - Localhost_OOW_RESTCompany$Employee.class
OjvmWCU INFO: Adding Entry - restws.class
OjvmWCU INFO: Adding Entry - Localhost_OOW_RESTCompany$WebApplicationExceptionMessage.class
OjvmWCU INFO: Done Executing OjvmWCU
arguments: '-u' 'hr/***' '-r' '-f' '-v' 'restws.jar' 
creating : resource META-INF/MANIFEST.MF
loading  : resource META-INF/MANIFEST.MF
creating : resource CatalogManager.properties
loading  : resource CatalogManager.properties
creating : class com/oracle/rest/Localhost_OOW_RESTCompany
loading  : class com/oracle/rest/Localhost_OOW_RESTCompany
creating : class com/oracle/rest/Localhost_OOW_RESTCompany$1
loading  : class com/oracle/rest/Localhost_OOW_RESTCompany$1
creating : class com/oracle/rest/Localhost_OOW_RESTCompany$Employee
loading  : class com/oracle/rest/Localhost_OOW_RESTCompany$Employee
creating : class com/oracle/rest/restws
loading  : class com/oracle/rest/restws
creating : class com/oracle/rest/Localhost_OOW_RESTCompany$WebApplicationExceptionMessage
loading  : class com/oracle/rest/Localhost_OOW_RESTCompany$WebApplicationExceptionMessage
skipping : resource META-INF/MANIFEST.MF
skipping : resource CatalogManager.properties
resolving: class com/oracle/rest/Localhost_OOW_RESTCompany
skipping : class com/oracle/rest/Localhost_OOW_RESTCompany$1
skipping : class com/oracle/rest/Localhost_OOW_RESTCompany$Employee
resolving: class com/oracle/rest/restws
skipping : class com/oracle/rest/Localhost_OOW_RESTCompany$WebApplicationExceptionMessage
Classes Loaded: 5
Resources Loaded: 2
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 5 06:45:30 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Fri Aug 05 2016 06:45:27 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 
Package created.


Package body created.   SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
/u01/oracle/ojvmwcu_oow/demo/rest

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 5 06:45:30 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Fri Aug 05 2016 06:45:30 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> -----------------------
Response REST:
-----------------------
{"person":[{"address":"Paris","id":"11","name":"Maris"},{"address":"Dubai","id":"12","name":"Albert"},{"address":"New York","id":"13","name":"Kent"},{"address":"London","id":"14","name":"Adolf"},{"address":"New jersey","id":"15","name":"Tanusha"},{"address":"Mumbai","id":"16","name":"Alma"},{"address":"Delhi","id":"17","name":"Tino"},{"address":"San francisco","id":"18","name":"Suresh"},{"address":"London","id":"19","name":"Vijay"},{"address":"New jersey","id":"20","name":"Wade"}]}
-----------------------

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production.

  9. Observe the rest folder which is generated.

    $ ls                                                                                                                         demo.sql  run_REST_demo.sh  rest

SOAP based Web Services

You can use Oracle JVM Web Services Call-Out Utility to access SOAP based Web Services.

  1. Open a new terminal. Navigate to /u01/oracle/ojvmwcu_oow directory. Execute ls to see the contents inside this directory.

    cd /u01/oracle/ojvmwcu_oow

    $ cd /u01/oracle/ojvmwcu_oow/
$ ls
demo                jdk1.7.0_79         README
ojvmwcu             wadl-dist-1.1.6     glassfish3.2.2OLE6

  2. Navigate to demo/soap directory. Observe the files present in it.

    cd demo/soap

    $ cd demo/soap
$ ls
run_SOAP_demo.sh   demo.sql

  3. Open and examine the demo_soap.sql file. Notice that the SOAP web service is invoked and result is printed.

    cat demo.sql

     $ cat demo.sql 
                                 
set serveroutput on;
set linesize 1000;
declare 
response Person_soapws;
begin
response:=soapws.getEmployeeById('1');
dbms_output.put_line('-----------------------');
dbms_output.put_line('Response SOAP');
dbms_output.put_line('Id:' || response.arg1);
dbms_output.put_line('Name:' || response.arg2);
dbms_output.put_line('City:' || response.arg0);
dbms_output.put_line('-----------------------');
dbms_output.put_line('-----------------------');
end;
/

  4. Open and examine the run_SOAP_demo.sh file. Notice that the soapws_wrapper.sql file and demo_soap.sql file are invoked.

    cat run_SOAP_demo.sh

    $ cat run_SOAP_demo.sh 
#!/bin/sh
perl $ORACLE_HOME/javavm/ojvmwcu/bin/ojvmwcu.pl -wsdl "http://localhost:47483/OOW_SOAP/Employee?WSDL" -p com.oracle.ws -v -keepsrc -name soapws  -out soap;
cd soap;
#sed -i 's@HR@g' soapws_grant_permission.sql soapws_revoke_permission.sql;
#sqlplus sys/Welcome1 as sysdba <<EOF
#@ soapws_grant_permission.sql;  
#exit;
#EOF
loadjava -r -f -v -u hr/hr soapws.jar ;
sqlplus hr/hr <<EOF
@ soapws_wrapper.sql;
exit;
EOF
cd -;
sqlplus hr/hr <<EOF
@ demo.sql;
exit;
EOF

  5. Set the TWO_TASK variable.

    export TWO_TASK=pdb1;

  6. Run the run_SOAP_demo.sh file and observe the output.

    sh run_SOAP_demo.sh

    $ sh run_SOAP_demo.sh 
 [oracle@host01 soap]$ sh run_SOAP_demo.sh 
OjvmWCU : Using JAVA_HOME as /usr/java/jdk1.8.0_91
OjvmWCU INFO: Starting OjvmWCU
OjvmWCU INFO: Generating Client Stubs
OjvmWCU INFO: java -classpath /usr/java/jdk1.8.0_91/lib/tools.jar com.sun.tools.internal.ws.WsImport -d /u01/oracle/ojvmwcu_oow/demo/soap/tmp -s soap -p com.oracle.ws http://localhost:47483/OOW_SOAP/Employee?WSDL 
OjvmWCU INFO: JAX-WS Output
OjvmWCU INFO: parsing WSDL...
OjvmWCU INFO: 
OjvmWCU INFO: 
OjvmWCU INFO: 
OjvmWCU INFO: Generating code...
OjvmWCU INFO: 
OjvmWCU INFO: 
OjvmWCU INFO: Compiling code...
OjvmWCU INFO: 
OjvmWCU INFO: Finished JAX-WS
OjvmWCU INFO: Generating Wrappers
OjvmWCU INFO: Parsing Generated Class Files
OjvmWCU INFO: Generating Accessor Object
OjvmWCU INFO: Interpreting Webservice Operations
OjvmWCU INFO: Generating PLSQL Wrapper
OjvmWCU INFO: Processing Mehtod getEmployeeById
OjvmWCU INFO: Processing Mehtod getAllEmployees
OjvmWCU INFO: Generating Static Java Class
Note: /u01/oracle/ojvmwcu_oow/demo/soap/tmp/com/oracle/ws/soapws.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
OjvmWCU INFO: Generating Jar files
OjvmWCU INFO: Adding Entry - CatalogManager.properties
OjvmWCU INFO: Adding Entry - ObjectFactory.class
OjvmWCU INFO: Adding Entry - Employee.class
OjvmWCU INFO: Adding Entry - soapws.class
OjvmWCU INFO: Adding Entry - GetEmployeeByIdResponse.class
OjvmWCU INFO: Adding Entry - package-info.class
OjvmWCU INFO: Adding Entry - GetAllEmployeesResponse.class
OjvmWCU INFO: Adding Entry - Employee_Service.class
OjvmWCU INFO: Adding Entry - Person.class
OjvmWCU INFO: Adding Entry - GetAllEmployees.class
OjvmWCU INFO: Adding Entry - GetEmployeeById.class
OjvmWCU INFO: Done Executing OjvmWCU
arguments: '-u' 'hr/***' '-r' '-f' '-v' 'soapws.jar' 
creating : resource META-INF/MANIFEST.MF
loading  : resource META-INF/MANIFEST.MF
creating : resource CatalogManager.properties
loading  : resource CatalogManager.properties
creating : class com/oracle/ws/ObjectFactory
loading  : class com/oracle/ws/ObjectFactory
creating : class com/oracle/ws/Employee
loading  : class com/oracle/ws/Employee
creating : class com/oracle/ws/soapws
loading  : class com/oracle/ws/soapws
creating : class com/oracle/ws/GetEmployeeByIdResponse
loading  : class com/oracle/ws/GetEmployeeByIdResponse
creating : class com/oracle/ws/package-info
loading  : class com/oracle/ws/package-info
creating : class com/oracle/ws/GetAllEmployeesResponse
loading  : class com/oracle/ws/GetAllEmployeesResponse
creating : class com/oracle/ws/Employee_Service
loading  : class com/oracle/ws/Employee_Service
creating : class com/oracle/ws/Person
loading  : class com/oracle/ws/Person
creating : class com/oracle/ws/GetAllEmployees
loading  : class com/oracle/ws/GetAllEmployees
creating : class com/oracle/ws/GetEmployeeById
loading  : class com/oracle/ws/GetEmployeeById
skipping : resource META-INF/MANIFEST.MF
skipping : resource CatalogManager.properties
resolving: class com/oracle/ws/ObjectFactory
resolving: class com/oracle/ws/Employee
resolving: class com/oracle/ws/soapws
skipping : class com/oracle/ws/GetEmployeeByIdResponse
resolving: class com/oracle/ws/package-info
skipping : class com/oracle/ws/GetAllEmployeesResponse
skipping : class com/oracle/ws/Employee_Service
skipping : class com/oracle/ws/Person
skipping : class com/oracle/ws/GetAllEmployees
skipping : class com/oracle/ws/GetEmployeeById
Classes Loaded: 10
Resources Loaded: 2
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 9 06:32:55 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Tue Aug 09 2016 06:32:54 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> CREATE OR REPLACE TYPE Person_soapws AS OBJECT (
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents



Type created.


Package created.


Package body created.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
/u01/oracle/ojvmwcu_oow/demo/soap

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 9 06:32:55 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Tue Aug 09 2016 06:32:55 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> -----------------------
Response SOAP
Id:1
Name:John
City:London
-----------------------
-----------------------

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

  7. Observe the soap folder which is generated.

    $ ls                                                                                                                          demo.sql  run_SOAP_demo.sh  soap

Want to Learn More

In this tutorial, you have learned how to:

  • Work with Cloud Data Service with Java 8 Nashorn
  • Work with Oracle In-Database Container for Hadoop
  • Work with Oracle JVM Web Services Call-Out Utility

These tutorials will later be available on Oracle Learning Library.

Resources

More information about the topics covered is available at: