I'll make no apologies for it, but I'm a Unix guy. I used Windows many (many!) years ago, but these days I predominantly use Mac and Linux (or other Unixes), so when it comes to configuring our databases and web servers we typically use Linux (Oracle Enterprise Linux) or Solaris. So in this section I'll cover installing and configuring via Linux. The installation for Windows will obviously be different but is well documented. The configuration side of things is very similar on any platform since it is done via the Apache configuration files.

You can download the OHS from all sorts of locations on the Oracle OTN (Oracle Technology Network) website, depending on your exact database release. For our purposes, I am using this (currently working!) URL:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

If you cannot find it from that location, then the main OTN download page should help:

http://www.oracle.com/technetwork/indexes/downloads/index.html

Note that, depending on which release you are looking for, the OHS Apache download is sometimes distributed in the companion CD for the database download.

From that page you should be able to find the download for the Oracle HTTP Server called Oracle HTTP Server (Apache 2.0) (10.1.3.3.0) for Linux x86. Please note, however, that at the time of this writing I've found that the downloads of files on OTN are constantly moving and being reorganized, so by the time you read this it may be in a different location (in which case, my first resort would be to use Google—or your preferred search engine—to search for the download and get a direct link to the location).

Once you have downloaded the file to your (Linux!) server you can unzip it and begin the installation, as shown in Listing 1-1.

[ohs@localhost downloads]$ ls -al
total 326972
drwxrwxr-x 6 ohs ohs      4096 Feb 12 10:36 .
drwx------ 4 ohs ohs      4096 Feb 12 10:29 ..
drwxr-xr-x 3 ohs ohs      4096 Apr 20  2007 access
-rw-r--r-- 1 ohs ohs 334457994 Feb 12 10:28 as_101330_apache2_lnx.zip
drwxr-xr-x 6 ohs ohs      4096 Apr 20  2007 doc
drwxr-xr-x 5 ohs ohs      4096 Apr 20  2007 install
-rwxr-xr-x 1 ohs ohs      1280 Apr 20  2007 runInstaller
drwxr-xr-x 9 ohs ohs      4096 May  2  2007 stage

The installation process is well documented and common to many of the pieces of Oracle software; usually you just need to configure any necessary operating system parameters and configure your environment (for example setting ORACLE_HOME). Rather than reproduce all that information here, it's (always!) best to refer to the installation document for the exact version you downloaded, since some of the requirements (and parameter settings) do vary from release to release.

Once you have executed the runInstaller script, the Installation wizard should start up, as shown in Figure 1-3.

Figure 1-3. OHS Installation wizard

Now it is a case of following the wizard and either filling out the required information or checking that it is correct, as shown in Figure 1-4 (if you explicitly set the options via your environment settings, for example, the ORACLE_HOME).

Figure 1-4. Defining the OHS name and path

When installing in a Linux/Unix environment, I typically create a separate Unix user just for the OHS. I never (not "almost never") install the OHS as the same Unix user the Oracle database software itself is running as. The reasons for this are twofold:

The above two reasons are, of course, not guarantees; however, by using a separate Unix user to install the OHS you provide a degree of separation and isolation between the database software and the OHS software. This, however, raises an interesting side question:

Should you install the OHS on the same machine as the database?

The answer is of course—as always—"it depends". If you only have a single machine to use, then you have no choice but to install them on the same machine. If it's a development or test environment, you might wish to install them on the same machine just to minimize your hardware overheads. In production, however, you might prefer complete hardware abstraction between the database and the web server (obviously there needs to be a network route between them, though).

This opens another can-of-potential-worms in terms of licensing implications. Now, as a disclaimer, I'm not an expert on Oracle licensing. In fact I'm not even going to pretend I understand the intricacies of licensing; on that subject I always defer to the professionals. That said, I've been using Oracle Application Express long enough to come across a "quirk" of licensing that you might not be aware of. The standalone Oracle HTTP Server (OHS) is covered by the database license, so if you install it on the same machine as the database, there is no additional license necessary. If, however, you install it on another (or additional) machine(s) then it will/could require additional licensing. In summary:

I have searched (and searched and searched!) for a snippet of text on the Oracle website for something succinct that I could point you to that explicitly states this; as with most things having to do with licensing, that hasn't been easy to find. So what I have just written should be viewed as my interpretation rather than absolute fact. It is not, however, just my opinion; I have spoken with many people inside Oracle to try and clarify this matter, and have correspondence to the effect that it is indeed the correct interpretation.

That said, your licensing is your responsibility, so please (please!) make sure to speak to your Oracle licensing expert to ensure you are correctly licensed!

So now we have the tricky aspect of licensing out of the way, we will assume for now that you've made sure that you're correctly licensed for the type of installation you went for (same machine or different machine). Once the installation is complete, you should see a summary screen similar to the one shown in Figure 1-5.

Figure 1-5. Completing the Installation wizard

Beside the issues of licensing, there is another issue that might affect whether you install the OHS on the same machine or a different machine than the database software. That issue is network latency. When the OHS is on the same machine as the database software, the network connection from the OHS to the database (via the mod_plsql handler) is within the same machine so there is very little network overhead. When you install the OHS on a machine other than the machine the database software is installed on, there is an additional—perhaps negligible, but still measurable—network overhead involved in the transmission of data between the two machines. While this overhead might be small, it does still all add up; as you increase the number of end users of your application(s), the overhead might become more and more noticeable, depending on your infrastructure.

If I had to summarize the two options, I'd say the advantages to installing on the same machine are as follows:

Disadvantages to installing on the same machine are

Advantages to installing on different machines are

Disadvantages to installing on different machines are

Okay, so now the OHS is installed (after you followed the Installation wizard), what now? Well, assuming you already have your database and Oracle APEX installed, you need to configure the OHS to be able to connect to your database, which we will cover in the next section.

