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.