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.