Building Web Applications with Oracle HTML DB, Part 1
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
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
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.
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.
Page 2 of 2