Home arrow Oracle arrow Page 3 - Adding Processes to HTML DB Applications

PL/SQL - Oracle

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).

  1. Adding Processes to HTML DB Applications
  2. Creating Processes
  3. PL/SQL
  4. Reset Pagination
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 2
April 26, 2007

print this article



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.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')
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.

        is_temp char(1);
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,
              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;

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.

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: