Hello friends,
This is a configuration of ORDS as a mod_plsql replacement for PL/SQL web toolkit based applications. Before that I've installed the database side, primarily as segregation of tasks that need to be performed by a DBA versus middle tier administrator.
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 (Glassfish ,Weblogic, Tomcat, or IBM Websphere) should be straightforward.
Following few things that I had done on my local instance:
ORDS install on the standalone server with multiple database connection pools setup as optional
Setup a self-signed certificate (optional)
Configure ORDS plugins as a workaround 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
Mainly there are four simple steps to complete the configuration and make a simple test work. Two directory locations are essential. One is the location where the “ords.war” is the location which would be referred to as installation directory. The second one is the 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/thiagoleoncio, 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/thiagoleoncio
$ unzip ../ords.3.0.9.348.07.16.zip
Edit the parameters file to enter as many 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 thiagoappuser01.
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@tgui thiagoleoncio]$ mkdir –p /u01/app/ords
This directory should exist before running the configdir command, which is as follows:
[oracle@tgui thiagoleoncio]$ java –jar ords.war configdir /u01/app/ords
INFO: Set config.dir to /u01/app/ords/conf in: /u02/downthenet/orasw/thiagoleoncio/ords.war
[oracle@tgui thiagoleoncio]$
Step1.3 – Core Installation:
If specific tablespace, port number, etc. need to be changed/updated then open params/ords_params.properties file. Since the installation doesn’t require APEX product to be installed, we should open the parameters file and changed the db.username from APEX_PUBLIC_USER to thiagoappuser01 (or any application schema/user where the PL/SQL web application is installed).
$ [oracle@tgui thiagoleoncio]$ vi params/ords_params.properties
db.hostname=localhost
db.port=1521
db.servicename=ORCL
db.sid=
db.username=thiagoappuser01
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/thiagoleoncio
$ [oracle@tgui thiagoleoncio]$
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@tgui thiagoleoncio]$ 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 thiagoappuser01:
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 17, 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 is written to /u02/downthenet/orasw/thiagoleoncio/logs/ords_install_2018-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 one if you wish to start in standalone mode or 2 to exit [1]:2
[oracle@vbgeneric thiagoleoncio]$
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@tgui thiagoleoncio]$ sql thiagoappuser01@ORCL
SQLcl: Release 4.2.0.16.153.2014 RC on Fri Jan 17 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 set up multiple database connection pools.
Here, we will create one connection pool for “
thiagoappuser01”.
[oracle@tgui thiagoleoncio]$ java -jar ords.war setup –database thiagoappuser01
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 determine the database SID [1]: 1
Enter the database service name [ORCL]: ORCL
Enter one if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:2
Enter one if you're going 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 username [thiagoappuser01]: thiagoappuser01
Enter the database password for thiagoappuser01:
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 17, 2018 3:08:59 PM
INFO: Updated configurations: thiagoappuser01
[oracle@tgui thiagoleoncio]$
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 (thiagoappuser01) 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/thiagoappuser01.xml, and thiagoappuser01_pu.xml
Step1.6 – Multiple database pool connections (optional step):
When applications are 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 (e.g., thiagoappuser01).
[oracle@tgui thiagoleoncio]$ java -jar ords.war setup –database appuser02
To get the ORDS_PUBLIC_USER for this database user, copy the previously created xml file (thiagoappuser01_pu.xml) and rename them to the new schema. i.e., appuser02_pu.xml. The username and password remain the same (ORDS_PUBLIC_USER and hashed value password).
I have three database pool connections in my configuration and works just fine. In essence, I have three dbpooluser .xml files and three dbpooluser_pu.xml files.
$ls -l /u01/app/ords/conf
-rw-r--r--. 1 oracle 344 Jan 17 08:57 appuser03_pu.xml
-rw-r--r--. 1 oracle 344 Jan 17 08:57 appuser03.xml
-rw-r--r--. 1 oracle 349 Jan 17 08:55 appuser02.xml
-rw-r--r--. 1 oracle 341 Jan 17 08:55 appuser02_pu.xml
-rw-r--r--. 1 oracle 344 Jan 17 08:50 thiagoappuser01_pu.xml
-rw-r--r--. 1 oracle 344 Jan 17 08:50 thiagoappuser01.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@tgui thiagoleoncio]$ java -jar ords.war map-url –type base-path /tmmapp1 thiagoappuser01
Jan 17, 2017 3:17:16 PM
INFO: Creating new mapping from: [uri-pattern,–type] to map to: [base-path,,]
[oracle@tgui thiagoleoncio]$
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 “thiagoappuser01” 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):
It's quite simple to create a self-signed certificate using the java keytool. Here’s the simple write-up 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, the jetty should be able to recognize the certificate.
$ ls -l /u01/app/ords/standlone
-rw-r--r--. 1 oracle 344 Jan 17 12:57 autoder.key
-rw-r--r--. 1 oracle 344 Jan 17 12:57 autokeystore.jks
-rw-r--r--. 1 oracle 349 Jan 17 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 Java code. The documentation to load/test a simple ORDS plugin worked but it wasn’t simple initially. I had to experiment with a couple of settings to make this work.
A couple of important things to make sure for the plugin to work.
ORDS_PUBLIC_USER should be set up correctly with correct database pool .xml file in the configuration directory. (e.g., thiagoappuser01_pu.xml)
The underlying / core database schema user should be enabled to use ORDS. This step is 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 precisely 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 username. 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/thiagoleoncio
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/thiagoleoncio/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.
However, I was sure that I had enabled the schema for ORDS, and the value was set correctly in the user view by logging into thiagoappuser01 schema and ran the following query,
SQL> select parsing_schema, type, pattern, status from user_ords_schemas;
PARSING_SC TYPE PATTERN STATUS
--------- ---------- ---------- ----------
thiagoappuser01 BASE_PATH app01_plugin ENABLED
While experimenting with few setup options, what I did was to create a new URL mapping to define my URL pattern using the following command.
$ java -jar ords.war map-url --type uri-template /app01_plugin thiagoappuser01
What it does is, it creates a new/additional URL mapping to serve ORDS plugin with the pattern “appo1_plugin” but use thiagoappuser01 database pool connection. Now when I checked the the url-mapping.xml file (under ORDS configuration directory), I see two 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="thiagoappuser01" base-path="/thiagoappuser01" updated="2017-01-18T16:17:27.523Z"/>
<pool name="thiagoappuser01" 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 to create a simple PL/SQL code to test if the setup works for non-APEX PL/SQL application.
Login to thiagoappuser01 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-CookieSSION_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_IDot 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 the adjustable parameter, try the following URL:
https://locahost:8443/ords/thiagoappuser01/!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/thiagoappuser01/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/thiagoappuser01/ords_test.set_cookie
https://locahost:8443/ords/thiagoappuser01/ords_test.set_cookie
Happy coding! Hope this helps someone who is looking to use ORDS as a replacement for mod_plsql in a non-APEX environment.
