Adding Processes to HTML DB Applications

In this second part of a three-part series covering HTML DB applications, you will learn about processes. This article is excerpted from chapter 13 of the Oracle HTML DB Handbook, written by Lawrence Linnemeyer and Bradley Brown (McGraw-Hill, 2006; ISBN: 0072257687).

Processes

Processes are the heart of an HTML DB application when it comes to making the application more than just another HTML application. The primary purpose of processes is to interact with the database, but other types of processes are also available to the developer. Most of the time processes are defined at the page level, but you can also define application-level processes. Processes defined at the application level will be evaluated for every page of the application unless limited by their Condition section.

Like computations, processes can be defined to be evaluated either during the page rendering or during the page processing. Those done during page rendering are usually used to retrieve data from the database and populate items on the page. Those done during processing are often used to save the user-entered information into the database. If we look back at a form page we created using wizards, such as the one to edit CONTACTS, we see three processes—one to fetch a row from CONTACTS, one to process a row of CONTACTS, and one to reset the page. The first two are Data Manipulation type processes, whereas the last is a Session State type process.

Processes can both reference and set the values of any page-level or application-level item for the current application. The items will be referenced in different ways, depending on the type of processes you are defining. You will use one of the substitution variable syntaxes discussed in Chapter 6, but most often you will use the bind variable syntax (:P8_MY_ITEM), or in PL/SQL you might use the v() or nv() function.

{mospagebreak title=Creating Processes}

Processes, like almost anything else in HTML DB, are created with a wizard. The wizard for page-level processes is started either from the Add icon in one of the two Processes sections on the Page Definition page or through the Create link on the Developer’s Toolbar (selecting the Page Control on This Page option and then the Process option). In the first step of the Create Page Process Wizard, you must select from one of the eight different types of processes available (see Figure 13-1).

The wizard for application-level processes is started with the Create button from the Application Processes section of an application’s shared components. With application-level processes, you will not have to select a process type because the only type you are allowed to create as an application-level process is a PL/SQL type process. The eight different types of processes are


Figure 13-1.  Process types in the Create Page Process Wizard

briefly described in the following table. The remaining steps in the process-creation wizard vary depending on the process type you select.

 
Process Type Description
PL/SQL This is a generic PL/SQL anonymous block of code, which could be as complicated as you would like to make it or could simply call a program unit that is stored in the database (for instance, a general API that is always used for access to a particular table).
Reset Pagination This process type is used to reset the pagination of a report back to the beginning of the result set. This way, the next time the report is displayed, the items displayed will be the first part of the result set.
On Demand The purpose of a page-level On Demand type process is to execute an application-level process that has been defined to have a processing point of On Demand. This way, a single process can be called from several different locations throughout the application. Use this process type if you have a generic process that must be executed at multiple locations in your application.
 Session State This process type is used to null out the values of existing session state items. It can be used to clear the cache for all workspace applications, for a selected workspace application, for the current session, for certain pages, or for particular items. This process type is also used to set the value of a user preference.
 Data Manipulation This process type is used for an automatic Data Manipulation Language (DML) process. The processes created by the wizards to do multirow or single-row form fetches, saves, updates, and deletes are of this type. Typically when you use this type of process, it will be created for you by a wizard.
Web Services This process type is used to implement a web service. When this process is run, it submits a request to the web service provider.
Form Pagination This process type is used with a master detail page. It is used to determine the next and previous master records and to display the master record positional count (for example, “3 of 4”).
Close Popup Window This process type is used to close a pop-up window and refresh the calling window. This process type is new to version 2.0—so new that as of the time of publishing this book, Oracle had not been able to provide instructions on how it refreshes the calling page.

The path of the wizard will vary depending on the type of process you choose to create. A number of fields will be entered for most processes. In the Name field, you provide a descriptive name of the process to give other developers an idea of what the process does. The value you enter in the Sequence field is used to determine the order in which processes will be evaluated within the same process point. The Point field is used to specify a point in page rendering or page processing at which the process will be evaluated. Here are the possible points:

  • On New Instance – After Authentication
  • On Load – Before Header
  • On Load – After Header
  • On Load – Before Regions
  • On Load – After Regions
  • On Load – Before Footer
  • On Load – After Footer
  • On Submit – Before Computations and Validations
  • On Submit – After Computations and Validations

In almost all the paths through the Create Page Process Wizard, after you select the type of process you want to create, as shown earlier in Figure 13-1, you will be presented with the process’s Type field. The value of this field will be the type you selected. In most cases, you won’t want to change its value. However, when you select to create a PL/SQL type process, this field will default to the value “PL/SQL anonymous block.” You may wish to change the Type field to “PL/SQL DBMS JOB (runs anonymous block asynchronously).” This allows you to run the process in the background. This is ideal for long-running processes where the user does not need immediate feedback from the process.

The values entered into the Success Message field will be displayed if the process is evaluated successfully and the following page has the #SUCCESS_MESSAGE# substitution variable used in the page template. The value entered into the Failure Message field will be displayed if the process fails. You can make a process conditional by selecting a button for the When Button Pressed field and/or by selecting a condition type and specifying values for Expression 1 and Expression 2.

{mospagebreak title=PL/SQL}

PL/SQL processes offer you the most flexibility in what you can do with them—from a simple DML on a single table, to DML on multiple tables, to the most complex business logic. Aside from the common fields discussed previously, you will also have to enter an anonymous block of PL/SQL. One example might be to insert a row into a table with data collected in a wizard. As noted, you can create a normal PL/SQL process or one that runs in the background. Refer to Appendix A for details on the HTMLDB_PLSQL_JOB package.

