After many years of working in APEX environment, recently I had been tasked to provide a solution to replace pure PL/SQL Web Toolkit application using mod_plsql, on Oracle HTTP Server (OHS), which had been working extremely well for more than a decade.

As outlined in the Oracle Middleware documentation, OHS  – mod_plsql will not be included from 12.2 OHS server, there is a real need to replace this technology piece with Oracle recommended ORDS (Oracle Rest Data Services) product.

This write-up is a simple “proof of concept” to make a mod_plsql based application work using ORDS in a Non-APEX environment. I’m splitting this writeup into a two-part based on the teams performing tasks. The first part would consist of Database Install which would require “SYSDBA” privilege. The second part would be Middle-Tier Install and config that would need application server access but “no SYSDBA” privilege.

1. Database Install Steps
2. Middleware Install and Configure Steps

What is ORDS?
Oracle Rest Data Services listener is a J2EE container that serves as a PL/SQL Gateway for PL/SQL web toolkit applications and presents in HTML format to a web browser. It functions very similar to the Apache’s PL/SQL module (mod_plsql) but is java based container.

It runs on a java enabled web application server like OFM (Oracle Fusion Middleware, a.k.a. weblogic), Tomcat, Glassfish. Even though there is no official support issued to IBM Websphere, I don’t see any reason why this wouldn’t work on that.

More details available from Oracle ORDS documentation here.

Comparison of mod_plsql v/s ORDS
A quick stab at comparing these products to server as “PL/SQL gateway”.

MOD_PLSQL	ORDS
Apache Module	Java based container
Work on OHS server	Needs Java enabled web server
deprecated in OHS 12.1.3 and not available in OHS 12.2 version	recommended product as replacement for mod_plsql

Pre-Requisite / Assumption
A few assumptions as part of this POC,

• Oracle database 12c version is installed
• Have access to SYSDBA account
• Java enabled application server (Weblogic, Tomcat, Glassfish) or Standalone
  [ORDS provides a standalone “jetty” server for test]

Two Part Installation:
In the first part, the following steps would be performed by a database administrator who has SYSDBA privilege.

1) Database installation (for a DBA to perform)
As part of ORDS product installation, two database schemas are created with few database objects.

1. ORDS_PUBLIC_SCHEMA
2. ORDS_METADATA

After the installation, ORDS_METADATA schema/user’s account is “expired and locked” and ORDS_PUBLIC_SCHEMA account would be “open”. If there is a password reset policy is set then possibly, ORDS_PUBLIC_SCHEMA user must be in the pool that does not expire or create a profile for that exception.

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 where this setup would be run.

Step1:
Download the latest version (version# 3.0.9 as of this writing) ords.war file into local machine or database server (/tmp or C:\Temp). That would be the installation directory.

Step2:
When performing the installation using a Unix machine, then execute the following command,

$> $JAVA_HOME/bin/java -jar ords.war schema

When performing from Windows, you must have the Java.exe executable in your PATH, and then you can execute the same command at the C:\ prompt. The installation process will prompt for database server and instance information and also SYSDBA privilege user (e.g. SYS). At each prompt, enter the response which are indicated in brown. For standard database install, leave the default values.

$> $JAVA_HOME/bin/java -jar ords.war schema
 Enter the name of the database server [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: 
 Please login with SYSDBA privileges to verify Oracle REST Data Services schema.
 Enter the username with SYSDBA privileges to verify the installation [SYS]: SYS (or user with SYSDBA privs)
 Enter the database password for SYS: **********************
 Confirm password: **********************
 Enter the default tablespace for ORDS_METADATA [SYSAUX]:
 Enter the temporary tablespace for ORDS_METADATA [TEMP]:
 Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:
 Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:
 Enter the database password for ORDS_PUBLIC_USER: **********************
 Confirm password: **********************

Installing Oracle REST Data Services version 3.0.9.348.07.16

... Log file written to /downthenet/orasw/ords309/logs/ords_install_core_2017-01-17_145514_00560.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 /downthenet/orasw/ords309/logs/ords_install_datamodel_2017-01-17_145539_00789.log

Completed installation for Oracle REST Data Services version 3.0.9.348.07.16. Elapsed time: 00:00:27.53

The ORDS database side of installation is complete.

Step 3:
The next step would be to enable the schema for ORDS. Login to the database schema where PL/SQL web application is compiled and run the following command.

begin
  ords.enable_schema;
commit;
end;
/

What this does internally, is to allow ORDS_PUBLIC_USER schema as a proxy user to run the code compiled in the PL/SQL web application schema. For e.g. if the db schema is “tmm_user” then it internally allows,

alter user tmm_user grant connect through ords_public_user;

Step 4 (Optional):
The following step (deprecated now) can be used to extract all the files that are used during the installation process.  This step is not required but can be used to understand what is occurring during the installation process.

$> $JAVA_HOME/bin/java -jar ords.war ords-scripts --scriptdir /tmp

The above step will extract all of the SQL scripts into the /tmp/script directory that are used by the installer.  The install folder has all of the scripts

References
An excellent article written by Doug McMahon was used to refer. You should read some Gotcha(s) he had mentioned in the article.
https://oss.oracle.com/projects/mod_owa/dist/documentation/ords_setup.htm