Expert Oracle Application Express

611 Pages·2011·14.2 MB·English

by  Dietmar Aust

The eXperT’s Voice® in oracle

Companion
eBook Available
Expert
Expert
Oracle

Application Express

Thirteen leading lights in Oracle Application
Express share their favorite insights from the field
John Scott, Dietmar Aust, Martin D'Souza,
Doug Gault, Dimitri Gielis, Roel Hartman, Michael
Hichwa, Sharon Kennedy, Denes Kubicek,
Raj Mattamal, Dan McGhan, Francis
Mignault, and Anton Nielsen
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
 CONTENTS AT A GLANCE
Contents at a Glance
 Foreword..............................................................................................................xvi
 About the Authors...............................................................................................xxii
 About the Technical Reviewers.........................................................................xxvi
 Acknowledgments..............................................................................................xvii
 Chapter 1: OHS, EPG, and APEX Listener Compared..............................................1
 Chapter 2: Oracle APEX 4.0 Charts Inside Out......................................................73
 Chapter 3: Tabular Forms...................................................................................155
 Chapter 4: Team Development............................................................................211
 Chapter 5: Globalization .....................................................................................241
 Chapter 6: Debugging.........................................................................................271
 Chapter 7: Dynamic Actions...............................................................................309
 Chapter 8: Security.............................................................................................349
 Chapter 9: Lifecycle Management......................................................................377
 Chapter 10: Working with APEX Collections.......................................................423
 Chapter 11: Plug-Ins...........................................................................................441
 Chapter 12: Architecture....................................................................................515
 Chapter 13: Advanced Interactive Reporting......................................................533
 Index...................................................................................................................571
iv
C H A P T E R 1

  

OHS, EPG, and
APEX Listener Compared
By John Edward Scott
When I first started using APEX, I didn’t really have a choice when it came to deciding which web server
to use; in a similar vein to the often-quoted Henry Ford (“any colour as long as it’s black”), it was a case
of “Use any web server as long as it’s the OHS”. The OHS is the commonly used name for the Oracle
HTTP Server.
However, as with all things technology related, times change. Oracle 9i Release 2 brought XML DB
and with it an embedded web server. As of Oracle 10g Release 2, this embedded web server can be used
as an embedded PL/SQL gateway to run PL/SQL via a browser. It is commonly known as the EPG
(Embedded PL/SQL Gateway) and controlled via the DBMS_EPG package.
So, things were good: we now had a choice of the OHS and the EPG, both of which were officially
supported by Oracle. But, never being content to sit on their laurels, the Oracle team decided to give us a
third option, the APEX Listener, which is a J2EE alternative to the OHS. The APEX Listener was created to
explicitly fulfill the needs of the web server that has to sit between the web browser and your Oracle
APEX application, but it can also support many other configurations, since it can be deployed using
Oracle Web Logic Server (WLS), Oracle Glassfish Server, and OC4J.
Why Should I Care About This Chapter?
When I first came up with the idea for this book (and before I’d approached the other authors), I thought
a lot about what sort of chapter I’d write. There are so many areas of APEX that interest me, particularly
with the release of APEX 4.0, it seemed like an impossible choice. I learned with my first book, Pro Oracle
Application Express, that no matter what you write about, some people will love a chapter while others
won’t find it that relevant to them.
So, I thought to myself, what is the one thing that everyone who uses APEX has to use, yet probably
never gives a second thought to? The answer (of course!) is the web server aspect.
You might be thinking “Okay, but I’m going to skip this chapter, because that’s something my
System Admin takes care of.” Well, dear reader, please bear with me. As an APEX developer myself, I
know that there are things you can do with your web-server configuration that will really impact the
performance and scalability of your APEX applications (you should care about that!), and there are also
some really great features available in the web server than you can leverage in your applications (and
you should care about that!).
So, while at first glance this chapter might not seem as cool, sexy, or “APEX 4.0” as some of the other
chapters in this book, I hope that you will find some things that make you think or, even better, make
you use them!
1
CHAPTER 1  OHS, EPG, AND APEX LISTENER COMPARED
Now, it’s impossible for me to say “Always use XYZ, it is the best.” The truth is more likely that the
right choice probably depends on your exact requirements. In fact, this would be a very short discussion
indeed if I could just say that XYZ was always best in all circumstances. So, in this chapter, I will go
through some of the areas that I think are important for the web server. They will include the following:
• Installation: How quick and easy is it to get up and running? In some situations
you might not care about making it the most secure environment possible and
just want it up and running as fast as possible.
• Configuration: After the initial setup, how configurable is it? What sort of options
can I “tweak”?
• Extensibility: Does the web server offer any ways to extend it and add
functionality?
• Scalability and Performance: How well does the web server scale? If the number of
end users of my application grows will I need to add more web server or can it
scale up?
These are overarching concepts and I won’t rigidly stick to them, but they give you a general idea of
the sort of things I believe are important. Rather than jumping back and forth between the different
options (which could be confusing), I’m breaking the rest of the chapter into three parts to cover each of
the options and show the various possibilities and features provided by each of them.
Web Server Basics
If you have never looked at the Oracle APEX architecture before, it really is a pretty simple and yet
powerful architecture. In the case of the OHS, the web server sits between the web browser and the
database and is responsible for handling the requests from the web browser, passing them through to
the database (via something called mod_plsql), then APEX processes the request and generates the
response (the HTML code to send back) which is passed by to the browser via the mod_plsql module in
the OHS. Figure 1-1 illustrates this architecture.

