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