Since the OHS is based on the Apache HTTP Server, all of the configuration is done via configuration files; there is no native GUI interface to the configuration, although there are various third-party tools that will make this easier for you, such as ApacheConf (http://www.apache-gui.com) and WebMin (http://www.webmin.com/).

As I mentioned, I'm a Unix guy and I think there's no better way to learn the different configuration options available than by looking at the configuration files manually, although I have been known to use GUIs from time to time (usually when I forget exactly what file a particular option is in).

So the first thing you need to do is configure the mod_plsql handler to connect to your database. You can do this by editing the DADS.CONF file (named after Database Access Descriptor) which is located in $ORACLE_HOME/ohs/modplsql/conf (where $ORACLE_HOME represents the directory you installed the OHS into).

Listing 1-2 shows the typical contents of a basic DADS.CONF file.

# ============================================================================
#                     mod_plsql DAD Configuration File
# ============================================================================
# 1. Please refer to dads.README for a description of this file
# ============================================================================

# Note: This file should typically be included in your plsql.conf file with
# the "include" directive.

# Hint: You can look at some sample DADs in the dads.README file

# ============================================================================
Alias /i/ "/home/ohs/apex4/images/"

<Location /pls/apex>
Order                       deny,allow
PlsqlDocumentPath           docs
AllowOverride               None
PlsqlDocumentProcedure      wwv_flow_file_mgr.process_download
PlsqlDatabaseConnectString  localhost:1521:dbtest ServiceNameFormat
PlsqlNLSLanguage            AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode     Basic
SetHandler                  pls_handler
PlsqlDocumentTablename      wwv_flow_file_objects$
PlsqlDatabaseUsername       APEX_PUBLIC_USER

PlsqlDefaultPage            apex
PlsqlDatabasePassword       <<your password here>>
Allow from all
</Location>

There are a few things to note here; first the line:

Alias /i/ "/home/ohs/apex4/images/"

This specifies the file system location where any files referenced by the location /i/ reside. So, for example, if the user references a file via the URL such as

http://yourserver/i/logo.jpg

the web server will try to return the file /home/ohs/apex4/images/logo.jpg (and obviously the request will fail if that file is not present in that location—assuming there are no other rewrite rules in effect).

For this alias you should specify the file server directory that you downloaded the APEX installation files into (so that it points to the location of the images subdirectory). This is covered in the Oracle Application Express installation guide.

While we're discussing the /i/ alias, it's worth noting that many people choose to locate their own custom files (for example JavaScript, CSS, and images) in the same file system directory (or a subdirectory thereof). The key reason they do this is that it means that there are no other web server configuration changes required (and therefore the web server does not need to be restarted). However, this is actually quite a bad idea because it means when you upgrade your version of APEX you might overwrite the directory with the new files from the Oracle APEX installation and therefore lose your own custom files.

It is far better (in my opinion, at least) to have specific directories for your own custom files, which means you can upgrade APEX without affecting your own files. I'll show some examples of how you can achieve this using the Apache virtual hosts feature to give a very flexible environment that supports multiple APEX applications yet maintains a degree of separation between them.

The next line in the DADS.CONF file worth discussing is the Location directive itself:

<Location /pls/apex>

This directive determines how users will access your APEX applications; in this case we are using the default (and actually I find very few reasons to change this), so users will access your application using a link like

http://yourserver/pls/apex/f?p=1000:1

to access page 1 in application 1000.

One reason you might wish to change this is if you have a single OHS that needs to point to different databases (perhaps running different versions of APEX). In this scenario you might have a DADS.CONF similar to

<Location /pls/apex>
  ...
</Location>

<Location /pls/apex32>
  ...
</Location>

<Location /pls/apex_test>
  ...
</Location>

This configuration allows you to specify different settings for each of the Location directives, so that you can, for example. access your current environment using /pls/apex, an old APEX3.2 environment using /pls/apex32, and a separate test environment using the database access descriptor (DAD) called /pls/apex_test.

The next line in the configuration:

PlsqlDatabaseConnectString     localhost:1521:dbtest ServiceNameFormat

details how the mod_plsql handler will connect to the database. In this case, since the OHS is installed on the same machine as the database, it connects to localhost (i.e., the local machine), and connects to dbtest on the usual listener port (1521). If you installed the OHS on a different machine than the database machine you would need to specify the hostname (or IP address) of the database machine here instead of localhost.

The next line specifies that mod_plsql will connect using the AL32UTF8 character set in the NLS settings. This is part of the Oracle APEX installation requirements and you should always use AL32UTF8.

PlsqlNLSLanguage               AMERICAN_AMERICA.AL32UTF8

While you may find that it works with other settings, I can guarantee that at some point it will come back to bite you and you may find very strange errors occurring. I once saw an APEX application that occasionally did not render correctly in the browser, sometimes only half the page would be output, other times garbled characters would appear. It took a while to figure it out, but eventually we tracked it down to the wrong setting being used in the DAD configuration. Now, with the benefit of hindsight, I typically check that first, but at the time we were convinced it was an "application issue".

The next line is the one that tells Apache to use the mod_plsql handler for any requests under the /pls/apex location:

SetHandler                     pls_handler

The next few lines are among the crucial ones:

PlsqlDatabaseUsername          APEX_PUBLIC_USER
PlsqlDefaultPage               apex
PlsqlDatabasePassword          <<your password here>>

Typically you would never need to alter the PlsqlDefaultPage value. The PlsqlDatabaseUsername will be APEX_PUBLIC_USER if you have started using APEX with a fairly recent release; if you've used Oracle APEX since the old days when it was called HTMLDB you may well find in some existing DADS.CONF files that you have a reference to HTMLDB_PUBLIC_USER.

The PlsqlDatabasePassword parameter specifies the password that you chose when you installed APEX (I hope you remember what it was!). This aspect of APEX often confuses people, but internally what happens is

PROCEDURE PARSE_AS_USER
Argument Name                                   Type       In/Out Default?
------------------------------     ----------------------- ------ --------
C                                               NUMBER(38)  IN
STATEMENT                                       CLOB        IN
LANGUAGE_FLAG                                   NUMBER(38)  IN
USERID                                          NUMBER(38)  IN     DEFAULT
USELOGONROLES                                   BOOLEAN     IN     DEFAULT
EDITION                                         VARCHAR2    IN     DEFAULT
APPLY_CROSSEDITION_TRIGGER                      VARCHAR2    IN
FIRE_APPLY_TRIGGER                              BOOLEAN     IN     DEFAULT

The procedure shown in Listing 1-3 is actually an overloaded one, so there are many different variants of the parameters you can pass in (for example, the statement can be a VARCHAR2 instead of a CLOB). However, you can see that there is a parameter called USERID which allows the calling routine to specify which user the code should be executed as. It is this feature that allows APEX to run different applications in different primary parsing schemas with the correct privileges (while preventing someone in Workspace A from accessing code/data from Workspace B).

You don't need to be too concerned with how this actually works, nor should you ever need to use DBMS_SYS_SQL yourself (in fact, it is perhaps the most powerful package in the database since it allows you to run code as ANY user). I wanted to highlight it for one reason: you should make sure the password to APEX_PUBLIC_USER is not known to anyone else, since it is a privileged user.

This point is very important. If I had to do a rough "finger in the air" calculation, I would say that the vast majority of people store the password in the DADS.CONF file in plaintext—after all, that's what the example uses and what the documentation shows, too! But you can store the password in an obfuscated format, by using the dadTool.pl command which is located in the $ORACLE_HOME/ohs/modplsql/conf directory. In that same directory you will find a file called dadTool.README that details how to use the tool in different environments (for example, Linux or Windows). Configuring dadTool.pl is not that difficult (I won't detail the steps here since they're already well documented in the dadTool.README file). Listing 1-4 shows an example in my environment:

[ohs@ae1 conf]$ export ORACLE_HOME=/home/ohs/OraHome_1
[ohs@ae1 conf]$ export PATH=$ORACLE_HOME/ohs/modplsql/conf:$PATH
[ohs@ae1 conf]$ export PATH=$ORACLE_HOME/perl/bin:$PATH
[ohs@ae1 conf]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
[ohs@ae1 conf]$export PERL5LIB=$ORACLE_HOME/perl/lib

[ohs@ae1 conf]$ perl dadTool.pl -o

All passwords successfully obfuscated. New obfuscations : 1

The first few commands (the export commands) are setting up my environment as per the dadTool.README file, then I run the dadTool.pl command, which reads the DADS.CONF file and converts the plaintext password into an obfuscated format. If you now look at the DADS.CONF file and locate the line for PlsqlDatabasePassword you will see that the password has been updated to the obfuscated version. (Note: the dadTool.pl does update the DADS.CONF file directly so it's always a good policy to take a backup of the file before you run it—obviously deleting the backup once you're happy the password has been obfuscated!)

[ohs@ae1 conf]$ grep PlsqlDatabasePassword dads.conf
PlsqlDatabasePassword          @BesOhl8aShdE5lMz2pA6zSVCzsRUFMeRAQ==

Now just because you've obfuscated the password, don't be lulled into a false sense of security. On Linux systems one extra step I usually take is to make sure that the DADS.CONF file is only readable by the Unix user you installed the OHS software on, as shown in Listing 1-5 (in other words, nobody else can read that file and see your obfuscated password—as Anton mentions in Chapter 8, it is trivial to construct a rainbow table of commonly used passwords which have been obfuscated in the same way and then compare them to your value to determine your clear text password).

[ohs@ae1 conf]$ ls -al dads.conf
-rw------- 1 ohs ohs 1364 Feb 14 14:19 dads.conf

[ohs@ae1 bin]$ $ORACLE_HOME/opmn/bin/opmnctl startall
opmnctl: starting opmn and all managed processes...

[ohs@ae1 bin]$ $ORACLE_HOME/opmn/bin/opmnctl stopall
opmnctl: stopping opmn and all managed processes...

One enhancement I typically make to save myself some keystrokes, is to create a script to automatically start, stop, and restart the OHS, shown in Listing 1-6, which comes in very handy whenever you make configuration files.

[ohs@ae1]$ ls -al
total 20
drwxrwxr-x  2 ohs ohs 4096 Nov  9 10:45 .
drwx------ 14 ohs ohs 4096 Mar 15 18:33 ..
-rwxrw-r--  1 ohs ohs  109 Nov  9 10:44 ohs-bounce
-rwxrw-r--  1 ohs ohs   70 Nov  9 10:45 ohs-down
-rwxrw-r--  1 ohs ohs   71 Nov  9 10:45 ohs-up

[ohs@ae1 bin]$ cat ohs-up
ORACLE_HOME=/home/ohs/OraHome_1
$ORACLE_HOME/opmn/bin/opmnctl startall
[ohs@ae1 bin]$ cat ohs-down
ORACLE_HOME=/home/ohs/OraHome_1
$ORACLE_HOME/opmn/bin/opmnctl stopall
[ohs@ae1 bin]$ cat ohs-bounce

ORACLE_HOME=/home/ohs/OraHome_1
$ORACLE_HOME/opmn/bin/opmnctl stopall
$ORACLE_HOME/opmn/bin/opmnctl startall

Instead of restarting all of the managed processes, you could just restart the OHS processes which is much quicker. To do that you can use the commands:

$ORACLE_HOME/opmn/bin/opmnctl stopproc ias-component=HTTP_Server

and

$ORACLE_HOME/opmn/bin/opmnctl stopproc ias-component=HTTP_Server

As you can see, the scripts are very simple; in fact, they could be combined into a single script, where a parameter is passed in to indicate whether to start, stop, or restart the OHS. However, for simplicity, I like to keep them as separate scripts—changing them is left as an exercise to the reader!

So now, you should have a running OHS that allows you to access your APEX instance, as shown in Figure 1-6.

Figure 1-6. Accessing APEX via the OHS

Notice in Figure 1-6 that the URL has the port number 7780; you'll see where that is specified in the next section.

Since the OHS is based on Apache, it uses the same main configuration file, called httpd.conf, which should be located in the $ORACLE_HOME/ohs/conf directory. I'm not going to cover every option in the file since it is already very well documented on the main Apache website (http://httpd.apache.org/). However, there are a few options that are relevant if you're running an APEX installation that you might wish to tweak.

First, if you wish to change the port number that APEX is running on then you need to locate the line in the httpd.conf file that specifies the Listen directive:

[ohs@ae1 conf]$ grep Listen httpd.conf
# Listen: Allows you to bind Apache to specific IP addresses and/or
# Change this to Listen on specific IP addresses as shown below to
#Listen 12.34.56.78:80
Listen 7780

Notice here that the lines that begin with a # are comments, the actual setting is the line that reads Listen 7780. So if you wished to run on port 8080 instead, you would change the 7780 to 8080. Obviously, you need to ensure that you don't try and run the OHS on a port that already has something else listening on it—this is actually a fairly common issue, and it can be pretty disastrous. I've witnessed the OHS error logs fill up repeatedly with errors; in one case, I saw it generate a 1GB error file in just a few seconds.

One common question that often comes up is "How can I run the OHS on port 80?" This question comes from the fact that web browsers default to accessing web servers on port 80 unless you specify a different port. In other words, the URL

http://yourserver:80/pls/apex/f?p=1000:1

is logically identical to the URL

http://yourserver/pls/apex/f?p=1000:1

So you'll probably want to run your OHS on port 80 to avoid having to give users a URL with a non-default port number in it (your URLs will look "prettier").

Now, on Unix, this is actually more difficult than you'd expect it to be; if you simply change the Listen directive to specify port 80, you will most likely receive an error like this:

[ohs@ae1 conf]$ $ORACLE_HOME/opmn/bin/opmnctl startall
opmnctl: starting opmn and all managed processes...
=============================================================================
opmn id=localhost.localdomain:6200
    0 of 1 processes started.

ias-instance id=IAS-1
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--------------------------------------------------------------------------------
ias-component/process-type/process-set:
    HTTP_Server/HTTP_Server/HTTP_Server/

Error
--> Process (index=1,uid=1350181004,pid=21365)
    failed to start a managed process after the maximum retry limit
    Log:
    /home/ohs/OraHome_1/opmn/logs//HTTP_Server~1.log

This is due to the fact that on Unix, listening on ports between 0 and 1024 is reserved for privileged users (you can consider it a historical reason), since typically things like mail server, SSH servers, etc., tend to run on these lower port numbers; if regular users were allowed to run their own processes on any ports, it could be a potential security risk.

To allow the OHS to run on a privileged port, you have a couple of options:

Both of these options have potential risks, since having the OHS running as the root user means that if the webvserver is compromised then the attack could potentially have full privileges over the machine. Please, weigh these risks carefully before adopting this approach.

So, for example, if you wished to modify the permissions to make the OHS run as the root user, you could use the commands shown in Listing 1-7.

[ohs@ae1 bin]$ ls -al .apachectl
-rwxr-xr-x 1 ohs ohs 1703780 Apr  5  2007 .apachectl
[ohs@ae1 bin]$ pwd
/home/ohs/OraHome_1/ohs/bin
[ohs@ae1 bin]$ ls -al .apachectl
-rwxr-xr-x 1 ohs ohs 1703780 Apr  5  2007 .apachectl
[ohs@ae1 bin]$ su root
Password:
[root@ae1 bin]# chown root .apachectl
[root@ae1 bin]# chmod 6750 .apachectl
[root@ae1 bin]# ls -al .apachectl
-rwsr-s--- 1 root ohs 1703780 Apr  5  2007 .apachectl

You should now be able to start the OHS as the OHS Unix user but have it running on port 80 (assuming you remembered to change the Listen directive in the httpd.conf file).

There are another couple of tweaks that I typically make to the httpd.conf file that are a bit more specific to the Apache configuration than they are to APEX if you have a medium-to-high number of end users.

The first setting I increase is the MaxClients setting, which is typically set to 150 by default. As you can see in the output below, if the number of end users simultaneously accessing exceeds this number, the end users will experience sluggish (actually blocked) connections.

#
# Limit on total number of servers running, i.e., limit on the number
# of clients who can simultaneously connect --- if this limit is ever
# reached, clients will be LOCKED OUT, so it should NOT BE SET TOO LOW.
# It is intended mainly as a brake to keep a runaway server from taking
# the system with it as it spirals down...
#
MaxClients 150

The obvious question you'll ask is, "what should I increase this to?". Well, of course, it depends, but as a general guideline we typically increase this to 300+, sometimes 500; it really depends on what your infrastructure can support. You can determine what your infrastructure will support by benchmarking it, as I'll discuss later.

The other parameter I tend to modify is the KeepAlive setting, which determines whether the browser maintains a persistent connection to the web server whenever it makes a request.

#
# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
#
KeepAlive On

I tend to set KeepAlive to Off for sites that I expect to have a medium-to-large number of end users. Because of the way APEX works, it's often better from a performance standpoint for end users to experience the slight latency overhead of having their browser re-establish a connection to the web server with each request, rather than users being unable to obtain a web server connection due to other users still retaining persistent connections. (In such cases response time for the users maintaining the persistent connection would be good, but for other users the application would appear to be slow or unresponsive).

These settings, together with other more general Apache configuration settings such as StartServers, MinSpareServers, MaxSpareServers—all of which are documented in the Apache documentation—should be carefully tuned to your own hardware and infrastructure. There is no single perfect setting for all situations. I've found that over time you raise and lower these settings in response to the ongoing performance of your servers. (It's very important to revisit these settings whenever you upgrade the hardware, for example.)

