Oracle on the Web - Part 3 - Using Applications in HTML DB

Wednesday Jun 8th 2005 by Steve Callan

In the first two parts of this series, we looked at the basic HTTP Server behind HTML DB (and Application Server) and the basic installation and setup of HTML DB itself. In this article, we will look at a simple example of adding a page to an existing application and detail some of the steps involved.

In the first two parts of this series, we looked at the basic HTTP Server behind HTML DB (and Application Server) and the basic installation and setup of HTML DB itself. In this article, we will look at a simple example of adding a page to an existing application and detail some of the steps involved. Before moving on to the example, I would like to clarify a few points about the HTTP Server and HTML DB.

What Oracle's HTTP Server Is and Is Not

As mentioned in several places in the official documentation, Oracle HTTP Server (OHS) is based on a version of the Apache Web server (1.3.28, to be exact). As of the date of this article's publication (June 2005), the current release of Apache is 2.0.54. It should be somewhat apparent that the difference in versions is significant. There are other Oracle products that use a later version of OHS. Application Server 10g, for example, uses a release from the 2.0 series. What are the ramifications of using Apache 1.3.28?

Take a quick tour of the Apache Web site and read the high-level release notes. Start here - http://httpd.apache.org/ - and follow the links to the announcements for 1.3.33 and 2.0.54. The 1.3.33 notes state in part:

We consider Apache 1.3.33 to be the best version of Apache 1.3 available and we strongly recommend that users of older versions, especially of the 1.1.x and 1.2.x family, upgrade as soon as possible.

Some of the major components of OHS (again, based on Apache) are the Listener and modules.

  • HTTP Listener: Oracle HTTP Server is based on an Apache HTTP listener to serve client requests. An HTTP server listener handles incoming requests and routes them to the appropriate processing utility.

  • Modules (mods): Many of the standard Apache modules are included with Oracle HTTP Server. Oracle also includes several internal modules that are specific to Oracle Database components.

One module in particular is of key importance for Oracle and that is the mod_plsql module. This module is Oracle-specific and is not part of the generic version of Apache you get from the Apache Software Foundation (ASF). The Oracle® HTTP Server Administrator's Guide lists the modules Oracle includes and the ones from ASF Oracle does not support. The implications are that if there is a problem with an Oracle-supplied module, there may not be much help or support available from the Apache user group mailing lists, and there may be "normal" modules from Apache that are unavailable to you. HTML DB uses mod_plsql (that is what the "pls" represents in the URI you use to login to a workspace or the admin area).

A third major point is that of controlling the Apache part of OHS. In a normal Apache HTTP Server installation/environment, you can use apachectl to start and stop the server, and on Windows, you can start/stop the server via services. OHS, however, requires that you use OPMN.

Oracle HTTP Server is managed by Oracle Process Manager and Notification Server (OPMN). You must always use the opmnctl utility to start, stop and restart Oracle HTTP Server. Otherwise, the configuration management infrastructure cannot detect or communicate with the Oracle HTTP Server processes, and problems may occur.

There are some configuration modifications normally available with Apache that OHS does support. Path aliasing and logging options are two such examples. However, for the most part, what you see is what you get when it comes to OHS, so it is kind of a trade-off. If you do not know much about setting up a server, Oracle does virtually all of the work for you. If you do know a lot and like to customize the settings, you may be out of luck because of the incompleteness of the Apache components.

HTML DB Versions and Examples

The version of HTML DB used so far is in the 1.5 series. If you look closely on the generated Web page, you can see the exact version.

A newer version (1.6) of HTML DB is available at OTN and it includes several enhancements over 1.5. More information (aside from what comes in the documentation) is available at http://www.oracle.com/technology/products/database/htmldb/index.html. The "obe" username (from before) comes from the Oracle By Example help/demo area at OTN. You can also request a workspace (hosted by Oracle) instead of installing and using HTML DB on your own computer.