Figure 1-1. The Oracle HTTP Server architecture
By contrast, when you use the EPG there is no “web server in the middle”; the web browser is
actually connecting directly to the database, as shown in Figure 1-2.
2
CHAPTER 1  OHS, EPG, AND APEX LISTENER COMPARED

Figure 1-2. Using the Embedded PL/SQL Gateway
This diagram often has DBAs raising their hands in shock and horror. However, there are examples
where this simplified architecture is desirable, which I’ll cover later in the chapter.
The Oracle HTTP Server (OHS)
The Oracle HTTP Server has been around for quite some time. In fact, I looked around to see if I could
find the first references to it but couldn’t find a definitive date for its first release, although I did find
references to it in the Oracle Database 8.1.7 release and the Oracle iAS (Oracle Internet Application
Server) software. So one way of looking at the OHS is that it’s a very tried and tested configuration. Of
course ,the other way of looking at it is that it’s pretty old (are you a glass half full or half empty person?).
So what is the Oracle HTTP Server? Simply put, it’s a web server that is based on the Apache HTTP
Server, which has been modified, tweaked, and optimized to connect to the Oracle Database via a
custom Apache Module (a plug-in) called mod_plsql. It is this mod_plsql module that enables a web
browser to directly access the data and code in the database.
When the OHS was first released, it was based on Apache version 1.3 and only relatively recently
have versions based on Apache version 2.0 been made available. The Apache release history is very
complex and full of intricacies, so it’s not as simple as saying “Ah, version 2.0 must be better than version
1.3”, you should think of version 1.3 and 2.0 as branches of the code and not that version 2.0 is
necessarily better than 1.3.
So, in summary, the OHS based on Apache 1.3 has been out a very long time and has been used in a
large number of situations and has proved to be a very stable web server (when configured correctly!).
The OHS based on Apache 2.0 is a much more recent release, but does offer some advantages over
version 1.3.
From my own personal experience, I have found the OHS based on Apache 1.3 very stable and have
used it many (many!) times; however, since using the OHS based on Apache 2.0, my preferred option is
now to use the 2.0 release over the 1.3 release (for reasons which I’ll explain later).
Installing the OHS
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.
3
CHAPTER 1  OHS, EPG, AND APEX LISTENER COMPARED
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.
Listing 1-1. OHS Installation File Downloaded and Unzipped
[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.
4
CHAPTER 1  OHS, EPG, AND APEX LISTENER COMPARED

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
5
CHAPTER 1  OHS, EPG, AND APEX LISTENER COMPARED
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:
• I want to minimize the chances that, if a malicious user compromises the OHS
web server, they only control the OHS Unix user, rather than the user that is
running the database.
• If for some reason there is an issue with the OHS—for example, the number of
open file inodes exceeds the limits (which might happen if the web server is hit by
a Denial of Service attack)— it only impacts the Unix user that the OHS is running
as and does not bring down the database.
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:
• Install the OHS on the same machine as the database and you’re covered by the
database license.
• Install the OHS on a machine other than the machine the database is installed on
and you will need to license it (the OHS) separately.
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.
6
CHAPTER 1  OHS, EPG, AND APEX LISTENER COMPARED

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:
• No additional licensing required
• Reduced network latency
Disadvantages to installing on the same machine are
• Out of control OHS process could affect the database (unless operating system
resource controls are used).
• If a hacker managed to exploit the OHS, they could control the database machine.
Advantages to installing on different machines are
• More secure configuration (better physical separation)
• Easier to scale out (add more web server layers)
7