One of the nice features of the Apache HTTP Server (and therefore also the OHS), is that a single web server can support multiple individual websites, with each website having its own distinct URL. If you look at the typical format of an APEX application URL, it would be similar to this:

http://yourserver/pls/apex/f?p=1000:1:

where 1000 represents the application id and 1 represents the initial page you wish your end user to land on. Now, through the use of application and page aliases, you can make this URL a bit friendlier for end users, because you can define a string for the application alias, as shown in Figure 1-7.

Figure 1-7. Defining an application alias

You can also do the same thing with a page alias, as shown in Figure 1-8.

Figure 1-8. Defining a page alias

Now instead of the URL

http://yourserver/pls/apex/f?p=1000:1:

our users can use

http://yourserver/pls/apex/f?p=ORDERS:HOME:

which is a little bit nicer, I hope you'll agree. One thing to bear in mind is that, internally, whenever APEX generates any links within your application, it will use the numeric application id, unfortunately, rather than the application alias. The end results of this means that the application alias and page alias will be changed in the URL to the numeric ids once the user starts navigating within the application.

But you can go a bit further with the use of Apache Virtual hosts. You have a couple of options at this point: you can either put the definition of the virtual hosts in the main httpd.conf file itself, or include them in a separate file. My own personal choice is to try and keep the main httpd.conf as clean as possible and to do all my own customizations in my own files that are included from the main httpd.conf. To achieve this, I create a subdirectory called vhosts (you can name it anything you like) in $ORACLE_HOME/ohs/conf and then include the following line at the end of the httpd.conf file:

include "/home/ohs/OraHome_1/ohs/conf/vhosts/*.conf"

This means I can create individual files in the vhosts subdirectory for each host I wish to support which, for me at least, makes it much more manageable than having a single file that contains everything. The *.conf in the include statement means include all files that have a file suffix of conf. This, again, has a side benefit, which is that I can very easily disable a virtual host by renaming the configuration file to foo.conf.old (or some other suffix) which means it won't be included when I reload the OHS.

So now I can create a configuration file for my new virtual host—let's call it foo-orders.conf—which contains the code in Listing 1-8.

<VirtualHost *:80>
ServerName www.foo-orders.com
ServerAlias www.foo-orders.com

DocumentRoot /home/foo/www/

ProxyPreserveHost On
RewriteEngine On
RewriteRule ^/$ /pls/apex/f?p=ORDERS:HOME:0: [R=301,L]
</VirtualHost>

Once again, much of this is specific to Apache rather than APEX, but I wanted to highlight how you can configure the web server to give your end users a much more "friendly" URL. The key sections here are the ServerName and ServerAlias sections which define that this virtual host is relevant for anyone using http://www.foo-orders.com as the domain name part of the URL in their browser.

The DocumentRoot directive specifies which directory the static files (for example the JavaScript, CSS, and images) will be served from. Recall earlier that I mentioned many people put these files below the same directory the /i/ directive points at. Well, using a virtual host like this lets you define different directories for different applications, which makes it a much more flexible way to work. For example, one huge benefit of this is that different developers can work on different applications and you can tie the permissions down so that they can't overwrite each other's files.

The next important section is the RewriteRule directive itself. Apache rewrite rules are a language in their own right and even have books dedicated to them, so I don't intend to go into a huge amount of detail on how they work, but as an overview the way to read them is as a regular expression and a result. So in this example

RewriteRule ^/$ /pls/apex/f?p=ORDERS:HOME:0: [R=301,L]

the rewrite rule will fire if the incoming URL matches the regular expression ^/$, where ^ means start and $ means end—in other words, if the entire URL is just http://foo-orders.com/, if it does match then the user (or rather their browser) is redirected to the URL /pls/apex/f?p=ORDERS:HOME:0:, which is a relative URL (relative to the same domain name). The [R=301,L] means that the web server returns an HTTP-301 code to the browser, which tells the browser that this is a permanent redirect (as opposed to a temporary one if your site is down for maintenance, for example).

Now, this virtual host example might look complex at first glance and, indeed, if you're unfamiliar with Apache configuration (and rewrite rules) in general, it is a bit strange looking. However, you can pretty much adopt a copy/paste approach to extend this example to support any URL, changing the application and page alias (or id if you prefer). The one step I missed is obviously that you need to ensure that the domain name you use does actually resolve to the web server IP address (typically done by whomever controls your DNS configuration).

So, with just a few configuration changes you've gone from giving end users a URL like this:

http://yourserver/pls/apex/f?p=1000:1:

to a URL like this:

http://www.foo-orders.com

Note that you don't have to specify the application or page alias/id anymore, since the Apache rewrite rule will take care of redirecting the user to the correct location now.

You can go even further and give some nice shortcut URLs to specific pages, as shown in Listing 1-9.

<VirtualHost *:80>
ServerName www.foo-orders.com
ServerAlias www.foo-orders.com

DocumentRoot /home/foo/www/

ProxyPreserveHost On
RewriteEngine On
RewriteRule ^/$ /pls/apex/f?p=ORDERS:HOME:0: [R=301,L]
RewriteRule ^/login$ /pls/apex/f?p=ORDERS:LOGIN:0: [R]
RewriteRule ^/news$ /pls/apex/f?p=ORDERS:NEWS:0: [R]

</VirtualHost>

http://www.foo-orders.com/news

http://www.foo-orders.com/f?p=ORDERS:NEWS:0:

This can be very important for search engines (for example, Google and Bing) to provide nicer URLs in the search results rather than the typical APEX URL (which includes a session id which would be different for each user).

Okay, great, but there's one last technique I want to share with you that I often find useful. Let's imagine you want to make your great APEX application public using the techniques I've just shown. You give your end users a URL like this:

http://my-fantastic-application.com

and they get redirected to this:

http://my-fantastic-application.com/pls/apex/f?p=1000:1

What's to stop a curious end user changing the 1000 to some other id, thereby accessing one of your other APEX applications that you hadn't intended them to see? Well, you can again use some Apache rewrite logic to ensure that they can only access application 1000 via that particular domain name URL, as shown in Listing 1-10.

<VirtualHost *:80>
ServerName www.my-fantastic-application.com
ServerAlias www.my-fantastic-application.com

DocumentRoot /home/foo/www/

ProxyPreserveHost On
RewriteEngine On
#RewriteRule ^/$ /index.html [R=301,L]
RewriteRule ^/$ /pls/apex/f?p=FANTASTIC_APPLICATION:HOME:0: [R=301,L]

RewriteCond %{REQUEST_URI}%{QUERY_STRING} /pls/apex/f?p=(.*)
RewriteCond %{REQUEST_URI}%{QUERY_STRING} !/pls/apex/f?p=(FANTASTIC_APPLICATION:.*)
RewriteCond %{REQUEST_URI}%{QUERY_STRING} !/pls/apex/f?p=(1000:.*)
RewriteRule ^.* /pls/apex/f?p=FANTASTIC_APPLICATION:HOME:0: [R=301]
</VirtualHost>

In this example, we use the RewriteCond directive to ensure that we are accessing the URL using an application id of 1000 or the application alias FANTASTIC_APPLICATION; if not, then the URL will be redirected back to the home page. There are an almost infinite number of possibilities available using rewrite rules and conditions and you're really only limited by your imagination (or rather your practical requirements!). Hopefully, these examples have shown you some of the possibilities to give your production applications a more polished appearance to end users.

So far we have concentrated on configuring the OHS to access your APEX applications; now we are going to look at a performance tweak that you can make to really increase the scalability of your APEX environment.

Now, I want to say up front that I've talked about these techniques many times. I've presented them at some of the major Oracle conferences such as Oracle OpenWorld, the ODTUG Kaleidoscope, the UKOUG conference, the IOUG Collaborate conference, and the list goes on. I also covered some of these techniques in my first book, Pro Oracle Application Express. So, you might ask "Hey, don't you have any new stuff to show us?". Well, these techniques can have such an impact on the performance and scalability of your applications that I won't stop talking about them until I've convinced every last one of you that they're worth investigating!

The first tweak I want to mention is another change to the OHS configuration that can greatly optimize the number of database pooled connections you see as a result of the mod_plsql handler. My good friend Joel Kallman, who is Director of Software at Oracle, wrote an excellent blog post on this feature, which is available at

http://joelkallman.blogspot.com/2008/01/oracle-http-server-apache-20-and.html

Basically, this feature takes advantage of the true multi-threading capability in Apache 2.0, rather than the previous prefork-based architecture in Apache 1.3. In other words, with the prefork-based approach there was a single database connection per HTTP process, whereas with the multi-threaded implementation (known as Multi-Processing Modules or MPM), there is a database connection pool which is shared among all threads.

Sounds complex, right? But actually, the configuration change is extremely simple. In the opmn.xml file you will see a section similar to this:

<ias-component id="HTTP_Server">
  <process-type id="HTTP_Server" module-id="OHS2">
    <module-data>
      <category id="start-parameters">
        <data id="start-mode" value="ssl-enabled"/>
     </category>
    </module-data>
    <process-set id="HTTP_Server" numprocs="1"/>
  </process-type>
</ias-component>

It is quite a simple change and involves adding an extra line, so the section becomes

<ias-component id="HTTP_Server">
  <process-type id="HTTP_Server" module-id="OHS2">
    <module-data>
      <category id="start-parameters">
        <data id="start-mode" value="ssl-enabled"/>
        <data id="mpm" value="worker"/>
      </category>
    </module-data>
    <process-set id="HTTP_Server" numprocs="1"/>
  </process-type>
</ias-component>

Notice the addition of the setting of the mpm parameter to a value of worker. So what does that do? Well, let's take a look at how it worked before we made that change. If we examine the running Apache processes

[ohs@db1 conf]$ ps -u ohs | grep httpd
18296 ?        00:00:00 httpd
18302 ?        00:00:00 httpd
18304 ?        00:00:00 httpd
18306 ?        00:00:00 httpd
18309 ?        00:00:00 httpd
18314 ?        00:00:00 httpd
18317 ?        00:00:00 httpd
18326 ?        00:00:00 httpd
18329 ?        00:00:00 httpd
18330 ?        00:00:00 httpd

notice how many different httpd processes there are. Now let's look at the database sessions attributed to that Unix user:

SQL> select count(*) from v$session where osuser = 'ohs';

COUNT(*)
----------
        10

Now, after switching into MPM mode using the settings described above, let's look at the Apache processes again:

[ohs@ae1 conf]$ ps -u ohs | grep httpd
18686 ?        00:00:00 httpd.worker
18687 ?        00:00:00 httpd.worker
18690 ?        00:00:00 httpd.worker
18691 ?        00:00:00 httpd.worker

The httpd.worker in the output confirms we're running in MPM mode. We should also notice fewer database connections (since the true connection pooling is in force):

SQL>  select count(*) from v$session where osuser = 'ohs';

  COUNT(*)
----------
         4

Obviously, the number of connections will fluctuate depending on system load—the connection pool should adapt and create more connections as needed and free them when no longer needed. However, the key thing to bear in mind here is the difference between the two methods. Generally, the benefits of running in MPM mode can have an influence on the performance and responsiveness of your application when supporting a larger number of users, not to mention the reduced overhead on the database in terms of having to maintain fewer database sessions.

