In the last chapter, we learned about the individual packages of the PL/SQL toolkit. In this chapter, we'll use these packages to create two realistic web applications. The first application allows us to create and distribute anonymous surveys; the second allows users to communicate over the Web in a threaded discussion forum.
Before looking at these two systems, however, let's take a quick look at four simple steps that can improve the design process. When you are confronted with any design task, it can greatly help to break the process down into manageable pieces, as follows:
Evaluate your development options; be sure PL/SQL and HTML are really the right tools for the job.[ 1 ]
 We'll assume that in this chapter!
Create a data model to help you create the screens.
Use PL/SQL packages to actually structure the code.
The next few sections describe these steps in more detail.
Choosing the right development language is always the critical first step in building any application. While you can use HTML and PL/SQL to create very powerful systems, there are still some things you should think about before you start coding. For example:
Does the proposed application require a lot of sophisticated data entry screens?
Will users accept a new kind of interface?
Do all users have modern browsers?
Is the system brand-new, or is it replacing an existing system you could easily retrofit for the Web? For example, you could take an existing Oracle Forms application and access it using the web-enabled version of Developer.
The first of these questions is the most important. As a rule of thumb, it's probably better to use Java or client/server than HTML for mission-critical applications with sophisticated data entry screens requiring lots of user interactions, which would be very difficult to code from scratch. For example, you would not want to develop an accounts payable system in HTML. In my own experience, the most effective use of PL/SQL and HTML is for creating systems that generate information from a database, not those that put information into a database.
Also, once again, it's important to remember that you must always accommodate your design to the stateless nature of HTML. For example, a complex, hierarchical system where users progressively drill down to lower and lower levels, all the while picking up extra state information, might be difficult to implement in a web environment.
Once you've decided to build an HTML system using PL/SQL, the next step is to create a diagram, called a storyboard , that helps you visualize the relationship between an application's various web pages. Figure 8.1 shows a simple storyboard for an organizational chart application.
Each block on the diagram represents a page. The arrowed lines represent links between the pages. The labels on the link represent the information needed to move from one screen to the next. For example, to generate the second screen of the organization chart application, we must use the department ID code (dept_id) to fetch the correct employees.
There are several benefits to building a simple storyboard before you begin an application:
Since a storyboard gives even a casual observer an overview of the entire system, you can get useful feedback from potential end users before you begin any actual coding.
The elements on the storyboard translate almost directly into PL/SQL structures. For example, a quick look at the organization chart storyboard shows that we need at least three procedures. The labels on the links give us the formal parameters for each of these procedures.
The storyboard, even if sketched out on the back of a napkin, is a useful piece of system documentation that can help future developers to quickly grasp the system's major functions.
Once you have completed the storyboard, you should build a simple data model to help you create the screens. Tables and relationships are your bread-and-butter resources; put them to work in your web systems. For example, suppose your company reorganizes and adopts some crazy new management plan. Inevitably, you will be called upon to develop hundreds of online surveys, with questions such as "Do you meet your core objectives?" or "How can you better align your personal life to the strategic goals of the company?"
We could take two approaches to this problem. In the first, we'll simply wait by the phone until someone in human resources (probably Bob) calls and asks us to create a particular survey (perhaps "Cross-Functional Teams and You"). Unfortunately, every call means that we have to build a new form, as well as analyze the results. This really cuts into our recreational web surfing.
A second, more interesting approach is to build a data-driven system that uses a data model to create any survey. Each time we have to create a new survey, we can simply enter the questions into the tables of our model, and out pops a complete form. Since the forms are standardized, we can also build a general system to store the user's responses and analyze the results.
Building a robust data model is a key part of this idea, and data models require data entry forms to populate their tables. We could build these tables with HTML, but it makes more sense to use a WebDB form or a client/server tool. If we, as IS developers, are the only people who will use a maintenance application, why spend the effort developing a complex system no one else will ever see?
The final step in building an application is to actually structure the code. As we've seen, the best tool for organizing PL/SQL is the package. Breaking your systems down along logical lines can simplify the design process and eliminate huge, monolithic programs. Package design is the most important PL/SQL skill to master, and simple web applications are a great place to practice your techniques. Here are some guidelines you should keep in mind when designing your systems:
Separate the "business rule" parts of your code from the user interface code. This will help you reuse these business rules in non-HTML applications, such as Java, client/server, and even Pro*COBOL.
Build a standard library of web functions that you can use over and over. Coding is hard work; anything you can do to reuse what you've already done (or better yet, what someone else has done) makes your life much easier.
Follow best-practices coding standards. A good PL/SQL book will help get you started; see Appendix A, Appendix: Resources for the Oracle Web Developer for further information.
Follow best-practices naming conventions for parameters and variables. As we've seen in earlier chapters, WebDB and OAS are very picky about variable names and types.
Okay, enough sermonizing. Let's look at some code.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.