Oracle Corporation has put a lot of development effort into HTML DB, and one of the most visible or public uses of it can be found at the Ask Tom Web site.

As a DBA responsible for maintaining clients and supporting developers who use Forms and Reports, here are two reasons why you should be very interested in adding HTML DB DBA to your repertoire.

First, because any (authorized) user can access the database via a browser, you may be able to say goodbye to the client software installation and its attendant maintenance in your organization. If you have users who are primarily view only, you can easily create a collection and have those users browse to obtain what it is they are looking for.

Second, Forms and Reports can be (to a large degree) developed and used via HTML DB - all without the overhead of using actual Forms or Reports files. For users with simple needs such as performing DML directly on a few tables, you do not need the overhead of Application Server and Developer Suite. The example shown in the next part does exactly that - it uses a simple tabular form that is easily customizable and takes around three minutes to create.

Using an HTML DB Demonstration Application to Create a Form

Login to a created workspace and navigate to the Review Demonstration Applications in the Task area (bottom right).

Note: I am using the "ADMIN" user in the OBE workspace from the last article.

Click Install to install the Collection showcase. Click Run to view the EMP collection.

In our example, we will add a page to this application. The page will be a tabular form whose end result will allow you to perform DML on an employees table.

Some preliminary steps

I used the EMPLOYEES table in the HR sample schema that comes with Oracle9i and later. You may have to unlock the HR account (and set a password). Login as HR and grant select on EMPLOYEES to PUBLIC (or the user OBE). Login as OBE and create a table named HR_EMPLOYEES using CTAS (create table as select). The user OBE should have a table with 107 rows (it is a larger version of Scott's EMP table).

Back in HTML DB, navigate to the Home page and click the Build icon. In the list of available applications, select the Collection Showcase.

Click for larger image

To add a page to this application, click on the Wizards tab on the top of the page. At the next window, click Tabular Form in the Forms area.

Click for larger image

Select the OBE schema and click Next. The flowchart to the left shows where you are in the process.

At the next page, click on the flashlight or search button to see the available tables and views. Select the HR_EMPLOYEES table (it should be at the bottom of the list), and then click Next.

Select all of the columns.

At the next page, I allowed everything but the employee ID number and the hire date to be updateable. Select the EMPLOYEE_ID column for the primary key.

At this page, you have to select a display type. I chose the alternating colors (and changed the Region Title from Tabular Form to ACME Employees and added a different page name).

Skip the tabs page by clicking Next. At the Buttons Label page, "Cancel" for cancel and "Submit" for submit works for me.

At the Identify Branching page, the choices you make (which do not really matter in this example) are where you are sent after a successful submit or a failure. I chose pages 5 and 1. Click Next to move on to the Confirm page, and when finished reviewing the selections, click Finish.

Let's run the page and see what we get. Click Run Page.

You should see a page similar to what is shown below (the one below already reflects a change mentioned next).

Neena (employee 101) was married last week, and her new last name is Smith. The boxed fields are what can be updated, so enter the updates and click Submit. Depending on your branch page choice, you may not come back to this page. Regardless of the branching destination, you should see a confirmation of the change at the top of the destination window.

In Closing

Overall, creating the tabular form was not too painful. As you can see, the choices under the Wizards tab are fairly extensive. Like other Oracle applications, these wizards are meant to be run only once (modifications are made using other means).

The following wizards build complete page(s). These wizards are one-time generators. The wizards generate multiple attributes, for example, a report wizard generates one page, one region, and multiple buttons. After generation, page attributes can be customized to your exact needs.

In the next article, we will cover the steps to upgrade HTML DB to version 1.6 (including the patch), and look at creating a different web application and validate Mr. Kyte's claim that "It is just too easy." In the meantime, install the Web Service demo and note its similarity to the look and feel of the Ask Tom Web site.

» See All Articles by Columnist Steve Callan

Mobile Site | Full Site