It's worth noting, as Joel mentions in his blog post, that on Windows, the OHS has always been multi-threaded, so this setting is only of interest to people running the OHS on Unix servers (which seems to be the majority of people that I speak to).

One of the other really cool features available with the OHS is the ability to have it compress the web server response before sending it to the browser. This has a couple of big advantages:

Web server compression is pretty much what it sounds like: the web server will compress the content in the same way you would run WinZip (or another compression application) to reduce the size of a file. The amount the file can be compressed is really a factor of the type of file it is. Files such as JavaScript files, CSS files, and HTML files, which are all text, are highly compressible, while images are typically not as compressible (particularly JPEG images, which are already in an optimized format). So usually you would configure web server compression to only compress things that you know are highly compressible—otherwise the payoff is not worth the overhead of compressing it.

Now, before we dive into how you configure web server compression, I want to show you a couple of tools I use when looking at my APEX applications to evaluate how they can be optimized. As I described in the foreword, this book is in honour of two friends, Carl Backstrom and Scott Spadafore. I'll always remember something Carl once told me:

"Even if you have to deploy to Internet Explorer, develop in Firefox—it'll make your life so much easier."

The reason he said this was that Firefox (certainly at the time) was a much friendlier browser for developers, since it gave much better debugging and inspection capabilities. The other reason was because of the Firebug plug-in. This plug-in alone transformed my web development. Looking back now, it's hard to remember just what an impact the Firebug plug-in made, since most browsers these days (such as Safari, Chrome, and the latest Internet Explorer) give similar functionality, but Firebug was really groundbreaking—so much so that I still use it today.

You can obtain Firebug (which only works as a Firefox plug-in) from http://getfirebug.com. When you install Firebug you can inspect and evaluate any site that is open in your browser. For example, in Figure 1-9 you can examine the individual requests for resources in my page using Firebug.

Figure 1-9. Examining page requests using Firebug

Now, I'm not going to cover the full capabilities of Firebug (again, that could be a chapter in its own right), but you can see in Figure 1-9 that using the Net tab, you can get a very nice visual overview of all the resources (such as JavaScript files, CSS files, and images) that are referenced in the page. Firebug also gives you details about the size of each of those resources and how long it took to download them from the web server.

If you drill down into the detail of one of those resources, you can see more details about the request and response headers, as shown in Figure 1-10.

Figure 1-10. Viewing response and request header detail

Notice in Figure 1-10 that you can view the handshaking and communication between the browser and the web server. The request headers are from the browser informing the web server which languages and encodings it supports; the web server responds with the response headers (and content) to tell the browser what data is being returned and in what format. The key thing here is to notice in the Request Headers the line that reads

Accept-Encoding: gzip, deflate

This is the browser telling the web server that it supports compressed data in the response. The compressed data can be either in the gzip or deflate format (the end results of compression are similar, but use slightly different methods). The web server can then decide whether to send compressed or the default uncompressed content, depending on whether the browser supports compression or not. If the browser does not support compression, it won't send that request header and the web server will send the regular uncompressed response. In other words, there's little to no downside of enabling compression on your web server, since any browsers that don't support compression will still have the uncompressed data sent to them, while browsers that do support compression will benefit from the compressed version.

In my first book, Pro Oracle Application Express, I went into a lot of detail on installing and configuring the mod_gzip module (which is another Apache module) that enables your web server to provide compressed output. I'm not going to reproduce all the steps here (I'm hoping you already purchased that book!), but I do want to show the effects, in case you either didn't buy that book or simply skimmed past that bit.

So you've seen how you can examine the web page content using Firebug. Let's look quickly at another Firefox plug-in that I find extremely useful (bear with me, it is relevant to web server compression). That plug-in is called YSlow and is available from http://developer.yahoo.com/yslow. It was actually developed by Yahoo (hence the Y in YSlow) as part of their ongoing development to profile and optimize the Yahoo websites. What YSlow does is to run a series of checks and rules against your web page and assign a score against each of those checks. For example, in Figure 1-11 you can see that YSlow has given my page an overall grade of C and you can see the individual scores for each of the checks.

Figure 1-11. Using YSlow against the page

Notice that we got an F for the "Compress components with gzip". That is because we currently have not configured the server to support compression. We can also see the individual files that YSlow suggests we should be compressing; it knows that these files are plain text files and therefore should compress well to a much smaller size.

If you drill into the Components section of YSlow, as shown in Figure 1-12, you can see much more detailed information about each component—in fact, more detail than we saw in Firebug earlier. This is why I always use a combination of tools when examining websites, since there is no single tool that shows everything.

Figure 1-12. Components view in YSlow

Figure 1-12 shows the individual components; in this case I have drilled into the JavaScript files. The interesting thing to notice here is that YSlow shows you the size of each resource, in kilobytes. Also notice that there is a column for GZIP (KB), which would show the compressed size if the content was in compressed format. Since we have not enabled gzip compression yet, obviously there is nothing in this column. However when you do enable compression, you will be able to use YSlow to see what kind of benefit is gained.

Okay, so enough talking, right? Let's get on with enabling gzip on our OHS. The first thing you need to do is to make sure the gzip module is loaded into your OHS configuration. The installation of mod_gzip is fairly straightforward and well documented. You simply need to place the module into the directory containing all your other Apache modules (usually in the libexec directory). It's also recommended that you use the separate configuration file (mod_gzip.conf) for all the mod_gzip-related configuration and include this new configuration file from your main Apache configuration file (httpd.conf), rather than placing the mod_gzip configuration directly in the main file.

If you look in the httpd.conf file you should notice a section where all the modules are loaded. For example:

...
LoadModule dbm_auth_module      libexec/mod_auth_dbm.so
LoadModule digest_module        libexec/mod_digest.so
LoadModule proxy_module         libexec/libproxy.so
LoadModule cern_meta_module     libexec/mod_cern_meta.so
...

This is taken from my server configuration—yours may be slightly different. Now you can add the mod_gzip module:

LoadModule gzip_module          libexec/mod_gzip.so

You should also copy the sample mod_gzip.conf to the Apache configuration file directory. Although the sample mod_gzip.conf should work fine in most cases, I usually make a few changes, one of which is adding the following line:

mod_gzip_item_include    handler    ^pls_handler$

The purpose of this line is to include compression on anything that is being handled by the pls_handler. The mod_plsql handler is responsible for handling requests for our DAD, which is how our APEX sessions are handled. We have added this because we've found in certain cases, where the MIME type is not detected properly, some items will not be compressed, even though they may be highly compressible items, such as CSS and JavaScript files. You may want to check whether this line is suitable for your own configuration (you can determine this through testing).

Next, you need to include the mod_gzip configuration by adding the following line to the main Apache configuration file (httpd.conf):

# Include the mod_gzip settings
include "/home/ohs/OraHome_1/ohs/conf/mod_gzip.conf"

Make sure you use the correct path to the mod_gzip.conf file for your own installation. Now reload the OHS and you should have a working installation of mod_gzip.

Now if you retest the application in YSlow you should see whether the gzip compression has been enabled correctly and what the impact has been. You can see in Figure 1-13 that we now get an A grade for the Compress components with gzip test. Excellent!

Figure 1-13. YSlow gives an A grade for gzip compression

As we did before, let's drill into the components section to see what the compression effect has been on the size of the data transferred (Figure 1-14).

Figure 1-14. Examining gzip compression in YSlow

You can see in Figure 1-14 that the GZIP (KB) column now has a figure in it, showing that we are indeed compressing the content on the web server before sending it to the browser. Using the apex_4.0.js file as an example, which is a standard JavaScript file included by APEX itself, you can see that before compression, it was 67.2Kb in size, whereas post compression it was reduced to 19Kb in size— roughly less than a third of the original size. As a rough guide, I typically find that JavaScript, CSS, and HTML files compress to anywhere between 1/3 to 1/5 of their original size, depending on the amount and type of content.

Another thing to notice from Figure 1-14 is that very small files, such as apex_interactive_reports_4_0.js, aren't compressed since the potential reduction in size is minimal compared to the slight processing overhead in compressing the file. This leads us nicely into a question I'm often asked regarding web server compression:

Is there an overhead in compressing the files?

The answer is, yes, there is, since the CPU has to do some work to compress the content. However, with the modern CPUs these days the overhead is extremely minimal (compared to, say, 15 years ago when it was much more noticeable). My stock answer to this question would be

Yes, there is a very minimal overhead, but it's more than outweighed by the benefits.

In the book Pro Oracle Application Express I do some benchmarking to determine the performance benefit of compressing the files. Again, rather than reproducing it all here, I will share the final results. In the benchmarking I simulated a large number of end users hitting the web server and requesting different pages. I tested the response of the server with gzip disabled and then with it enabled. The difference in the results was surprising even to me.

You can see from the benchmarks that when mod_gzip is enabled, the web server is able to handle roughly eleven times as many active connections; in other words, an order of magnitude more connections. That is a huge benefit from a relatively simple server configuration change. You can also see that the bandwidth savings are significant too (in terms of the difference). It is easy to overlook the benefit that a reduction in bandwidth would have; after all, network speeds are getting faster and faster all the time, so who really cares? Well, even though network speeds are increasing all the time, we are also using more and more features in our websites—for example, third-party Javascript libraries—which increase the "weight" of our page. If we can reduce the size of the data that has to be downloaded to each user, then we are also decreasing the amount of network traffic on our infrastructure, which means that other applications using the same infrastructure can benefit, too. It's a win all round.

In the previous section we covered compressing the web server output. In this section, we will cover the expiry headers features which enabled browsers to cache static content locally rather than requesting it from the web server every time. If we look again at YSlow, you can see in Figure 1-15 that we get graded an F for the Add Expires headers test.

Figure 1-15. Examining expiry headers in YSlow

So what are expiry headers? Well, in a nutshell, when the browser requests a resource, such as an image, from the web server the web server can add a header to the response to tell the browser how long that resource can be cached in the browser's local cache. If the browser needs that resource again, it can look in the local cache and check if the resource is still valid by looking at the date in the expiry header. If the cached version is still "within date", the browser can use the cached version, thereby avoiding a web server request; otherwise, the browser will request the resource from the web server again.

Let us look again at the components section and examine the expiry header, as shown in Figure 1-16.

Figure 1-16. No expiry headers set

Notice that currently there are no expiry headers set. There is, however, something called an ETag, which we will discuss shortly. As I mentioned previously, Expiry headers allow us to tell the browser that a particular resource can be cached locally until a particular time. Note that you cannot force the browser to do this, it is completely up to each individual browser whether they cache the resource or not (since the user might have turned caching off, or have a very small cache region), so consider expiry headers a hint/suggestion rather than a rule that the browser must obey.

So how do you configure expiry headers? Well, the configuration is slightly easier than with mod_gzip since the mod_expires Apache module is shipping out of the box with the OHS.

First, you need to load the mod_expires module, so add the following line to your httpd.conf if it does not already exist:

LoadModule expires_module modules/mod_expires.so

Next, we need to include the configuration file for the expiry settings:

# Include the mod_expires configuration file
include "/oracle/ohs/Apache/Apache/conf/mod_expires.conf"

As I mentioned in the mod_gzip section, I prefer to maintain a separate configuration file for these things, rather than cluttering up the main httpd.conf file. So you will need to create this file and adjust the path to suit your own environment.

Now let us take a look at a sample mod_expires.conf file; note that this is a simple example and you can change the values to suit your own needs:

