Building Web Applications with Oracle HTML DB, Part 1

Today, an increasing number of enterprise applications are accessible through a Web browser. Once reserved for eCommerce and ASP (Application Service Provider) software solutions, Web-based applications have gone mainstream and have become widely accepted by businesses and their users. Despite the fact that a browser can only offer a subset of thick client functionality, the ease in which a Web application can be deployed, then accessed, provides an incentive too strong to be ignored. Further, with the advent of Web technologies such as DHTML and Flash, the functionality gap that exists between the browser and a desktop application is slowly closing.

Development languages used to construct Web applications include Microsoft Active Server Pages (ASP), PHP, and JavaServer Pages (JSP). These technologies are successors to the CGI-based applications from the early days of the Web. These technologies often provide a number of advantages that legacy cgi programs did not, including thread and session management.

Building applications with today’s Web development languages, while not terribly difficult to learn, is still a job for professional software developers. These developers must be comfortable with the fundamentals of programming and have an understanding of the inner workings of how a Web server handles client requests and generates responses. With Oracle 10g, Oracle provides a development platform called HTML DB that aims to simplify the task of developing and deploying a Web-enabled application that works with an Oracle database.

HTML DB’s TARGETED NICHE

Oracle refers to HTML DB as a ‘declarative development tool.’ For simple application requirements, there may very well be no coding necessary. The platform ships with a variety of wizards that allow end users to select database tables, and build forms and reports based upon those tables with a few simple mouse clicks.

HTML DB is ideal for two categories of staff. The first group is the non-technical user. As I mentioned, end users are perfectly capable of constructing a functional application without a single line of code. For more complex requirements, developers (or power users) with knowledge of Oracle SQL and PL/SQL can add a great deal of functionality to their applications, without learning a new language. In fact, from a purely functional aspect, HTML DB lets you do virtually anything you might do with JSP, ASP, or any other Web development language.

A key difference between an HTML DB application and most large-scale Web applications is how it handles internal program data. A language such as JSP will allow you to create an in-memory session object for each user, storing data such as a user’s password or the contents of a shopping cart. This session object keeps track of data until the user quits his or her browser session. A session object is needed due to the stateless nature of the Web. Without using some sort of session storing device, a server loses all knowledge of that user’s state once a response is generated. HTML DB solves this problem by storing all session data in the database. Each time a page is processed, a new database session is created and user data can be accessed. When the processing is complete, the database session is closed. This approach is convenient because an HTML DB developer does not have to manage session states themselves, which can be complex. However, this convenience comes at the expense of scalability. The Oracle database itself scales well if configured properly, but a site with heavy traffic will prove to be very taxing for an HTML DB application, given the need for constant application-to-database interaction. As a result, HTML DB is intended for workgroup and intranet applications with a low to moderate number of users. If you are planning a large scale eCommerce application, HTML DB is probably not the best choice.

HTML DB COMPONENTS

The HTML DB platform actually consists of a few different components.

• SQL Workshop: The SQL Workshop allows you to manipulate database objects through the browser. You can query, create, and modify tables, stored procedures, and more.
• Data Workshop: Data Workshop provides a way to import data into your database from files and spreadsheets, and then export that data from HTML DB if desired.
• Application Builder: Application Builder is the component to HTML DB that we will focus on. It is the Application Builder that allows you to quickly create Web-enabled applications that operate on an Oracle database. By using a wizard, an application can be generated in seconds. Once built, you can modify the application’s interface and logic can be added to process data, perform data validation, and branch to different pages within the application.

For the remainder of this article, we’ll step through building a data-driven application in HTML DB.

YOUR FIRST HTML DB APPLICATION

Oracle makes it very easy for you to experiment with HTML DB without actually installing the product. You can visit Oracle’s HTML DB site here and request a workspace. Once you receive your password, you can begin working with the suite of tools.

After logging on to your workspace, select Application Builder from the list of components. You will notice that your workspace comes with a sample application. This sample application is feature-rich and it can serve as a valuable point of reference. For our example, we will create a new application based upon an employee table provided in the database schema the accompanied our workspace.

• Click on the Create Application icon.
• Click the Based on Existing Tables option.
• Confirm you want to use the provided database schema by clicking Next.
• Select the table ‘Emp’ as the sole data source of our application.
• Click Next, then Finish to complete the process.

When complete, click Run Application. We have succeeded in creating a simple application based upon table Emp. The HTML DB application wizard generates a number of pages for us, including a login page, report page, a chart page, and a page with an enterable form for modifying employee data. Once you log in, you will be presented with the main screen shown in Figure 1 below. Now, take a few minutes to peruse the application. All of this was accomplished with just a few mouse clicks. Try clicking the edit icons aside each entry on the report page, and then modify a few form fields and save your changes. Visit the Analysis page where you have the ability to view employee statistics in chart format.

