DatabaseBuilding Web Applications with Oracle HTML DB, Part 2

Building Web Applications with Oracle HTML DB, Part 2

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

In last month’s article, “Developing Applications with Oracle HTML DB, Part 1,” we set up a rudimentary HTML DB application and deployed it to our workspace. To highlight the ease in which any developer, or non-developer with minimal SQL skills, could build something useful with this tool, we stuck to the basics. We used wizards to build the initial application based upon an employee table, then we made a few modifications. For example, we modified our report’s query to display more useful information and included a pop-up list of values for form field lookups, all to improve the user’s experience. In this article, we’ll continue where we left off and add some additional features that real-world applications might require. If you have not read last month’s article, please do so before continuing.

Improving the interface

I hinted last month how easy it was to beautify your application by adding templates, report pagination, and more. Before we start adding any new functionality, let’s work on the interface. We’ll start with selecting a template. HTML DB provides a repository of existing templates to choose from. Follow the steps below to add a template to a page.

  • Log into your HTML DB workspace and select your ‘Emp’ application from Available Applications.
  • Click on a link for a page in your application.
  • Under Shared Components -> Templates, click Create.
  • In the Region Templates section, click Create/copy.
  • Select the option ‘Choose one from the HTML DB Repository’. Click Next.
  • We will select the theme ‘Blue/Tan Box’. Click Next.
  • Leave the Actual Name field as is and click Create.
  • The template is created. Now, select the Pages tab to return to the Page Definition screen.
  • Under Page Rendering – > Regions, click View.
  • In the Template drop down box, select the ‘Blue/Tan Box’ for a page and save.

This page in our application now uses a template. Once added to the application, a template can be added to any (and probably every) page by setting its ‘Region’ Template property.

Now, let’s add a few more display features to our employee report.

  • Navigate to your application’s employee report page and click Edit Page at the bottom of the page.
  • Select the region containing your employee report. Select the Report Attributes tab.
  • Under the Heading column for each report item, change the text to something more meaningful.
  • For the ‘Sal’ (salary) column, check the Sum checkbox.
  • In the Show null values as textbox, type ‘N/A’.
  • For Pagination Scheme, select the ‘1 -15 16-30’ option.
  • Type ‘8’ in the Number of Rows field.
  • Click Apply Changes, and then Run Page.

We’ve just made several changes to our interface. First, the report page displays more meaningful column headings. Commission is an optional field. If an employee has no designated commission, that field is displayed as ‘N/A’. Adding pagination, we now display only 8 report records per page. Click on the ‘9-n’ link to view the remainder of the report. Finally, at the bottom of our report, we have a total calculation for employee salaries.

Adding navigation buttons

In a typical business application, there is a need to navigate between different screens. There are numerous ways to accomplish simple navigation. Oracle HTML DB provides a ‘breadcrumb trail’ by default when it generates an application. These page links are visible in the upper left hand corner of every application page. Oracle HTML DB also provides ready-made components such as a tree menu or a docked navigation bar. However, often navigation is a little more involved. It may not be enough to simply navigate to a page, but we might want to conditionally navigate to one of several pages or naviate to a page with some data input as a parameter to this new page. In Web development, this is sometimes accomplished by attaching request parameters to the HTTP request that is made. The following steps will demonstrate how to add two buttons to our report. One will perform a simple navigation, while the other attaches request information to the button click event.

  • Navigate to your application’s employee report page and click Edit Page at the bottom of the page.
  • Under Page Rendering -> Buttons, click Create.
  • In the Region drop down box, select the region containing our employee data. Click Next.
  • For Button Name, enter ‘Add_Employee’. Leave the Action ‘Redirect to URL’ selected. Click Next.
  • Click Next, and then Next again until you reach the Branching tab.
  • In the Page text box, display its List of Values. Select the form used to insert employee records.
  • Click Create Button.
  • Repeat the first six steps of this sequence to create a second button. This time, enter ‘View_Salary_Chart’ for Button Name.
  • When you reach the Branching screen, stop. Display the Page LOV once again.
  • Select the analysis page of our application (that displays a pie chart for a selected field).
  • In the Set these items field, below Page, enter the item name ‘P6_SHOW’.
  • In the With these values field, below Page, enter the text ‘Sal’.
  • Click Create Button.
  • Run the page.

Figure 1 shows what we have accomplished. The report page has two buttons. ‘Add Employee’ takes us to a blank employee entry form. Quick and simple. The second button, ‘View Salary Chart’, goes one step further. On the employee analysis page, there is a drop down box called ‘P6_SHOW’ that accepts a string to tell it what data item needs to be analyzed. We told HTML DB to branch to this page, but we also added a request parameter of ‘P6_SHOW’ and gave it a value of ‘Sal’, the employee salary field. In a typical Web application, we might have added this extra information in a query string ala http://www.my_site.com/my_chart_page?P6_SHOW=Sal. HTML DB gives us a convienent way to accomplish the same task. Go ahead and try these two buttons and, if you so desire, add a few more.

