Adventures with Apex, part three: Setting up Apex with Tomcat and the DBPrism plugin

It is common knowledge that you can run Apex using either the Apache-based Oracle HTTP Server (with mod_plsql) or the Embedded PL/SQL Gateway (on 10g XE and 11g), but did you also know that there is a third alternative?

The DBPrism project is a free, open-source plugin for the Tomcat web server that, among other things, can function as a replacement for mod_plsql.

Side Note 1: Interestingly, Oracle itself is also developing the "Apex Listener", what I assume is based on similar [Java] technology. So by Apex 4.0 we will have even more deployment choices.

Side Note 2: My wish is that someone familiar with Microsoft's IIS should write a plugin to mimic mod_plsql on that platform as well, to make it easier to start using Apex in companies that have standardized on Microsoft technology (sneaking it in the back door, so to speak :-). UPDATE (AUGUST 2009): I decided to implement a free, open-source PL/SQL gateway for IIS myself. The Thoth Gateway runs all mod_plsql applications (including Apex applications) on Microsoft Internet Information Server (IIS) 6.0 or later.

Anyway, here are instructions for using Apex with Tomcat and DBPrism:

ON THE DATABASE SERVER

1. Download the latest version of Application Express from oracle.com

- http://www.oracle.com/technology/products/database/application_express/download.html

2. Install Apex into the database according to the instructions

- do not worry about configuring either the Embedded PL/SQL Gateway (DBMS_EPG) or Oracle HTTP Server,
- but make sure to unlock the apex_public_user schema and make a note of the password given to apex_public_user (you will need it to configure the JDBC connection on the web server)


ON THE WEB SERVER

Note: This could be the same physical machine as the database, if desired.

1. Download Java Runtime Engine (JRE) from sun.com

- install by running setup program
- create an environment variable called JRE_HOME and set it to the location where JRE was installed

2. Download Tomcat from tomcat.apache.org

- unzip to a folder, for example c:\program files\apache-tomcat-xxx (where xxx is version number)
- follow instructions in RUNNING.txt to start Tomcat and verify installation (http://localhost:8080)

3. Download the Oracle JDBC Thin driver from Oracle.com

- http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_111060.html
- get the "ojdbc5.jar" file

4. Download DBPrism from http://sourceforge.net/projects/dbprism/

- choose the "dbprism" package (not the "cms" package)
- unzip and get the "dpls.war" file
- put the dpls.war file into the "webapps" folder in Tomcat
- shutdown and restart Tomcat
- this will create a "dpls" folder under "webapps"
- put the "ojdbc5.jar" file from step 3 in the dpls\WEB-INF\lib folder
- edit the prism.xconf file
- under "variables", change the value of the "demo.db" to a valid jdbc connectionstring to the database
- under under category named "DAD_apex", change the "dbpassword" to the correct password of the "apex_public_user" (note that this was set during/after the installation of Apex)

- shutdown and restart Tomcat again

5. Configure Apex images/javascript for Tomcat

- Create a folder called "i" under "webapps" in Tomcat
- shutdown and restart Tomcat
- Extract the files in the "images" folder in the Apex installation zip file and copy it to the "i" folder

6. Verify that everything works

- Go to http://localhost:8080/dpls/apex (this should display the login page of the Apex environment)


Pretty cool, isn't it? :-)