Top Tips and Techniques for WebDB

Sameer Jejurkar

Hencie Consulting Services Inc.

Introduction

The business case for Oracle WebDB states "it is an end-user oriented tool to build dynamic web-sites. It provides tools to manage content and access databases. Oracle WebDB gives content creators the tools they need to publish their own documents on the Web, by directly editing the Web site from within their browser". While this statement is absolutely correct, it doesn't tell the whole story. The point that gets lost is that WebDB is also a tool that developers can use to build web-based applications to access their Oracle databases. There many features that can be used to add power to the basic functionality generated by WebDB. This paper explores some of these WebDB features that can be used to build more powerful and user friendly applications.

WebDB Architecture

WebDB is written is PL/SQL and resides completely in an Oracle database. It uses a PL/SQL gateway (either as CGI or as a cartridge for Oracle Application Server) to dynamically generate web pages that use standard technologies like HTML and JavaScript. All the code generated by WebDB is in the form of PL/SQL packages that use the PL/SQL web toolkit. This allows you to extend the default functionality of components generated by WebDB using simple PL/SQL, HTML and JavaScript.

Tip # 1: Using JavaScript for Validation in Forms

Using JavaScript to perform basic validations in your WebDB forms offers two advantages. One, it allows data to be validated before it is submitted � this can save unnecessary network trip to the server and two, users get instantaneous feedback (this can be important if users are used to working with client-server applications, where the feedback is usually immediate).

WebDB provides Shared JavaScript Components to create and manage scripts to perform field and form validation. These can be reused easily in as many forms as required. The good part about these is that you can either write your own validation scripts or get them from elsewhere. WebDB provides some basic validation scripts � isNull, isNumber etc. You can write your own validation scripts to perform other simple operations like validating format for date fields, validating that the user has entered a valid email address, convert the data entered by the user to upper case etc. Take a look at the JavaScript component isDate given below. Its purpose is to validate if the data entered is a valid date in the format MM/DD/YYYY.

