This is the second installment of ORDS as a mod_plsql replacement for PL/SQL web toolkit based applications. The part I was database side install, basically as a segregation of tasks that need to be performed by a DBA versus middle tier administrator. Click here for Part I.
2) Middle Tier Installation and Configuration
The middle tier part is the most important setup for ORDS. Here I would describe the configuration that I had done on ‘standalone’ (jetty server that comes with ords.war) mode but deploying the .war file on any of the middle tier server (Weblogic, Tomcat, Glassfish or IBM Websphere) should be straight forward.
Following few things that I had done on my local instance:
- ORDS install on standalone server with multiple database connection pools setup as optional
- Setup a self-signed certificate (optional)
- Configure ORDS plugins as a work-around for file uploads
- Sample code to test
- PL/SQL code to do GET
- Secure cookie
- Plugin test with demo-plugin.jar
Pre-Requisite:
The ORDS product installation uses java to perform the installation. Make sure that java development kit (JRE/JDK version 1.7 and above) is installed on the local machine from here this setup would be run.
ORDS install on Standalone Server
Essentially there are four simple steps to complete the configuration and make a simple test work. There are two directory locations that are important. One is the location where the “ords.war” is location which would be referred as installation directory. The second one is configuration directory where the configuration files are saved.
STEP 1:
Go to the location where the ords.war was downloaded [Here’s the link if needed to download the file]. My folder is under /u01/downthenet/orasw/ords309 and my os/unix username is oracle.
Step1.1 – Unzip ords software:
Unzip the ords.3.0.9.348.07.16.zip file to this directory.
$ pwd /u01/downthenet/orasw/ords309 $ unzip ../ords.3.0.9.348.07.16.zip
Edit the parameters file to enter as much details as needed to avoid having to type at the command prompt for each value during the installation. My database schema where the application code is compiled is appuser01.
Run an ORDS command to establish a directory on your file system where ORDS will store all its configuration information. To keep it simple and not work around the permission of different directories, please create the conf/ords under “oracle” user’s home as follows:
Step1.2 – Configuration directory:
Create a configuration directory where the ORDS config files would be saved.
[oracle@tmel7 ords309]$ mkdir –p /u01/app/ords
This directory should exist prior to running the configdir command, which is as follows:
[oracle@tmel7 ords309]$ java –jar ords.war configdir /u01/app/ords Jan 13, 2017 1:32:45 PM INFO: Set config.dir to /u01/app/ords/conf in: /u02/downthenet/orasw/ords309/ords.war [oracle@tmel7 ords309]$
Step1.3 – Core Installation:
If certain tablespace, port number, etc. needs to be changed/updated then open params/ords_params.properties file. Since the installation doesn’t need APEX product to be installed, we should open the parameters file and changed the db.username from APEX_PUBLIC_USER to APPUSER01 (or any application schema/user where the PL/SQL web application is installed).
$ [oracle@tmel7 ords309]$ vi params/ords_params.properties db.hostname=localhost db.port=1521 db.servicename=ORCL db.sid= db.username=appuser01 migrate.apex.rest=false rest.services.apex.add= rest.services.ords.add=true schema.tablespace.default=SYSAUX schema.tablespace.temp=TEMP standalone.https.port=8443 standalone.mode=true standalone.static.images=/u01/app/ords/static_images user.tablespace.default=USERS user.tablespace.temp=TEMP /u01/downthenet/orasw/ords309 $ [oracle@tmel7 ords309]$
The next step is to run the install command. Since most of the basic information was updated in the properties file, the installation prompts are mainly to enter passwords. See below for the values that had been entered. Remember, password for all schemas is “oracle” (without the quotes)
[oracle@tmel7 ords309]$ java –jar ords.war install
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: Confirm password: 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]:1 Enter the database password for APPUSER01: Confirm password: Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:2 Jan 13, 2017 2:31:40 PM INFO: Updated configurations: defaults, apex, apex_pu Installing Oracle REST Data Services schema to version 3.0.9.348.07.16 ... Log file written to /u02/downthenet/orasw/ords309/logs/ords_install_2017-01-13_143140_00752.log Completed install for Oracle REST Data Services version 3.0.9.348.07.16. Elapsed time: 00:00:21.188 Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2 [oracle@vbgeneric ords309]$
I’ve not started the “jetty” standalone server just yet. We would need to complete a couple more steps.
Step1.4 – Enable ORDS for the custom application schema:
Connect to the custom database schema/user using SQL. This is an important step to be done. I’m using the “sqlcl” tool. If you haven’t tried then you should give it a try , its sqlplus on steroids. The tool can be downloaded here from OTN.
[oracle@tmel7 ords309]$ sql APPUSER01@ORCL SQLcl: Release 4.2.0.16.153.2014 RC on Fri Jan 13 12:52:53 2017 Copyright (c) 1982, 2017, Oracle. All rights reserved. Password? (**********?) ****** Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL>exec ords.enable_schema; PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL>
Step1.5 – database connection pool:
Setup database (connection pool) for the custom PL/SQL application. If there are multiple applications or DADs defined in the same database, then you could setup multiple database connection pool.
Here, we will create one connection pool for “appuser01”.
[oracle@tmel7 ords309]$ java -jar ords.war setup –database appuser01 Enter the name of the database server [localhost]: localhost Enter the database listen port [1521]: 1521 Enter 1 to specify the database service name, or 2 to specify the database SID [1]: 1 Enter the database service name [ORCL]: ORCL Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:2 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]:1 Enter the PL/SQL Gateway database user name [APPUSER01]: APPUSER01 Enter the database password for APPUSER01: Confirm password: Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:2 Jan 13, 2017 3:09:58 PM INFO: Updated configurations: APPUSER01 [oracle@tmel7 ords309]$
In response to the scripted prompts, you give (or accept as defaults) the same machine name, listener port number, service name, etc. This time enter [2] when prompted to skip verifying/installing ORDS again. Then when prompted enter [1] to use the PL/SQL Gateway. Here is where you can enter the username (APPUSER01) as the gateway username, and also enter the password. Enter [2] to skip RESTful services again.
If you go to the config directory (/u01/app/ords/conf) you should see an XML file for this pool in the conf sub-directory, e.g.conf/appuser01.xml and appuser01_pu.xml
Step1.6 – Multiple database pool connections (optional step):
When there are applications running under different schemas (for different DAD), then we need to create multiple database pool connections. The simplest way is to repeat the previous step with the second database user (for e.g. appuser01).
[oracle@tmel7 ords309]$ java -jar ords.war setup –database appuser02
To get the ORDS_PUBLIC_USER for this database user, simply copy the previously created xml file (appuser01_pu.xml) and rename them to the new schema. i.e. appuser02_pu.xml. The username and password remains the same (ORDS_PUBLIC_USER and hashed value password).
I have 3 database pool connections in my configuration and works just fine. In essence, I have 3 dbpooluser .xml files and 3 dbpooluser_pu.xml files.
$ls -l /u01/app/ords/conf -rw-r--r--. 1 oracle 344 Jan 13 08:57 appuser03_pu.xml -rw-r--r--. 1 oracle 344 Jan 13 08:57 appuser03.xml -rw-r--r--. 1 oracle 349 Jan 13 08:55 appuser02.xml -rw-r--r--. 1 oracle 341 Jan 13 08:55 appuser02_pu.xml -rw-r--r--. 1 oracle 344 Jan 13 08:50 appuser01_pu.xml -rw-r--r--. 1 oracle 344 Jan 13 08:50 appuser01.xml oracle-/u01/app/ords/conf
Step1.7 – Create URL mapping for custom application:
This is part two of the two-part setup for the equivalent of a modplsql DAD. Here’s what I did:
[oracle@tmel7 ords309]$ java -jar ords.war map-url –type base-path /tmmapp1 appuser01 Jan 13, 2017 3:17:16 PM INFO: Creating new mapping from: [uri-pattern,–type] to map to: [base-path,,] [oracle@tmel7 ords309]$
What this does is create a URL mapping, which should now show up in the configuration directory in file /u01/app/ords/url-mapping.xml. The trailing string “appuser01” is the schema name of the database to which this path (/tmmapp1) is bound. There is another file (/u01/app/ords/default.xml) created with core parameter values.
Step1.8 – ORDS debugging parameter (to be used in non-production environment):
In the configuration directory, edit defaults.xml file to enable debugging. Change the following two lines from false to true:
<entry key="debug.debugger">true</entry> <entry key="debug.printDebugToScreen">true</entry>
STEP 2
Install self-signed certificate (optional):
Its quite simple to create a self-signed certificate using the java keytool. Here’s the simple writeup from SSL shopper. https://www.sslshopper.com/article-how-to-create-a-self-signed-certificate-using-java-keytool.html
Copy the .jks and .key files under “standalone” directory. Since, we’ll run ORDS in standalone mode, jetty should be able to recognize the certificate.
$ ls -l /u01/app/ords/standlone -rw-r--r--. 1 oracle 344 Jan 13 12:57 autoder.key -rw-r--r--. 1 oracle 344 Jan 13 12:57 autokeystore.jks -rw-r--r--. 1 oracle 349 Jan 13 12:58 standaline.properties oracle-/u01/app/ords/conf
Now, we should be able to access the URL via https (8443) as they had been configured in the standalone server parameters.
STEP 3
There is a limitation with current ORDS 3.0.9 version in which there is no native support to upload files something similar in mod_plsql dad configuration value PlsqlDocumentProcedure. The only other option that I could find was to be able to use ORDS plugins to write your own java code. The documentation to load/test a simple ORDS plugin worked but it wasn’t simple initially. I had to experiment a couple of settings to really make this work.
A couple of important things to make sure for plugin to work.
- ORDS_PUBLIC_USER should be setup correctly with correct database pool .xml file in the configuration directory. (for e.g. appuser01_pu.xml)
- The underlying / core database schema user should be enabled to use ORDS. This step is clearly mentioned in the documents but I’ll explain how it works based on the value set in USER_ORDS_SCHEMAS (user view). Read Colm Divilly’s (part of ORDS product development team) blog about what exactly enabling schema means here.
Please check the Oracle documentation for ORDS plugin here especially from prepare environment. The sample that is given returns the schema/database user name. I’m not going to repeat how to build the .jar file as the instructions are quite clear on the documentation.
Once the .jar is created (plugin-demo.jar), add that to the “ords.war” file then start the ORDS in standalone mode.
$ ls -l /u02/downthenet/orasw/ords309 total 48328 drwxr-xr-x. 3 oracle 20 Dec 13 07:16 docs/ drwxr-xr-x. 6 oracle 81 Dec 13 07:21 examples/ drwxr-xr-x. 2 oracle 53 Feb 17 14:14 logs/ -rw-r--r--. 1 oracle 49442151 Feb 17 14:13 ords.war drwxr-xr-x. 2 oracle 35 Feb 21 14:02 params/ -rw-r--r--. 1 oracle 43605 Dec 13 07:21 readme.html oracle-/u02/downthenet/orasw/ $
$ java -jar /u02/downthenet/orasw/ords309/ords.war standalone
The most difficulty I faced was to test this plugin from the browser. When I tried to test as mentioned in the document, was getting 404 error,
The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured.
But I was sure that I had enabled the schema for ORDS and the value was set correctly in the user view by logging into APPUSER01 schema and ran the following query,
SQL> select parsing_schema, type, pattern, status from user_ords_schemas; PARSING_SC TYPE PATTERN STATUS --------- ---------- ---------- ---------- APPUSER01 BASE_PATH app01_plugin ENABLED
While experimenting with few setup options, what I did was to create a new URL mapping to define my own URL pattern using the following command.
$ java -jar ords.war map-url --type uri-template /app01_plugin appuser01
What it does is, it creates a new/additional URL mapping to serve ORDS plugin with the pattern “appo1_plugin” but use APPUSER01 database pool connection. Now when I checked the the url-mapping.xml file (under ORDS configuration directory), I see 2 lines. One for base-path for the PL/SQL applications and one for the Java/ORDS plugin.
$vi /u01/app/ords/url-mapping.xml
<?xml version="1.0" encoding="UTF-8"?> <pool-config xmlns="http://xmlns.oracle.com/apex/pool-config"> <pool name="appuser01" base-path="/appuser01" updated="2017-01-18T16:17:27.523Z"/> <pool name="appuser01" uri-pattern="/app01_plugin" updated="2017-01-18T16:17:27.523Z"/> <pool name="appuser02" base-path="/appuser02" updated="2017-01-18T16:17:27.523Z"/> <pool name="appuser03" base-path="/appuser03" updated="2017-01-18T16:17:27.523Z"/> </pool-config>
Once the setting is complete, need to stop and restart ORDS standalone server. Tested to see if the plugin would return the value using the following URL,
https://localhost:8443/ords/app01_plugin/demos/plugin?who=Test-Plugin
STEP 4:
The final step would be create a simple PL/SQL code to test if the setup really works for non-APEX PL/SQL application.
Login to appuser01 database schema and compile a sample “ords_test” package.
CREATE OR REPLACE PACKAGE ORDS_TEST
IS
PROCEDURE flexible_param1
(
name_array in owa_util.vc_arr,
value_array in owa_util.vc_arr
);
PROCEDURE fixed_param1
(
pName in varchar2,
pId in number default 0
);
PROCEDURE get_cookie;
PROCEDURE set_cookie;
END ORDS_TEST1;
/
show error
CREATE OR REPLACE PACKAGE BODY ORDS_TEST
IS
PROCEDURE flexible_param1
(
name_array in owa_util.vc_arr,
value_array in owa_util.vc_arr
)
IS
BEGIN
for vCnt in 1..name_array.count loop
htp.p('name - '|| name_array(vCnt) ||' value - '||value_array(vCnt)||'
');
end loop;
END flexible_param1;
PROCEDURE fixed_param1
(
pName in varchar2,
pId in number default 0
)
IS
BEGIN
htp.p('name: '||pName||' id: '||to_char(pId));
END fixed_param1;
PROCEDURE set_cookie
IS
vSessionId VARCHAR2(4000) := substr(replace(replace(utl_raw.cast_to_varchar2(utl_encode.base64_encode( dbms_crypto.randombytes(120))),CHR(13),NULL),CHR(10),NULL),1,150 );
BEGIN
owa_util.mime_header( 'text/html', FALSE );
-- Added Secure and HttpOnly to only allow cookie over HTTPS and to prevent Javascript retrieval of Session Cookie
htp.print('Set-Cookie: SESSION_ID='||vSessionId||'; path=/; HttpOnly; Secure'||CHR(10));
owa_util.http_header_close;
END set_cookie;
PROCEDURE get_cookie
IS
vCookie owa_cookie.cookie;
BEGIN
vCookie := owa_cookie.get('SESSION_ID');
IF vCookie.num_vals > 0 then
htp.prn('SESSION_ID - '|| vCookie.vals(1));
ELSE
htp.prn('SESSION_ID - Not Set');
END IF;
END get_cookie;
END ORDS_TEST;
/
show error
After the above sample package/snippet is compiled successfully, try the following URL to test based on the setup and configuration we had done so far.
- To test flexible parameter, try the following URL:
https://locahost:8443/ords/appuser01/!ords_test.flexible_param1?pName=TMM&pNum=1234
Note: the ‘ ! ‘ for testing the flexible parameter procedure
2. To test fixed parameter, try the following URL:
https://locahost:8443/ords/appuser01/ords_test.fixed_param1?pName=TMMfixed&pId=1234
3. To test secure cookies work, try the following 2 URLS. First set the cookie value and then get the value,
https://locahost:8443/ords/appuser01/ords_test.set_cookie
https://locahost:8443/ords/appuser01/ords_test.set_cookie
Hope this helps someone who is looking to use ORDS as a replacement for mod_plsql in non-APEX environment.
If there is anything you’d like to add or comment please do so. I’m ready to learn if there is a different take on this one. Thanks to my colleague, google, discussion forum, blogs for all the knowledge and information.
tajsblog 