ExpiresActive On
ExpiresByType image/gif "access plus 15 days"
ExpiresByType image/jpeg "access plus 15 days"
ExpiresByType image/png "access plus 15 days"
ExpiresByType application/x-javascript "access plus 7 days"
ExpiresByType text/javascript "access plus 7 days"

ExpiresByType text/css "access plus 7 days"
FileETag None

The directives for the mod_expires module are quite logical (and well documented); it is fairly easy to understand what they mean even if you have never seen mod_expires rules before. In this case, I first enable the mod_expires module with this line:

ExpiresActive On

Next, we define the rules for the different content types, based on their mime type. For example:

ExpiresByType image/gif "access plus 15 days"

means that all GIF images will have an expiry header added with a date to expire 15 days from the date they were just accessed. In other words, if the browser needs that GIF image again and it is within 15 days from the time it was last requested from the web server, then the browser will be able to use the cached local version. The rest of the rules follow a similar pattern. In the case of Javascript and CSS files, we define shorter cache durations since we expect those files to be changed more frequently. The final line

FileETag None

disables ETags. So what are ETags? Well, ETags (Entity Tags) are a way for the browser and web server to determine if a resource has changed. So, for example, if the browser needs to load the logo for your web page, it can contact the web server and ask if the resource is different to the cached version that the browser already has. If the resource is different (in other words, if it has changed), the web server will send the browser the updated version. If the resource has not changed, the web browser can use the cached version, thereby saving the overhead of downloading the new version. The way the browser and server determine whether the resource has changed or not is via the ETag, which is essentially a unique identifier based on attributes of the resource (perhaps the last updated date, or the file size, or a combination). You don't need to worry too much about how ETags work since the server is responsible for generating the ETag identifiers transparently if you have them enabled.

So how do ETags compare to Expiry Headers, and would you benefit from them? Well, they certainly can help in some situations and at first glance might appear to perform the same functionality as Expiry Headers. However, there is a very subtle difference, namely:

Whenever I present on this topic, I have a little example which I think helps to illustrate the subtleties, so please bear with me while I describe it. If you think about the different options regarding expiry headers and browser caching, it's a bit like making a cup of coffee (I told you, bear with me!):.

Hopefully that very silly story helps to make the differences between the methods more concrete. And while this might make it sound like I am not a big fan of ETags, that is not true; I think they certainly have their uses. However, with a system such as APEX, where each web server request might result in a database request (if that resource is stored in the database), you need to be very careful that you don't make unnecessary requests if you hope to scale to large volumes of users.

Okay, now we have enabled Expiry Headers, what difference has that made? If we rerun Yslow, we should see that we score an A for the Add Expires headers test, as shown in Figure 1-17.

Figure 1-17. Running YSlow with Expiry Headers enabled

If we drill down into the detail of the components, you should see that each of the resource types that we specified in the mod_expires.conf file should now have an expiry header attached to it, as shown in Figure 1-18.

Figure 1-18. Expiry headers defined

Now that we have the EPG running, let's take a look at a typical APEX application page using YSlow as we did in the OHS section.

Figure 1-21 shows that we achieve an F rating for both the Add Expires headers and Compress components with gzip tests. Now, this is potentially where we see some issues with the EPG: there is unfortunately no support for compressing the data between the browser and the EPG. So what are your options here? Well, the only workaround is to use another external web server that sits in between the EPG and the browser and proxies requests between the two, compressing the content in the process.

Figure 1-21. Using YSlow with the EPG

You could, for example, use a standard Apache installation as the proxy, or you could use the Oracle Web Cache product, which is, extremely flexible and allows very granular control over what is compressed and cached.

However, in my opinion, if you're going to the trouble of having to configure an external component to overcome the deficiencies of lack of compression in the EPG, why not just install the OHS and not use the EPG in the first place? So, when would I use the EPG? Well, typically, I would use the EPG when I need to set up an APEX system that I know is never going to support a large number of users—perhaps a single user system, or a development or test environment, for example. In that situation, the convenience of the ease of installation of the EPG would probably be outweighed by the fact that the infrastructure is not quite as optimal as it could be (I say "probably" because I am a bit of an optimization geek).

So we know the EPG does not support native compression, but what about expiry headers? Well, again, there is no native support for expiry headers (let alone custom expiry headers) with the EPG, which could be quite a significant issue if you wished to use it for a high volume site (i.e., one you expected to be used by a large number of users).

Now, you might get the impression from this that the EPG can't really be optimized. Well, it's certainly true, in my opinion, that it's not as flexible and configurable as the OHS. However, there are a few things you can do to improve the performance of the EPG if you do choose to use it.

The embedded PL/SQL gateway uses the shared server architecture of the Oracle Database. One issue I often see is that when people enable the EPG, they do not adjust the value of SHARED_SERVERS from the default to ensure that it is high enough to deal with the number of end users accessing the APEX applications.

You can check the current value of the SHARED_SERVERS parameter by using the following command:

system@ae1> show parameter shared_servers;

NAME                                 TYPE        VALUE
-----------------------------        ----------- ------------------------------
max_shared_servers                   integer
shared_servers                       integer     1

Notice that the value of SHARED_SERVERS is set to 1, which is the default on the release of the database I used. So what does this mean? Well, it means that all of the end users of our application that access the application via the EPG are going to experience contention since there is only one shared server process to deal with those requests.

I think at this stage it is useful to check the impact of changing the SHARED_SERVER parameter by benchmarking it. Now, there are many different ways and tools that you could use, and I can't really recommend the best method for your environment (in our company we use a combination of tools). However, for this simple benchmark I will use a free command-line tool called HTTPerf that is available from http://code.google.com/p/httperf.

The HTTPerf website contains information on how to install and use it, which I'm not going to repeat here, since the purpose of this section isn't to show you how to benchmark, I just want to use the tool to show you the difference changing the SHARED_SERVERS parameter can have. As a disclaimer, obviously if you benchmark this on your own system, you might (and most probably will), get different figures to the ones I show here; the important thing is the difference between the figures, not the figures themselves.

So, let's do a quick test of simulating users accessing the home page of our APEX application:

[jes@ae1]$ httperf --hog --server="ae1:8080/apex/f?p=101:1" --wsess=10,5,2 --rate 1

 --timeout 5

This command will cause HTTPerf to generate a total of 10 sessions at a rate of 1 session per second. Each session consists of 5 calls that are spaced out by 2 seconds. There is nothing really magic about these figures, they just seemed like a reasonable amount of traffic to throw at the web page to show some indicative statistics. The output that we get from the HTTPerf command is very verbose and looks like the following (note that I ran the command a few times to get a good "average"):

[jes@ae1]$ httperf --hog --server="ae1:8080/apex/f?p=101:1" --wsess=10,5,2 --rate 1

 --timeout 5
httperf --hog --timeout=5 --client=0/1 --server=ae1:8080/apex/f?p=101:1 --port=80 --uri=/

 --rate=1 --send-buffer=4096 --recv-buffer=16384 --wsess=10,5,2.000
httperf: warning: open file limit > FD_SETSIZE; limiting max. # of open files to FD_SETSIZE
Maximum connect burst length: 1

Total: connections 40 requests 70 replies 36 test-duration 24.073 s

Connection rate: 1.7 conn/s (601.8 ms/conn, <=10 concurrent connections)
Connection time [ms]: min 43.6 avg 3186.9 max 5015.6 median 2048.5 stddev 1460.8
Connection time [ms]: connect 22.7
Connection length [replies/conn]: 1.000

Request rate: 2.9 req/s (343.9 ms/req)
Request size [B]: 108.0

Reply rate [replies/s]: min 1.4 avg 1.6 max 1.8 stddev 0.2 (4 samples)
Reply time [ms]: response 1497.0 transfer 0.0

Reply size [B]: header 166.0 content 298.0 footer 0.0 (total 464.0)
Reply status: 1xx=0 2xx=0 3xx=0 4xx=36 5xx=0

CPU time [s]: user 2.90 system 21.17 (user 12.1% system 87.9% total 100.0%)
Net I/O: 1.0 KB/s (0.0*10^6 bps)

Errors: total 34 client-timo 4 socket-timo 0 connrefused 0 connreset 30
Errors: fd-unavail 0 addrunavail 0 ftab-full 0 other 0

Session rate [sess/s]: min 0.00 avg 0.25 max 0.40 stddev 0.19 (6/10)
Session: avg 5.00 connections/session
Session lifetime [s]: 15.1
Session failtime [s]: 11.0
Session length histogram: 2 0 0 2 0 6

You can see there is a lot of information to digest here. Some highline figures that jump out are

Total: connections 40 requests 70 replies 36 test-duration 24.073 s

So, we established 40 connections and performed 70 requests, with the web server returning 36 replies, taking a total of around 24 seconds to perform. You might wonder what happened to the other requests; if you look lower down in the output you can see that the server returned some failures:

Errors: total 34 client-timo 4 socket-timo 0 connrefused 0 connreset 30

So, out of the 70 requests, the EPG handled 36 of them successfully and 34 of them resulted in an error (4 of them were timeouts while 30 of them were a result of a connection reset due to the shared server not being able to handle them in time).

Another useful statistic is the number of connections per second that the server processed (the corollary figure is the amount of time it took to process each connection):

Connection rate: 1.7 conn/s (601.8 ms/conn, <=10 concurrent connections)

We managed to achieve a rate of 1.7 connections per second. Is that good or bad? Well, if we scale it up and we averaged that rate across a 24-hour period, it would mean we could handle around 146,000 web requests a day. That figure is certainly above the requirements of many applications, but also below the requirements of many others. Plus, we need to factor in that with most websites there are peak periods of activity. If we had to provide an APEX application that could support 100,000 page views a day it would typically be 100,000 page views during working hours and not spread across the entire 24 hours. Context is everything when it comes to benchmarking and particularly when analyzing the results.

What happens when we change the SHARED_SERVERS settings? Well, let's try a small change first, doubling it from 1 to 2:

system@ae1> alter system set shared_servers=2 scope=both;

System altered.

Now if we run the same test as before, we find that the connection rate went from 1.7 connections per seconds to 2.5 connections per second, which is a modest but measurable (and more importantly, repeatable) improvement. If we now bump the SHARED_SERVERS setting up to a more realistic limit:

system@ae1> alter system set shared_servers=5 scope=both;

it improves performance to 6.8 connections per second, with far fewer (but still some) failed connections. This means that by changing a single parameter we went from supporting around 102 requests per minute to 408 requests, a four-fold increase. Note that these tests were performed on a very old, very low spec test machine and are not indicative of the sorts of figures you should be trying to achieve on your production system! As I mentioned earlier, the important thing here is the difference between the figures, not the figures themselves.

You might be wondering how the OHS performed with the same test. I found with mod_gzip and mod_expires disabled, the OHS was able to handle around 20 connections per second (with no failures). When I enabled both mod_gzip and mod_expires the performance stayed the same; however, upon investigation this was because the HTTPerf tool does not handle compressed content or handle expiry headers correctly—unlike a real browser. In other benchmarking tests I've performed, the best the OHS with mod_gzip and mod_expires correctly configured performed significantly better than an OHS server without those two modules enabled.

You saw in the section on the OHS how we defined the DAD in the dads.conf file. So how would you achieve the same functionality using the EPG? How could you, for example, provide different dads to your end users? Also, how can you view and change the parameters for the EPG?

Well, most of the configuration is done using the DBMS_EPG package, which contains a lot of useful helper routines, as shown in Listing 1-11.

SQL> desc dbms_epg;
PROCEDURE AUTHORIZE_DAD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DAD_NAME                       VARCHAR2                IN
 USER                           VARCHAR2                IN     DEFAULT
