Thursday, March 02, 2017

What's in Oracle Database 12c Release 2 for Java & JavaScript Developers?

Here is the summary of New Java & JavaScript Features in Oracle Database 12c Release 2 on Cloud and on-Premise

  • Java 8: Java 8 in JDBC/UCP and OJVM; JDBC 4.2
  • JavaScript with Nashorn: JDBC/UCP, OJVM
  • Performance: JIT (OJVM), Network Compression over WAN (JDBC), Configurable connection health check frequency (UCP), PL/SQL Callback interface (JDBC)
  • Scalability: Shared Pool for Multi-Tenant Database (UCP), Shared Pool for Sharded database (UCP), Sharding Key APIs (JDBC, UCP), DRCP Proxy session sharing, DRCP support for  multiple labels
  • High-Availability: Java APIs for FAN events (SimpleFan.jar), Planned Maintenance in the driver (JDBC), Application Continuity for XA Datasources, Transaction Guard for XA Datasource
  • Security: SSL v1.2 / TLS v 1.2 (JDBC)
  • Manageability: XMLconfiguration (UCP), Enable/disable/suspend/resume feature level logging (JDBC), MAX_THINK_TIME for Transactions in progress (DRCP), new statistics view and AWR reports  
  • Ease of Use : Web Services Callout (OJCM), Long Identifiers (OJVM), PL/SQL Boolean (JDBC), Debugger for OJVM (Java Debug Wire Protocol)
See our latest white paper for more details.

Friday, December 02, 2016

My Talks/Sessions at UKOUG Tech16

Hi guys,

If you plan to attend UKOUG Tech16, please check out my talks.sessions

  1.  Sunday 12/04 16:10  New JDBC & UCP Perf, Scalability & HA features in Oracle Database 12cR2 @
  2. Monday 12/05 17:55 A RESTful MicroService for JSON Processing in Oracle Database 12c R2 @  
  3. Wednesday 12/07 8:50 Hadoop, Spark & Flink Explained to Oracle DBAs & why They Should Care @ https://t.co/7CGLchAYAF
  4. Wednesday 12/07 15:10 Integrate BigData with Master Data: Oracle database table as Hadoop Datasource @
See you there



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

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.