Figure 1: HTML DB Application Main Screen

Following this simple process, we could have selected numerous tables within our database schema and generated similar reports, charts, and forms for maintaining those tables. In many cases, this may be enough to satisfy the needs of a small departmental workgroup.

At the bottom of each page in your application, a set of links is displayed. These links provide quick access to the pages that allow you to edit your software. Included are links for viewing session data, setting debug features, and editing properties for the current page as well as the entire application. HTML DB has a number of predefined roles that you can assign to users. By default, when you create an HTML DB application, you are given the role of developer so these links automatically appear. End users will typically not see these links.

Each page in an HTML DB application is given a unique name with a number on the end. I renamed the page containing my report from ‘Page 2’ to ‘Report Page’. From here, I will click the ‘Edit Page’ link at the bottom. Figure 2 displays the configuration interface that appears.

Figure 2: HTML DB Edit Page Screen

As you can see, this page is divided into three categories:

• Page Rendering: Here, you edit page attributes that affect the rendered interface. HTML DB breaks pages into something called ‘Regions.’ A Region controls page layout and each Region typically manages one component like a form, report, or menu. You can add HTML markup to a Region. Also, you can add new items or buttons to a given page, then assign them to a particular Region for display.
• Page Processing: You can add processing logic to your HTML DB application to accomplish things such as validating data and computing values for certain fields. This logic would be invoked when actions occur against the page, such as when a user clicks a button or submits an input form.
• Shared Components: Pages can share a number of components. For navigating through your application, you can create controls such as tabs and navigation bars. You can create a popup List of Values for form fields to perform convenient lookups. You can control the appearance of your pages with HTML templates. These components, once created, can be added to any page in your application.

Let’s make a simple modification to our interface.

• In the Page Rendering section, under the subsection Page, click our report page.
• Edit the Title field to ‘ABC Co.’s Employee Report’.
• Apply the changes and run the page (via the Run Page link).

We could also add HTML header and footer markup.

In the Page Rendering section, click the link to the Region on the report page that contains the report. Notice how, when we generated this report, HTML DB created the PL/SQL source for the view. This is a good starting point, but it is also customizable. Currently, our emp report shows the deptno column, which is a foreign key to the dept table. It would be more meaningful to display the department name, stored in the dname column on dept.

• On the Region properties page, change the Type field to ‘SQL Query’.
• In Region Source, enter the following query:

SELECT empno,ename,job,mgr,hiredate,sal,comm,dname
   FROM emp,dept
   WHERE dept.deptno = emp.deptno

• Apply the changes and run the page.

The report now displays the more meaningful department name.

When users are performing data entry, it is often helpful to provide them with a selectable list. When you are editing an employee record, an ideal place for such a list, commonly called a List of Values or LOV, is the deptno field. Remember, our report now shows the department name but the employee table actually requires deptno. Navigate to the update form that HTML DB has created for us. You can get there by clicking the pencil icon on the left side of a report entry. Follow these steps to create a department LOV for our employee form:

• From the update form page, click the Edit Page link at the bottom.
• Under the Shared Components section, List of Values subsection, click Create.
• Under Identify LOV Source, select ‘From Scratch.’ Click Next.
• Enter ‘Department_LOV’ in the Name field and select ‘Dynamic’ for Type. Click Next.
• On the next page, enter the following in LOV Source:

SELECT INITCAP(dname) display_value, deptno return_value
   FROM dept
   ORDER BY 1

• Apply these changes.

Now, we have defined an List of Values for use anywhere in our application. We will attach it to the deptno field on our update form:

• In Page Rendering, under the Items subsection, click item P4_DEPTNO.
• Beneath Name, select ‘Popup LOV’ for the Display As property.
• Scroll down to the List of Values section and select ‘Department_LOV.’
• Apply the changes and run this page.

Now, when you click the edit icon on a report record, and you wish to change an employee’s department, you can simply select the department number from the List of Values (which displays both deptno and dname). Figure 3 shows my new form with its List of Values.

Figure 3: HTML DB Update Form

There are a number of other options available when customizing your application. For example, you can click through a wizard to designate an HTML template for your pages. You also can define report pagination, calculate totals for items, and conditionally display items based upon their runtime values. None of this requires coding.

Next month, we will add a second table to our application. Then, we will add more complex customizations such as JavaScript code to populate data items, PL/SQL code that performs data validation on form input, and buttons that compute information.

In the meantime, if you have not registered for a free workspace, do so. Then, visit Oracle’s OTN site that offers numerous HTML DB How-To’s.

About the Author

Michael Klaene is a Senior Consultant with Sogeti LLC. He has spent over 9 years in Information Technology and is an experienced Systems Analyst, delivering solutions that involve numerous technologies, such as J2EE and .NET.