PROCEDURE CREATE_DAD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DAD_NAME                       VARCHAR2                IN
 PATH                           VARCHAR2                IN     DEFAULT
PROCEDURE DEAUTHORIZE_DAD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DAD_NAME                       VARCHAR2                IN
 USER                           VARCHAR2                IN     DEFAULT
PROCEDURE DELETE_DAD_ATTRIBUTE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DAD_NAME                       VARCHAR2                IN
 ATTR_NAME                      VARCHAR2                IN
PROCEDURE DELETE_GLOBAL_ATTRIBUTE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ATTR_NAME                      VARCHAR2                IN
PROCEDURE DROP_DAD
 Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ --------
 DAD_NAME                       VARCHAR2                IN
PROCEDURE GET_ALL_DAD_ATTRIBUTES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DAD_NAME                       VARCHAR2                IN
 ATTR_NAMES                     TABLE OF VARCHAR2(4000) OUT
 ATTR_VALUES                    TABLE OF VARCHAR2(4000) OUT
PROCEDURE GET_ALL_DAD_MAPPINGS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DAD_NAME                       VARCHAR2                IN
 PATHS                          TABLE OF VARCHAR2(4000) OUT
PROCEDURE GET_ALL_GLOBAL_ATTRIBUTES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ATTR_NAMES                     TABLE OF VARCHAR2(4000) OUT
 ATTR_VALUES                    TABLE OF VARCHAR2(4000) OUT
FUNCTION GET_DAD_ATTRIBUTE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DAD_NAME                       VARCHAR2                IN
 ATTR_NAME                      VARCHAR2                IN
PROCEDURE GET_DAD_LIST
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DAD_NAMES                      TABLE OF VARCHAR2(4000) OUT
FUNCTION GET_GLOBAL_ATTRIBUTE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ATTR_NAME                      VARCHAR2                IN
PROCEDURE MAP_DAD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DAD_NAME                       VARCHAR2                IN
 PATH                           VARCHAR2                IN
PROCEDURE SET_DAD_ATTRIBUTE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DAD_NAME                       VARCHAR2                IN
 ATTR_NAME                      VARCHAR2                IN
 ATTR_VALUE                     VARCHAR2                IN
PROCEDURE SET_GLOBAL_ATTRIBUTE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ATTR_NAME                      VARCHAR2                IN
 ATTR_VALUE                     VARCHAR2                IN
PROCEDURE UNMAP_DAD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DAD_NAME                       VARCHAR2                IN
 PATH                           VARCHAR2                IN     DEFAULT

So to obtain a list of all the current DAD configuration we can run a block of code like:

SQL> set serveroutput on;
SQL> exec dbms_output.enable(1000000);

PL/SQL procedure successfully completed.

SQL> declare
  l_dads dbms_epg.varchar2_table;
begin
  dbms_epg.get_dad_list(l_dads);

  dbms_output.put_line('DADS defined: ' || l_dads.count);

  for i in 1..l_dads.count loop
    dbms_output.put_line('DAD: ' || l_dads(i));
  end loop;
end;
/

DADS defined: 1
DAD: APEX

PL/SQL procedure successfully completed.

Now let's say we want to view the attributes for the DAD, we can run the following code:

declare
 name_list dbms_epg.varchar2_table;
 vals_list dbms_epg.varchar2_table;
begin
  dbms_epg.get_all_dad_attributes('APEX', name_list, vals_list);

  for i in 1..name_list.count loop
    dbms_output.put_line(name_list(i) || ' = ' || vals_list(i));
  end loop;
end;

This produces output similar to

database-username = ANONYMOUS
default-page = apex
document-table-name = wwv_flow_file_objects$
document-path = docs
document-procedure = wwv_flow_file_mgr.process_download
nls-language = american_america.al32utf8
request-validation-function = wwv_flow_epg_include_modules.authorize

Notice how the settings are very similar to the ones listed in the dads.conf file for the OHS. There is actually an extremely useful (yet little-publicized) script called epgstat.sql which should be in $ORACLE_HOME/rdbms/admin which you can run to get some great diagnostic output on the configuration and status of your EPG, shown in Listing 1-12.

system@ae1> @?/rdbms/admin/epgstat
+--------------------------------------+
| XDB protocol ports:                  |
|  XDB is listening for the protocol   |
|  when the protocol port is non-zero. |
+--------------------------------------+

HTTP Port FTP Port
--------- --------
     8080        0

1 row selected.

+---------------------------+
| DAD virtual-path mappings |
+---------------------------+