The CONTACTS table we have been working with contains almost 30 columns, so it would be a good candidate for a wizard. Use the Create Page | Wizard option to create a wizard with four or five pages. Divide up the fields over three or four pages, leaving the last page for a confirmation page. On the final confirmation page you would have a Finish button. Also, you could display all the data collected so far in a Display Only on Existing Items type region. You then create a PL/SQL process that’s conditional on the Finish button being clicked and inserts a record into the CONTACTS table. The PL/SQL for the process would look something like this:

insert into contacts (contact_id, firstname, lastname, dear, …
                            companyname, workphone, workextension, … )
      values (:p20_contact_id, :p20_firstname, :p20_lastname, …
                :p23_companyname, :p23_workphone, :p23workextension, … );

Another useful purpose for a PL/SQL process would be to process the contents of a custom multirow edit form. The wizards in HTML DB make implementing a multirow edit form very easy if it is for a single table. If you have the need to create a multirow edit form based on a complex query, you can create and process the form using two built-in packages: HTMLDB_ITEM and HTMLDB_APPLICATION. The form is constructed by creating a SQL Query–based report and using the HTMLDB_ITEM package in the query to generate the items. To illustrate the concept, the following is a simple query based on the EMP table:

SELECT empno,
        HTMLDB_ITEM.HIDDEN(1,empno),
        HTMLDB_ITEM.TEXT(2,ename,10,10) ename,
        HTMLDB_ITEM.TEXT(3,job,9,9) position,
        HTMLDB_ITEM.SELECT_LIST_FROM_LOV(9,mgr,’managers’,null, ‘NO’) mgr,
        HTMLDB_ITEM.DATE_POPUP(4,rownum,hiredate,’Month dd, yyyy’) hd,
        HTMLDB_ITEM.TEXT(5,sal,12) sal,
        HTMLDB_ITEM.TEXT(6,comm,12) comm,
        HTMLDB_ITEM.SELECT_LIST_FROM_LOV(7,deptno,’department’,null, ‘NO’)
      deptno,
       
HTMLDB_ITEM.CHECKBOX(8,empno,decode(temp,’Y’,’CHECKED’,null)) temp
      FROM emp
      ORDER BY 1

A report created with the previous query produces a report like the one shown here:

To process the changes to this multirow edit form, you use a PL/SQL process like the following. The values of the items in a multirow edit form are passed in arrays that are available to the developer using the HTMLDB_APPLICATION package. Note the special treatment of the check box (item 8). The arrays for check box items only contain entries for those check boxes that are checked.

declare
        is_temp char(1);
     
begin
       
for i in 1..htmldb_application.g_f01.count loop
          
is_temp := ‘N';
       
for x in 1..htmldb_application.g_f08.count loop
          
if htmldb_application.g_f01(i) = htmldb_application.g_f08(x)then
             
is_temp := ‘Y';
          
end if;
        end loop;
        update emp
           set ename   = htmldb_application.g_f02(i),
             
job      = htmldb_application.g_f03(i),
             
hiredate = to_date(htmldb_application.g_f04(i),’Month dd,
yyyy’),
              sal      = htmldb_application.g_f05(i),
              comm     = htmldb_application.g_f06(i),
              deptno   = htmldb_application.g_f07(i),
              temp     = is_temp,
              mgr      = htmldb_application.g_f09(i)
       
where empno = htmldb_application.g_f01(i);
    end loop;
end;

The HTMLDB_ITEM and HTMLDB_APPLICATION packages are introduced in Appendix B and are covered in great detail in the HTML DB User’s Guide. Note that the first parameter passed into the various HTMLDB_ITEM functions is a sequence number. This sequence number corresponds to the g_fXX functions in the HTMLDB_APPLICATION application that reference the arrays of data passed from the items.

These are just a couple examples of how a PL/SQL type process can be used. Its limitations lie only in your PL/SQL coding skills.

{mospagebreak title=Reset Pagination}

The Reset Pagination process does one and only one thing—it resets the pagination for the current page. After selecting the Reset Pagination type option in the Create Page Process Wizard, the only information you must provide is a name, sequence, processing point, and any conditions.

On Demand

On Demand processes allow you to write a single process that can be evaluated on multiple pages at different points. There are two parts to On Demand processes: the definition of an application-level On Demand process and page-level definitions of On Demand processes that call the application-level process. Like all application-level processes, an On Demand process is a PL/SQL type process.

If you have a process that needs to be evaluated at the same evaluation point on multiple pages, create an application-level process and limit its evaluation to the appropriate pages with the process’s Condition section. However, if you need the same process to be evaluated by multiple pages at different points, you will want to create an application-level On Demand process and page-level On Demand processes on the appropriate pages for evaluation at the appropriate points.

Session State

Session State processes allow you to clear out the session state. Normally this type of process is used to clear out the cached values for the current session of the current user. You can clear out individual items by listing those items. You can clear out all items on individual pages by listing the pages, or you can clear out everything for the current session of the current user.

In addition to clearing items for the current session, this type process can also deal with the current user’s preferences. Remember, preferences are values stored per user, so the next time the user returns, in a different session, the values are still available. With a Session State process, you can clear all the preferences for the current user or you can set the value of a certain preference based on the value of an item.

Finally, Session State processes can be used for managing sessions within the workspace. This is part of the administrative portion of an application. Remember, session states are stored in the database, and they don’t go away until they are removed either through the Workspace Administration section available in the HTML DB development environment or through the HTML DB Administrator interface. The options available for this purpose are Remove All Session State for the Current Application and Remove All Session State for a List of Applications Within the Workspace.

Please check back next week for the conclusion to this article.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan