Adding Processes to HTML DB Applications - PL/SQL (Page 3 of 4 )
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.
Next: Reset Pagination >>
More Oracle Articles
More By McGraw-Hill/Osborne
|
 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). Check it out today at your favorite bookstore. Buy this book now.
|
|