As you can see a JavaScript component is nothing but a JavaScript function written in a particular way. It must accept two parameters: theElement (a reference to the form element object being validated - in this case, the field) and theName (a string providing the name of the field). Also, it must also return "true" if the validation succeeds or "false" if the validation fails. If the validation fails, it must display an error (alert(theName... portion of the code) and move the cursor back to the erring field (theElement.focus() portion of the code). There are many web sites that have JavaScript listings containing code to perform a variety of validations. All you need to do is simply modify those as per the above requirements and they are ready for use.

Once you've added a JavaScript to your WebDB Shared Components Library, you can use it in as many forms as you want, just by specifying its name in the appropriate place. These JavaScript components can be used to validate the data entered in the field either at the field level (validate as soon as the cursor moves out of the field) or at the form level (validate when the form is submitted). As the figure below shows, all you need to do is simply select the name of the appropriate JavaScript component when specifying the details of the particular field.

For a details about using JavaScript for validation in WebDB forms please read my article in Oracle Magazine online. This article also contains listings of JavaScript components to validate date, date/time and e-mail fields.

Tip # 2: Developing Forms based on Stored Procedures

In cases where you want the data entered by the user to go straight to a table without any manipulation, you can create a WebDB form based on a table. However, if any processing must be done or business rules validated before it is inserted, it may be a better idea to develop the form using the "form based on Stored Procedures" option. Also, if you have an existing stored procedure that you want to use, for example if you have a table API, you can use it to be the basis for the form.

For example, consider that you have a stored procedure as shown below:

When you build a form based on this stored procedure, the form will have three fields - one field for each parameter of the procedure. When the user submits the form, the stored procedure form_proc is called with the data entered by the user as the parameter values.

All the parameters must be of type varchar2. You can also use parameters of type number, but you must remember to put a JavaScript validation on the number fields.

Tip # 3: Using Text Options

To show static headers and footers in your WebDB user interface components - Forms, Reports (for the report itself as well as the parameter form), Frame Driver (for driving frame), Calendars, Charts and Hierarchies, you can use the Text Options � Header Text and Footer Text. The good part is that in addition to text, you can use HTML code in header as well as footer text.

As shown if the above figure, if you want to add a link for new order status and a button to refresh the list in the driver frame of a frame driver component, you can do it by adding code to the header text and footer text portions.

For the link, add the following HTML code to the header text portion:

Similarly, to get the button at the bottom, add the following HTML code to the footer text:

This example uses the frame driver example because it is convenient to use a frame driver for forms based on simple look-up type tables. However, the list in the driver frame does not automatically refresh itself when an entry is updated, a new entry added or existing entry deleted. You can also use the header and footer text in other types of components.

Tip # 4: Using Advanced PL/SQL code

You can also, use "Advanced PL/SQL code" � before displaying the page, after displaying the header, before displaying the footer and after displaying the page � options to show special headers and footer. You can show static headers and footers using PL/SQL code that generates HTML. However, using PL/SQL offers the advantage of being able to use the data from the database. This is not possible using Text Options. Forms have two additional advanced PL/SQL options � before processing form and after processing form.

Consider an example. As discussed in tip # 3, the list in the frame driver is not updated automatically to reflect the modifications, additions or deletions. Another solution to this problem could be to have the WebDB component in the target frame to refresh the whole HTML page (including both the driver frame and the target frame). This can be done as follows. Include the following PL/SQL code in the "after form processing" portion of the WebDB component being called in the target frame.

This code simply refreshes the entire HTML page after the form has been submitted and thus the list in the driver frame is also updated to reflect any changes, additions or deletions made.

Tip # 5: Using Reports to Create Special Forms

Suppose you want to create a form as shown in the figure below

Although this is an HTML form, it is not possible to create such a form using the "Form" user interface component of WebDB. Instead, you can create a "report" component (using the Report from SQL option) based on a SQL query that generates the necessary HTML. The SQL used in the above report is as follows:

As you can see, the HTML code to generate the check box is a part of the SQL query and every row shown in the report will contain a check box. In addition, as shown in the figure below you must use header and footer text options to add the HTML form tags and the code for the form submit button. The header contains the following HTML code to open the HTML form and show the form submit button.

This code implies that the stored procedure

The values for selected check boxes are passed to the procedure as a PL/SQL table of varchar2 elements (order_num_list). The name of the parameter must match the name of the HTML check box. Using these values, further processing can be carried out.

The Footer Text contains the tag

Tip # 6: Using Unstructured Form Layout

In a structured form, you can control the layout of the fields in the form by specifying if the field begins on a new line. There is nothing apart from this that allows you to control the layout. If you want to get better control over the layout of the form, use an unstructured form.

For example, given below is a structured form for entering simple customer information.

As you can see there is a considerable space between the city field and the label for the State field. Using an unstructured form layout you can easily eliminate such layout problems. Using unstructured layout is easy � write the necessary HTML code to create the type of layout that you want and simply embed the special field label tags and field tags wherever you want to place them. The special tags <#FIELD_NAME.LABEL#> and <#FIELD_NAME.FIELD#> indicate the field label and the field itself. Thus, a possible unstructured layout for the above may look like this:

The following figure shows the form generated by the unstructured layout given above. Note that we have removed the unnecessary space between the city field and the label for the State field.

Related tip: using unstructured layout for templates

As with form layouts, you can create either structured or unstructured templates. To create an unstructured template you can use any HTML to get the basic layout and then add these substitution tags to your HTML code:

You can use any editor � text or HTML � to generate the necessary HTML code for unstructured layouts, then copy-paste the code at the appropriate place in WebDB and add the special tags.

Tip #7: Master Detail Forms

The master detail forms in WebDB work well if the primary key for the master record is generated before the form is submitted. However, if the primary key for the master record is generated by the system after the form is submitted, there is a problem. Let's consider an example � an ORDER_HEADER and ORDER_LINE. The primary key for the ORDER_HEADER � ORDER_NUMBER � is system generated and is also a foreign key in the ORDER_LINE table. The order number is system generated (using a database trigger) and is not available until the insert statement for the order header table is issued. But this order number is not available to the insert statement for the order lines table and the inserts fail.

A workaround to this problem is to use your own combination of triggers, a packaged procedure and function. Write a before-row-insert trigger on the ORDER_HEADER table that will generate the value for ORDER_NUMBER and write it to a packaged variable and set a flag. Also, write a before-row-insert trigger on ORDER_LINES that will read the generated order number from the packaged variable and if the flag is set, use the value for inserting records into the ORDER_LINE table. For sample code that uses this technique refer to the file that is included with this paper.

Tip #8: Write your own PL/SQL packages

There is certain functionality that you will use repeatedly when you build more complex applications with WebDB. It is a good idea to put together all the PL/SQL code that you write for this purpose into one or more stored packages. Try and write the code that is more general so that it can be easily reused.

For example, when you create a frame driver module, you have to write a Query that selects two columns:

Typically, the returned URL will be the URL for another WebDB component. In such a case, if you have many frame drivers the format of the returned_url is always the same. You can write a function that accepts the name of the WebDB component to be used and returns the appropriate URL. This function can then be used in the SQL for the driver frame. For details about this function refer to the file that is included with this paper.

Conclusion

WebDB is a great tool if you want to use it to develop web-based applications quickly and easily. The fact that WebDB generates all code in PL/SQL helps to enhance the functionality easily. The tips and techniques that we have explored here represent the tip of the proverbial iceberg. If you would like to learn more of HTML, JavaScript or PL/SQL web tool kit, there are a lot of good books available. Another good idea is to read the PL/SQL code generated by WebDB and HTML code generated by these WebDB components (using the "view source" option in your browser). This will give you a better understanding of how WebDB works and how you can enhance its functionality. What you can do is limited only by your imagination.

Sameer Jejurkar ([email protected]) is a Principal Consultant for Hencie Consulting Services in Dallas, Texas. For the past several years, he has been involved in providing solutions using Oracle Applications, Oracle Database and tools.