Virtual Path                     DAD Name
-------------------------------- --------------------------------
/apex/*                          APEX

1 row selected.

+----------------+
| DAD attributes |
+----------------+

DAD Name     DAD Param                DAD Value
------------ ------------------------ ----------------------------------------
APEX         database-username        ANONYMOUS
             default-page             apex
             document-table-name      wwv_flow_file_objects$
             request-validation-funct wwv_flow_epg_include_modules.authorize
             ion

             document-procedure       wwv_flow_file_mgr.process_download
             nls-language             american_america.al32utf8
             document-path            docs

7 rows selected.

+---------------------------------------------------+
| DAD authorization:                                |
|  To use static authentication of a user in a DAD, |
|  the DAD must be authorized for the user.         |
+---------------------------------------------------+

no rows selected

+----------------------------+
| DAD authentication schemes |
+----------------------------+

DAD Name             User Name                        Auth Scheme
-------------------- -------------------------------- ------------------
APEX                 ANONYMOUS                        Anonymous

1 row selected.
+--------------------------------------------------------+
| ANONYMOUS user status:                                 |
|  To use static or anonymous authentication in any DAD, |
|  the ANONYMOUS account must be unlocked.               |
+--------------------------------------------------------+

Database User   Status
--------------- --------------------
ANONYMOUS       EXPIRED

1 row selected.

+-------------------------------------------------------------------+
| ANONYMOUS access to XDB repository:                               |
|  To allow public access to XDB repository without authentication, |
|  ANONYMOUS access to the repository must be allowed.              |
+-------------------------------------------------------------------+

Allow repository anonymous access?
----------------------------------
false

1 row selected.

From time to time you might experience issues with the EPG, particularly if you are trying to access a page and all the browser returns is a generic HTTP-404 type error. With the OHS you can look in the Apache log files for more information, but with the EPG there are no text log files available. Fortunately, my friend Dietmar Aust found a great solution to this, which he was happy to permit me to include in this chapter. (Thanks, Dietmar!) Dietmar's original posting on the topic is available at:

http://daust.blogspot.com/2008/04/troubleshooting-404-not-found-error-on.html

Basically what we need to do is set the logging level with the command

system@ae1>execute dbms_epg.set_global_attribute('log-level', 3);

where the log level can be one of

0 - LOG_EMERG
1 - LOG_ALERT
2 - LOG_CRIT
3 - LOG_ERR

4 - LOG_WARNING
5 - LOG_NOTICE
6 - LOG_INFO
7 - LOG_DEBUG

Then we need to enable error logging in our DAD:

system@ae1>exec dbms_epg.set_dad_attribute('APEX', 'error-style', 'DebugStyle'),

If we now visit a URL which has an issue (such as a nonexistent page), we will get more debugging information, as shown in Figure 1-22.

Figure 1-22. Extra debug information using EPG logging

Now while this extra debugging information is not going to "auto-magically" solve all of your problems, it is certainly more helpful than just a standard HTTP-404 page which gives you next to no useful information.

Once you have resolved the issue, you can turn off the extra debugging information by issuing the command

system@ae1> exec dbms_epg.delete_dad_attribute('APEX', 'error-style')

You saw earlier in the chapter how you could configure the OHS to configure virtual hosts and Apache rewrites which allowed you to give your end users a more "friendly" URL to use. So, can you do that with the EPG? Well, until fairly recently I didn't think it was possible at all. However, following a conversation with my friend, Tim Hall, who runs the fantastic and highly informative Oracle Base website at http://www.oracle-base.com, it turns out that you actually can achieve a similar result. I asked Tim if I could show a technique here based on his examples and he agreed. (Thanks, Tim!) Note I say "similar result" since it is not true virtual hosting, although it does let you do some quite cool things.

So, first, we need to create a new DAD entry. You could apply this technique to the existing APEX DAD, but I prefer not to tinker with that and instead create my own custom DAD. This needs to be done as a DBA-level user:

BEGIN
  DBMS_EPG.create_dad (
    dad_name => 'training',
    path     => '/training/*'),
END;
/

BEGIN
  DBMS_EPG.authorize_dad (
    dad_name => 'training',
    user     => 'TRAINING'),
END;
/

Here we create a new DAD called training, which will be referenced by /training/* in the URL. Then we authorize the dad to link it to the TRAINING database user/schema.

The next step is to make use of a couple of DAD attributes called path-alias and path-alias-procedure. The path-alias attribute allows us to add a new URL /training/rs (the "rs" does not refer to anything in particular; I could have named it anything, or indeed used the root URL).

BEGIN
  DBMS_EPG.set_dad_attribute(
    dad_name   => 'training',
    attr_name  => 'path-alias',
    attr_value => 'rs'),

  DBMS_EPG.set_dad_attribute(
    dad_name   => 'training',
    attr_name  => 'path-alias-procedure',

attr_value => 'handle_request'),
END;
/

The path-alias-procedure attribute is the interesting bit. This allows us to define the name of a procedure which will be called whenever we reference the /training/rs URL—in this case we want it to call a procedure called handle_request which will be in the TRAINING schema, since that was the schema we authorized in the DAD.

The final step is to define the handle_request procedure:

CREATE OR REPLACE PROCEDURE handle_request(p_path IN VARCHAR2) IS
  l_path_arr  apex_application_global.vc_arr2;
  l_path   VARCHAR2(32767);
  l_id        VARCHAR2(32767) := NULL;
BEGIN
  l_path_arr := apex_util.string_to_table(p_path || '/', '/'),
  l_path  := l_path_arr(1);
  l_id       := l_path_arr(2);

  CASE LOWER(l_path)
  WHEN 'foo' THEN
    htp.p('You did FOO!'),
  WHEN 'google' THEN
    owa_util.redirect_url('http://www.google.com'),
  WHEN 'sales' THEN
    owa_util.redirect_url('/apex/f?p=SALES:HOME:0'),
  ELSE
    HTP.Print('Page not found.'),
  END CASE;
END handle_request;

This is quite a simple example; your real procedure would most likely be more complex. The key things to note are that at the beginning of the procedure we break the incoming path into the component parts, using the apex_util.string_to_table command, so that we can compare them. Then using a simple case statement we can perform specific actions based on what the incoming URL was. For example:

Figure 1-23. Calling the http://yourserver/training/rs/foo URL

Hopefully this example shows you are only limited by your imagination and that you can achieve a very similar functionality to the OHS Virtual Host, albeit in a slightly different way. You could, for example

If you need to clean up from the previous example, you can run the following code:

BEGIN
  DBMS_EPG.delete_dad_attribute(
    dad_name   => 'training',
    attr_name  => 'path-alias'),

  DBMS_EPG.delete_dad_attribute(
    dad_name   => 'training',
    attr_name  => 'path-alias-procedure'),

  DBMS_EPG.DEAUTHORIZE_DAD (
   'training',
   'TRAINING'),

  DBMS_EPG.drop_dad (
    dad_name => 'training'),
END;
/

To show how easy it is to get up and running with the APEX Listener, I want to show you the stand-alone installation. In stand-alone mode you get slightly less control over manageability, since you do not have a full application server behind you, but it is a very quick way to get up and running, which might be appropriate for development or demo environment, where you do not necessarily need the full degree of control gained by installing WLS or Glassfish.

The first thing you need to do is download the APEX Listener, which you can download from

http://www.oracle.com/technetwork/developer-tools/apex-listener/downloads/index.html
[oracle@ae1 listener]$ ls -al
total 10448
drwxr-xr-x 2 oracle dba        4096 Mar 3 12:38 .
drwx------ 6 oracle oracle     4096 Mar 3 12:38 ..
-rw-r--r-- 1 oracle dba    10671063 Mar 3 12:38 apex_listener.1.1.0.60.10.38.zip
[oracle@ae1 listener]$ du -sh apex_listener.1.1.0.60.10.38.zip
11M     apex_listener.1.1.0.60.10.38.zip

You can see that the download is actually quite small—only 11MB, compared with the (hundreds of MBs) OHS. When you unzip the zip file, you will find that some of that 11MB is actually documentation. This is one of the main reasons I love the APEX Listener: it is extremely compact.

[oracle@ae1 listener]$ ls -al
total 20072
drwxr-xr-x 4 oracle dba        4096 Mar  3 12:40 .
drwx------ 6 oracle oracle     4096 Mar  3 12:38 ..
-rw-r--r-- 1 oracle dba        2153 Mar  1 10:44 apex-config.xml
-rw-r--r-- 1 oracle dba    10671063 Mar  3 12:38 apex_listener.1.1.0.60.10.38.zip
-rw-r--r-- 1 oracle dba     9789612 Mar  1 10:44 apex.war
drwxr-xr-x 5 oracle dba        4096 Mar  1 10:44 docs
-rw-r--r-- 1 oracle dba        2846 Mar  1 10:44 index.htm
drwxr-xr-x 4 oracle dba        4096 Mar  1 10:44 javadoc
-rw-r--r-- 1 oracle dba       24890 Mar  1 10:44 license.html

Notice the apex.war file that is the APEX Listener. We can start the APEX Listener by executing the following command:

[oracle@ae1 listener]$ java -jar apex.war

When you run this you should see a large amount of output, and you will be prompted to specify where the APEX static files are located so the APEX Listener knows where to reference them:

[oracle@ae1 listener]$ java -jar apex.war
INFO: Starting: /Users/jes/listener/apex.war
 See: 'java -jar apex.war --help' for full range of configuration options
INFO: Extracting to: /var/folders/mN/mNPteN52HoqHK2miZ4o6IU+++TU/-Tmp-/apex
Enter the path to the directory containing the APEX static resources
          Example:  /Users/myuser/apex/images
         or press Enter to skip: /files/apex4
INFO: Using classpath: ...<output omitted>...
INFO: Starting Embedded Web Container in: /var/folders/mN/mNPteN52HoqHK2miZ4o6IU+++TU/

-Tmp-/apex
Enter a username for the APEX Listener Administrator [adminlistener]:
Enter a password for adminlistener:
Confirm password for adminlistener:
Enter a username for the APEX Listener Manager [managerlistener]:
Enter a password for managerlistener:
Confirm password for managerlistener:
Mar 19, 2011 1:02:12 PM ____bootstrap.Deployer deploy
INFO: Will deploy application path=/var/folders/mN/mNPteN52HoqHK2miZ4o6IU+++TU/

-Tmp-/apex/apex/WEB-INF/web.xml
Mar 19, 2011 1:02:13 PM ____bootstrap.Deployer deploy
INFO: deployed application path=/var/folders/mN/mNPteN52HoqHK2miZ4o6IU+++TU/

-Tmp-/apex/apex/WEB-INF/web.xml
Using config file: /var/folders/mN/mNPteN52HoqHK2miZ4o6IU+++TU/-Tmp-/apex/apex-config.xml
APEX Listener version : 1.1.0.60.10.38
APEX Listener server info: Grizzly/1.9.18-o
Mar 19, 2011 1:02:13 PM com.sun.grizzly.Controller logVersion
INFO: Starting Grizzly Framework 1.9.18-o - Sat Mar 19 13:02:13 GMT 2011
INFO: Please complete configuration at: http://localhost:8080/apex/listenerConfigure
Database connection not yet configured

I have highlighted in bold the sections where you are prompted for information. Other than defining the path and passwords, there is very little configuration to do at this stage. You should then find that the APEX Listener fires up your browser automatically and navigates you to the URL to configure it. If this doesn't happen automatically, then notice at the end of the output it tells you the URL you can use to configure the Listener.

In your browser you should see a screen much like Figure 1-24.

Figure 1-24. APEX Listener configuration screen

Note that whether you have deployed the APEX Listener via Web Logic Server, Oracle Glassfish, or OC4J you would still need to perform this configuration step.

So now you need to fill out the relevant details, specifying the password for APEX_PUBLIC_USER and also the hostname and SID or service name. You may also need to modify the port number if you are using a nonstandard port for the Oracle Listener (note: Oracle Listener, not APEX Listener—this could get confusing, right?!).

Figure 1-25 shows the new screen configured with the correct settings for this environment.

Figure 1-25. APEX Listener configured with connection parameters

If you apply those changes you should (hopefully) be redirected to the APEX login screen, as shown in Figure 1-26.

Figure 1-26. Accessing APEX via the APEX Listener

Notice in Figure 1-26 that the URL should be the location and port that the APEX Listener is configured on. I've seen situations where people thought they were having problems with the APEX Listener when they were actually connecting via the EPG, so any settings they were making in the APEX Listener did not appear to be picked up.

So, great, we are up and running! Now let us go back and examine some of the other options we could have configured in the APEX Listener. Now you might expect that you could just reenter the URL you used before to get back into the configuration. But if you enter the same URL, for example,

http://localhost:8080/apex/listenerConfigure

you will see a message:

"The APEX Listener is already configured.  Please login as Administrator to access APEX
Listener Administration."

This is to prevent anyone else from going into the configuration screen after you have configured it and making changes. After the initial installation the URL you need to use to administer the APEX Listener is

http://localhost:8080/apex/listenerAdmin

(Obviously change the hostname and port for your environment). You will be prompted for your username and password that you used when you started up the APEX Listener, as shown in Figure 1-27.

Figure 1-27. Accessing the listenerAdmin URL.

Here you can use the username adminlistener and the same password you provided during the APEX Listener startup. Once you log in successfully, you will see the same configuration screen you saw earlier.

Each of the configuration tabs covers a different area of functionality. For example, in the Security tab you can define the names of any procedures you want to allow or disallow to be accessed directly through the URL. In Figure 1-28, we have defined in the Inclusion List that we are allowed to directly access any procedures, packages, or functions in the training schema, since we have used the wildcard entry training.*. Note that this is probably a very bad idea, but it demonstrates the flexibility you have when combining the Inclusion and Exclusion list to get very granular controls over the permissions.

Figure 1-28. Allowing direct access to procedures

We can go one step further and, rather than statically defining the Inclusion and Exclusion list, we can dynamically check whether the URL is allowed or not by defining a validation function, as shown in Figure 1-29.

Figure 1-29. Defining a Validation function

Here we define a function that is called which returns true if the procedure is allowed or false if it should be disallowed. Note that the signature of the function should follow the format

CREATE OR REPLACE
  FUNCTION is_procedure_allowed(
      Procedure_Name IN VARCHAR2)
    RETURN BOOLEAN
  AS
  BEGIN
    IF (UPPER(Procedure_Name) LIKE ('%ADMIN%')) THEN
      RETURN false;
    ELSE
      RETURN true;
    END IF;
  END is_procedure_allowed;

In this example, if the procedure name contains the text "ADMIN" then we disallow the call; otherwise, it is permitted. Obviously this is a very simple example; you could adapt it to take the time of day into account, or the referring page, etc.

There is a slight overhead in calling a validation function every time, so the APEX Listener allows you to define a cache to store the result of previous invocations of the validation function. You can define in the security settings section how many cache entries you wish to store and you can also purge the results (if, for example, you modified the function, you might wish to make sure the new results were picked up), as shown in Figure 1-30.

Figure 1-30. Defining security cache settings

The APEX Listener also allows you to define caching rules for any procedures that produce content, as Figure 1-31 shows. For example, you might have a routine that retrieves a previously uploaded PDF file and downloads it to the user's browser. In this case, it might be useful to avoid incurring the database hit to retrieve the PDF file and instead issue it from the cache in the APEX Listener (this kind of caching could have huge benefits if your application is used by a large number of users since it reduces the overhead on your database).

Figure 1-31. Defining file caching settings

You can see in Figure 1-31 that you can define the procedure names that you wish to provide caching for, as well as the caching rules; for example, the number of minutes, hours, or days that you wish to cache the contents for or, alternatively, the total number of cached files you wish to maintain.

Another potentially useful setting is the Pre and Post Processing Procedures option, as shown in Figure 1-32, which allows you to define procedures that are executed before and after the URL request.

Figure 1-32. Pre and Post Processing option

This allows you to do things like do custom logging about the URLs being called and the usage of your application. This kind of functionality is possible using the OHS and EPG, but is not quite as user-friendly to implement as it is in the APEX Listener.

You can also view the Status tab to view statistics such as uptime, number of accesses, traffic transmitted, etc., as well as errors and logging information. Figure 1-33 shows how easy it is to get an instant overview of the status of your APEX Listener configuration.

Figure 1-33. Viewing Status information

Finally, the Miscellaneous tab (Figure 1-34) contains some very useful functionality to enable displaying debugging and error messages directly in the browser, as well as maintaining the error and logging information.

Figure 1-34. Miscellaneous tab

Notice in Figure 1-34 how much easier it is to enable the debugging and error message compared to what you had to do to achieve the same result for the EPG. Even as a command-line guy myself, I do appreciate that some things are much easier to do via a GUI interface rather than having to try and remember the exact commands.

I thought long and hard about whether to do a full demonstration of deploying the APEX Listener via Oracle Glassfish or Web Logic Server. I decided against it for a couple of reasons:

So I hope, dear reader, that you don't feel shortchanged that I didn't cover that in this chapter. Please be assured that the deployment is pretty painless and the configuration of the APEX Listener itself is done using the same exact process as just described.

For a full description of how to install under these servers, you can reference the APEX Listener installation guide available at

http://www.oracle.com/technetwork/developer-tools/apex-listener/documentation/index.html

If you use YSlow to examine an APEX page while accessing it through the APEX Listener, you will see that, as shown in Figure 1-35, in a standalone deployment, by default, the resources are not compressed, nor do they have expiry headers associated with them.

Figure 1-35. Examining APEX Listener with YSlow

So, what are the options? Well, it appears that the APEX listener does support compression, there is just not currently an option in the configuration screens to enable it. You need to drop down to editing the configuration file manually to do that.

What I typically do is use the previous process to fire up the APEX Listener and quickly configure it. Once I'm happy, I copy the configuration file it created and store it somewhere locally. If you didn't notice, when you started up the APEX Listener it created the configuration directory in a "random" directory (the directory used will depend on your environment and may not match mine). In my case it was

INFO: Extracting to: /var/folders/mN/mNPteN52HoqHK2miZ4o6IU+++TU/-Tmp-/apex

By looking at the output in your startup you should be able to navigate to that directly and find the apex-config.xml file:

[jes@ae1 ~]$ cd /var/folders/mN/mNPteN52HoqHK2miZ4o6IU+++TU/-Tmp-/apex
[jes@ae1 apex]$ ls -al
total 24
drwxr-xr-x   7 jes  staff   238 19 Feb 13:39 .
drwx------  10 jes  staff   340 19 Feb 14:53 ..
drwxr-xr-x   7 jes  staff   238 19 Feb 12:49 apex
-rw-r--r--  1 jes  staff  2727 19 Feb 13:39 apex-config.xml
-rw-r--r--   1 jes  staff    63 19 Feb 13:01 apex.properties
drwxr-xr-x   6 jes  staff   204 19 Feb 13:02 bdb
-rw-r--r--   1 jes  staff   258 19 Feb 13:02 credentials

So now you can copy that apex-config.xml file to the same directory as the apex.war file (or anywhere else you prefer). If you take a look at the apex-config.xml file you'll see there are a lot of options, as shown in Listing 1-13.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>

<comment> Updated: Sat Feb 19 13:39:12 GMT 2011  Version: 1.1.0.60.10.38 </comment>

<entry key="apex.db.username">APEX_PUBLIC_USER</entry>
<entry key="apex.db.password">@054C5980900B92C337F9317B6A19E3FB39F3A8403</entry>
<entry key="apex.db.connectionType">basic</entry>
<entry key="apex.db.hostname">ae1</entry>
<entry key="apex.db.port">1521</entry>
<entry key="apex.db.sid">dbtest</entry>
<entry key="apex.db.servicename"></entry>
<entry key="apex.db.tnsAliasName"></entry>
<entry key="apex.db.tnsDirectory">/usr/local/oracle</entry>
<entry key="apex.db.customURL"></entry>
<entry key="apex.jdbc.DriverType">thin</entry>
<entry key="apex.jdbc.InitialLimit">3</entry>
<entry key="apex.jdbc.MinLimit">1</entry>
<entry key="apex.jdbc.MaxLimit">10</entry>
<entry key="apex.jdbc.MaxStatementsLimit">10</entry>
<entry key="apex.jdbc.InactivityTimeout">1800</entry>
<entry key="apex.jdbc.AbandonedConnectionTimeout">900</entry>
<entry key="apex.jdbc.MaxConnectionReuseCount">50000</entry>

<entry key="apex.jdbc.DriverType">thin</entry>
<entry key="apex.jdbc.InitialLimit">3</entry>
<entry key="apex.jdbc.MinLimit">1</entry>
<entry key="apex.jdbc.MaxLimit">10</entry>
<entry key="apex.jdbc.MaxStatementsLimit">10</entry>
<entry key="apex.jdbc.InactivityTimeout">1800</entry>
<entry key="apex.jdbc.AbandonedConnectionTimeout">900</entry>

<entry key="apex.security.inclusionList"></entry>
<entry key="apex.security.exclusionList"></entry>
<entry key="apex.security.disableDefaultExclusionList">false</entry>
<entry key="apex.security.requestValidationFunction"></entry>
<entry key="apex.security.maxEntries">2000</entry>
<entry key="apex.security.trustedProxies"></entry>

<entry key="apex.cache.caching">false</entry>
<entry key="apex.cache.procedureNameList"></entry>
<entry key="apex.cache.type">lru</entry>
<entry key="apex.cache.maxEntries">500</entry>
<entry key="apex.cache.expiration">7</entry>
<entry key="apex.cache.duration">days</entry>

<entry key="apex.cache.monitorInterval">60</entry>
<entry key="apex.cache.directory">/var/folders/mN/mNPteN52HoqHK2miZ4o6IU+++TU/

-Tmp-/apex/cache</entry>

<entry key="apex.procedure.preProcess"></entry>
<entry key="apex.procedure.postProcess"></entry>

<entry key="apex.misc.defaultPage">apex</entry>
<entry key="apex.misc.compress"></entry>
<entry key="apex.debug.debugger">false</entry>
<entry key="apex.debug.printDebugToScreen">false</entry>
<entry key="apex.error.keepErrorMessages">true</entry>
<entry key="apex.error.maxEntries">50</entry>

<entry key="apex.log.logging">false</entry>
<entry key="apex.log.maxEntries">50</entry>

</properties>

Now I'm not going to go through all the options here; many of them should be obvious (for example, the hostname and SID). This also illustrates how you can update the configuration directly without going via the GUI. (You could potentially even generate the configuration file automatically through a script.) The setting I am interested in here is

<entry key="apex.misc.compress"></entry>

We can change that to:

<entry key="apex.misc.compress">true</entry>

to enable compression.

Now you need to restart the APEX Listener, but this time you'll want it to use your current configuration file. You can do that by examining the options available with the apex.war file by providing the -help parameter:

[jes@ae1 listener]$ java -jar apex.war --help
java [options] -jar apex.war [--help]
 Options:
 -Dapex.home=/path/to/apex      : Path to the folder used to store the
                                  web container runtime, defaults to:
                                  ${java.io.tmpdir}/apex
 -Dapex.port=nnnn               : HTTP listen port, default 8080
 -Dapex.ajp=nnnn                : AJP (mod_jk) listen port, default none
                                  If an AJP Port is specified then HTTP access is disabled
 -Dapex.images=/images/location : Path to the folder containing static
                                  resources required by APEX
 -Dapex.erase=true              : Erase the contents of ${apex.home}
                                  before launching
 --help                         : Print this usage message

You should now be able to restart the Listener and confirm that compression is indeed occurring. Hopefully at a future time this option will be configurable through the GUI configuration itself.

If you are running the APEX Listener through Oracle Glassfish, rather than enabling compression in the APEX Listener itself, you could compress at the Glassfish level, as shown in Figure 1-36. A similar option exists within Web Logic Server.

Figure 1-36. Configuring HTTP compression within Glassfish

There are a couple of very useful features in the APEX Listener that I find very interesting in conjunction with APEX applications. These features are

The first feature is exactly what it sounds like: it allows you to upload an Excel document via the APEX Listener, but it goes further than that and allows the document to be parsed into an APEX collection. (If you're not familiar with APEX collections, you can consider them similar to a temporary table in Oracle which a user can consistently query from within their APEX session.) This functionality alone saves you a lot of work in APEX; I've lost count of the number of times over the years when I've ended up implementing this functionality myself in all the applications that need to allow users to upload an Excel document.

So how do you do it? Well, first of all, you need to configure some extra options in the apex-config.xml file for the APEX Listener to support the Excel uploads. You need to add these lines to the end of your configuration file if you don't already have them (these features are not available through the GUI configuration so you need to edit the file directly and restart the APEX Listener).

<entry key="apex.excel2collection.onecollection">true</entry>
<entry key="apex.excel2collection.name">EXCEL_COLLECTION</entry>
<entry key="apex.excel2collection.useSheetName">true</entry>
<entry key="apex.excel2collection">true</entry>

The settings I have made here mean that the Excel file will be placed into an APEX collection called EXCEL_COLLECTION (I could have used any name); also, the parsing routine will use the worksheet name rather than a generic iterative name.

Now you can create a simple APEX application to demonstrate how to perform the upload. First, you need to add a File Upload item, as shown in Figure 1-37.

Figure 1-37. File upload item

Notice that I have chosen Table WWV_FLOW_FILES as the storage type, since I am not really interested in storing the uploaded file in my own table in this demo (it simplifies the example since I don't need to create my own table, etc.).

The next step is to add a button to submit the page, thereby uploading the file, as shown in Figure 1-38.

Figure 1-38. Button with request value XLS2COLLECTION

The important thing to note here is that I have given the button a request value of XLS2COLLECTION, which is what will trigger the Excel parsing in the APEX Listener during the upload. The final step is to create a report on the page that will display the data in the collection. The report will use the following SQL query:

select *
from apex_collections
where collection_name = 'P1_FILE'

When you run the application and upload an Excel document, you should find that the contents of the worksheets are parsed and visible in the report, as shown in Figure 1-39.

Figure 1-39. Uploaded Excel file parsed into a collection

Notice how the worksheet name has been copied into the collection column C001 and we know the line number in the Excel document from the SEQ_ID column.

If you have ever had to provide this kind of upload functionality in your applications, I am sure you appreciate just how useful this feature can be. In the past we have asked clients to provide data in comma separated value format rather than native Excel, but having this kind of native Excel integration means one less step for people to perform when uploading data. This feature alone could be reason enough to convince some people to use the APEX Listener over the alternatives.

The second feature I want to discuss is resource templates. So what are resource templates? They are a feature that allows you to provide a REST interface to your application to the outside world. If you're not familiar with REST, it stands for Representational State Transfer, essentially a web service. This allows other (even non-APEX) systems to interface with your system. Oracle Application Express has for a long time now been able to consume remote web services, but it has not been easy to publish web services without resorting to Java (which kind of defeats the purpose of developing in APEX). However, by using the APEX Listener you can quite easily provide these REST interfaces that can be called from remote systems, or perhaps even your own APEX systems.

So what can you do with resource templates? Well, they allow you to define the REST interface itself in terms of the URI (Uniform Resource Identifier) that the REST interface will be called from and to link that to an underlying SQL query or PL/SQL block of code.

The easiest way to visualize this is with a quick example. You can edit the resource templates in the APEX Listener using the URL

http://localhost:8080/apex/resourceTemplates

changing the hostname and port number to suit your environment.

You can see in Figure 1-40 that you can also access the Resource Templates editor via the regular administration pages by clicking the Resource Templates link.

Figure 1-40. Accessing the Resource Templates editor

If you click the Add Resource Template button you will see that it creates a sample template for you, as shown in Figure 1-41.

Figure 1-41. Sample resource template

You can see from the example that the SQL query

select 'Hello ' || :person || ' from APEX' greeting from dual

contains :person, which looks like a bind variable in Oracle. It is, however, actually the way that you define any parameters that will be passed in the interface. You can also see that the URI Template value is

hello?who={person}

which means that the interface can be accessed via a URL using the following syntax:

http://localhost:8080/apex/hello?who=World

Notice that the {person} parameter in the URI Template maps to the :person value in the SQL query.

If you accept the example Resource Template, you should see it listed in the available Resource Templates, as shown in Figure 1-42.

Figure 1-42. Available Resource Templates

If you now call this URI directly in the browser you will see that behind the scenes the APEX Listener executes the SQL Query and returns the output to the browser as a JSON (JavaScript Object Notation) string, as shown in Figure 1-43.

Figure 1-43. Calling the REST URI and getting JSON response back

The JSON output is shown below:

{
    "items":
    [
        {
            "greeting":"Hello john from APEX"
        }
    ]
}

Notice how the alias from our query is used.

You can actually choose to return CSV format rather than JSON format, or define that the result will be generated via block of PL/SQL code or Media Resource rather than a SQL query, as shown in Figure 1-44.

Figure 1-44. Defining the Resource Template type

you could instead return a single record result. Consider the query:

select * from training.emp where empno =:empno

You can create a new Resource Template based on this query, as shown in Figure 1-45.

Figure 1-45. Defining a Resource Template with a single result

If we call the URI passing in an employee number we know exists (since we know the EMP table off-by-heart!), we can see output similar to Figure 1-46.

Figure 1-46. Single employee record returned

You could extend this example to return all the records if no employee number is passed in, but hopefully you can see just how useful this might be. You can now easily have external systems calling these URIs to obtain data directly from your system. Alternatively, they can also post information into the system by passing data as part of the call. For example, you could have captured the passed-in employee number and stored it locally (or any number of other parameters that were passed in).

The APEX Listener installation document has a number of examples that are quite interesting to look at;for example, there is an Image Gallery example which allows you to upload images and view thumbnails of the images. I highly encourage you to check out that document to see how easily you can use a Resource Template to perform some useful functionality!