Figure 1: Our Report Page with Buttons

Adding JavaScript to the mix

Many Web application developers like to augment the functionality of their systems with client-side scripting. Oracle HTML DB supports JavaScript, which can be very useful when trying to compensate for a Web browser’s stateless behavior. It is possible to respond to user actions such as mouse clicks and inter-field navigation. Probably most important, JavaScript can help avoid uneccessary requests made against the Web server, thereby improving application responsiveness.

There really are no limits to using JavaScript within HTML DB. The following steps will demonstrate just one way developers use JavaScript to improve their applications. HTML DB adds several standard buttons to wizard-generated forms. On the employee update form, one of these buttons will delete the current record. An unintentional delete from a production database can have serious consequences, so we might want to verify the user’s decision before actually doing the deed. HTML DB provides several JavaScript functions. We will use one here to add a confirmation before sending a ‘delete’ request to the server.

  • In your application, navigate to the form that updates employee information.
  • Click Show Edit Links from the bottom of the page. Click the edit option on the delete button.
  • Scroll down. In the Target is a field, select the ‘URL’ option.
  • In the URL Target field, enter the following JavaScript code:
  • javascript:confirmDelete('Do you really want to delete this
                              Employee record?');
    
  • Click Apply Changes; then, run the page.

When deleting a record from the employee table, a user will first need to confirm their choice. It is also possible to perform client-side edits with JavaScript; however, it should be noted that Oracle HTML DB allows you to add item validations in a simple, wizard-like fashion. Using the declaritive approach to validation with HTML DB is quick and easy. When violations occur during data entry, HTML DB can use JavaScript behind the scenes to present messages to the user.

Adding complex validations

Sometimes, even the simplest of applications requires validation processing that is more complex in nature. For example, if you are entering data into the database, SQL lookups may be required in some validation code to ensure that certain business rules are enforced. Oracle HTML DB allows you to write custom PL/SQL code so developers can customize as necessary. Our first example uses PL/SQL to check the value of multiple fields in a form.

  • Click Show Edit Page from the bottom of the employee update page.
  • In the Page Processing section, under Validations, click Create.
  • For Name, type ‘CHECK_JOB_AGAINST_SALARY’. Leave Validation Type as ‘PL/SQL Expression’. Click Next.
  • In the Type field, select ‘Function Returning Error Text’.
  • In Validation, enter the following PL/SQL code:
  • BEGIN
       --Only Presidents can make the big bucks:
       IF :P4_JOB <> 'PRESIDENT'  AND :P4_SAL > 75000 THEN
           RETURN 'Only the President can make this much money!!!!';
       ELSE
           RETURN NULL;
       END IF;
    EXCEPTION
         WHEN OTHERS THEN 
             RETURN SQLERRM;
    END;
    
  • In Error Message, enter ‘Only the President can make this much money!!!!’
  • Select ‘P4_SAL’ as the Associated Item.
  • Save these changes and run the page.

Attempt to update an employee who is not the ‘President’ by giving them a salary greater than $75,000. When you do so, this validation code will execute and the error will be displayed next to the salary field.

As mentioned previously, you may be required to access the database during form validation. Add another validation by the name of ‘CHECK_FOR_MULTIPLE_MANAGERS’ that enforces a business rule that states for any given department, there can be at most one manager. Here is the PL/SQL validation code we will use:

DECLARE
   mgr_cnt PLS_INTEGER := 0;
BEGIN
   --Only 1 Manager per department:
   IF :P4_JOB = 'MANAGER' THEN

   SELECT COUNT(1)
      INTO mgr_cnt
      FROM emp
      WHERE deptno = :P4_DEPTNO
      AND empno <> :P4_EMPNO
      AND job = 'MANAGER';

      IF mgr_cnt > 0 THEN
          RETURN 'Department '||:P4_DNAME||' already has a
                  Manager!!!!';
      END IF;
   END IF; 

   RETURN NULL;

EXCEPTION
   WHEN OTHERS THEN
      RETURN SQLERRM;
 END;

In our application, there is a department called ‘Research’. In this department, employee Ford has the job title of ‘Manager’ and employee Jones is an ‘Analyst’. According to business rules, Jones can’t be promoted to Manager while Ford already holds the title of Manager. Figure 2 shows the results of attempting to do so: a repopulated form with a descriptive error message.

Figure 2: Update Failure on Employee Form

Conclusion

Hopefully, these articles have demonstrated how to construct an HTML DB application with functionality that most business applications require. It is not possible for me to step through all of the functionality that Oracle HTML DB offers. Experimentation is the best way to learn how this tool can help you build robust web-enabled applications, in a minimal amount of time. Visit Oracle’s HTML DB site for more information. There, you will find, among other things, a fairly thick User’s Guide that will serve as a nice reference while you work.

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.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories