Build Tabular Forms for Multi-Row Operations

After completing this How-To, you should be able to understand how to:

• Build a tabular form using wizard
• Build a tabular form using the HTMLDB_ITEM package
• Build a tabular form with extra rows for insert
• Add optimistic locking on tabular form
• Add validation using collections

Table of Contents

• Introduction
• Software Requirements
• Building a tabular form using wizard
  • Changing updateable column display type
  • Restrictions on wizard generated tabular form
  • Building a tabular form with extra rows for insert
  • Adding optimistic locking
  • Adding validation using collections

  Introduction

  In Oracle HTML DB, you can create a tabular form in two ways:

  • Using the Tabular Form wizard.
  • Using the HTMLDB_ITEM package.

  The easiest way to build a tabular form is to use Tabular Form wizard. This wizard creates a tabular form with a built in multiple row update process. This built in process does optimistic locking behind the scene to maintain the data integrity. In certain cases you may want to build a tabular form manually to control the display and functionality. You can use HTMLDB_ITEM package to build a tabular form manually. When you build a tabular form using HTMLDB_ITEM, you will need to build your own optimistic locking since it is not done automatically during update. This How To describes how you can create a tabular form using EMP table that is included when a new workspace is provisioned.

  Software Requirements

  • Oracle HTML DB
  • Web browser such as Netscape 7.0 or higher+ or Internet Explorer 5.5+ or higher

  Building a Tabular Form Using a Wizard

  The simplest way to build a tabular form is to use Tabular Form wizard. This wizard creates a tabular form with a built in multiple row update process. This process checks for MD5 checksum values before doing the update in order to perform optimistic locking behind the scene. When you build a tabular form using the wizard, there are some editing restrictions. We will go over these restrictions in the later steps.

  We will start by creating a tabular form on EMP table using the wizard with following steps:

  1. Navigate to Appication Builder.
  2. From the Available Applications list, select Sample Application.
  3. Click the Wizards tab .
  4. Under Forms, click Tabular Form.
  5. Select the owner of EMP table and click Next.
  6. Select EMP table and and click Next.
  7. Select the following columns as select columns and click Next: ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
  8. Select the following columns as updateable columns and click Next: JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
  9. Select EMPNO as the primary key and click Next.
  10. Accept the default values for page and region attributes and click Next.
  11. Select Do not use tabs and click Next.
  12. For the Submit Button Label, enter Apply Changes and click Next.
  13. Click Finish to create the tabular form.

  Changing Updateable Column Display Type

  When the wizard creates the tabular form, all updateable columns display as text field by default. The following section describes how to change this default display to a select list.

  To change the default display of JOB and DEPTNO to a select list:

  1. Navigate to the Page Definition of the page containing the tabular form.
  2. Under Regions, click Q in front of Tabular Form.
  3. Under Report Column Attributes, click the edit icon next to the JOB column. Figure 1 - Edit report column attributes
  4. Under Updateable Column Attributes, select Select List (query based LOV) as the Display As value.
  5. Under List of Values, enter the following LOV query: select distinct job a, job b from emp Figure 2 - Edit updateable column attributes
  6. Click Apply Changes to complete the change of JOB column.
  7. Click the edit icon next to the DEPTNO column.
  8. Under Updateable Column Attributes, select Select List (query based LOV) as the Display As value.
  9. Under List of Values, enter the following LOV query: select dname, deptno from dept
  10. Click Apply Changes to complete the change of DEPTNO column.
  11. Click Apply Changes again to complete the change of this tabular form.

  Run the page. The tabular form displays with JOB and DEPTNO column in select list (see Figure 3).

  Figure 3 - Wizard created tabular form

  Edit Restrictions on Wizard Generated Tabular Form

  If you create a tabular form using the wizard, the wizard creates a SQL Query (updateable report) region type. When you wish to change the display type of updateable columns for this type of region, you need to use updateable column attributes. If you change the display type using HTMLDB_ITEM package in the select list of a SQL statement, the update will not work properly. For example, the wizard generated SQL statement cannot be mixed with HTMLDB_ITEM package:

  select htmldb_item.hidden(1,empno) empno, "ENAME", "JOB", htmldb_item.select_list_from_query(2,mgr,'select ename, empno from emp') mgr, "HIREDATE", "SAL", "COMM", "DEPTNO" from "#OWNER#"."EMP" order by 1 

  The select list of a SQL statement of a tabular form should not be modified after it has been generated. Doing so can result in a checksum error when altering data of the form and applying updates. Consider the following example:

  select ename from emp; Note that this should not be altered to: select lower(ename) from emp.

  Building a Tabular Form Manually

  You can also create a tabular form manually by defining SQL report query using HTMLDB_ITEM package. When you build a tabular form manually, you have more control on editing the tabular form query and processing the update. However, be aware this manual approach does involve more steps. If you wish to build a complex tabular form, it is best to take the manual approach. Otherwise, building a simple tabular form can be easily done using the wizard.

  To build a tabular form manually:

  1. Navigate to Appication Builder.
  2. From the Available Applications list, select Sample Application.
  3. Click the Wizards tab.
  4. Under Reports, select SQL Report.
  5. Enter Update EMP as the Page Name and click Next.
  6. Select Do not use tabs and click Next.
  7. Enter following SQL query and click Next:

   select htmldb_item.hidden(1,empno) empno, ename, htmldb_item.select_list_from_query(3,job,'select distinct job, job from emp') job, htmldb_item.popupkey_from_query(4,mgr,'select ename, empno from emp',10) mgr, wwv_flow_item.date_popup(6,null,hiredate) hiredate, htmldb_item.text(7,sal,10) sal, htmldb_item.text(8,comm,10) comm, htmldb_item.select_list_from_query(9,deptno,'select dname, deptno from dept') deptno from emp 

  To enhance the display of the report, you can change the report column headings as follows:

  1. Navigate to the Page Definition containing the tabular form.
  2. Under regions, click the Q next to Tabular Form, Update Emp.
  3. For the Heading type, select Custom.
  4. Change the column headings to the following:
     1. EMPNO:  
     2. ENAME: Name
     3. JOB: Job
     4. MGR: Manager
     5. HIREDATE: Hire Date
     6. SAL: Salary
     7. COMM: Commission
     8. DEPTNO: Department

     Figure 4 - Edit column headings

     To submit the tabular form array values, you need to add a button.

     To add a button, follow these steps:

     1. Navigate to the Page Definition containing the tabular form.
     2. Under buttons, click Create.
     3. Select Update EMP 1.10 as the Region.
     4. Select Create a button in a region position and click Next.
     5. Enter UPDATE_EMP as the Button Name.
     6. Enter Update Employee(s) as the Label and click Next.
     7. For the Position, select Top of Region and click Next.
     8. From Branch to Page, select same page containing your tabular form.
     9. Click Create Button.

     To update employee records, you need to add a process:

     1. Under processes, click Create.
     2. Enter Update EMP as the Process Name.
     3. For Process Point, select On Submit - After Computations and Validations.
     4. For Type, select PL/SQL anonymous block and click Next.
     5. Enter following PL/SQL page process and click Next:

     -- -- update EMP -- for i in 1..htmldb_application.g_f01.count loop update emp set job = replace(htmldb_application.g_f03(i),'%'||'null%',NULL), mgr = replace(htmldb_application.g_f04(i),'%'||'null%',null), hiredate = htmldb_application.g_f06(i), sal = htmldb_application.g_f07(i), comm = htmldb_application.g_f08(i), deptno = replace(htmldb_application.g_f09(i),'%'||'null%',NULL) where empno = htmldb_application.g_f01(i); end loop; 

     1. Success Message: Updated employee(s).
     2. Failure Message: Unable to update employee(s).

     When you run the page, you should see a tabular form looks like Figure 5.

     Figure 5 - Manually created tabular form

     Building a Tabular Form with Extra Rows for Insert

     Building a tabular form with an extra row for insert is useful when you wish to have a tabular form that does both insert and update. This section describes how to add an extra insert row to the tabular form.

     To add an extra insert row to a tablular form:

     1. Navigate to the Page Definition containing the tabular form.
     2. Under Regions, click Update EMP.
     3. Under Region Source, change the query as follows:

     select x.empno, x.ename, x.job, x.mgr, x.hiredate, x.sal, x.comm, x.deptno from ( select htmldb_item.hidden(1,empno) empno, wwv_flow_item.display_and_save(2,ename) ename, htmldb_item.select_list_from_query(3,job,'select distinct job, job from emp') job, htmldb_item.popupkey_from_query(4,mgr,'select ename, empno from emp',10) mgr, wwv_flow_item.date_popup(6,null,hiredate) hiredate, htmldb_item.text(7,sal,10) sal, htmldb_item.text(8,comm,10) comm, htmldb_item.select_list_from_query(9,deptno,'select dname, deptno from dept') deptno from emp union all select htmldb_item.hidden(1,null) empno, htmldb_item.text(2,null) ename, htmldb_item.select_list_from_query(3,null,'select distinct job, job from emp') job, htmldb_item.popupkey_from_query(4,null,'select ename, empno from emp',10) mgr, wwv_flow_item.date_popup(6,null,null) hiredate, htmldb_item.text(7,null,10) sal, htmldb_item.text(8,null,10) comm, htmldb_item.select_list_from_query(9,null,'select dname, deptno from dept') deptno from dual) x 

     The primary key column, EMPNO, is not an updateable column on the tabular form. Since the primary key is required to insert the data into the EMP table, you need to have a trigger populate the primary key using a sequence. You can create a sequence and trigger in SQL Workshop.

     To create a sequence and trigger using SQL Workshop:

     1. Navigate to SQL Workshop.
     2. Click Create Object, select Sequence, and click Next. Figure 6 - Create object from SQL Workshop
     3. For the Sequence Name, enter EMP_SEQ and click Next.
     4. Click Create to create the sequence.
     5. On the success page, click the SQL Workshop Home icon.
     6. Click Create Object, select Trigger, and click Next.
     7. Select the schema where you have EMP table and click Next.
     8. Select the EMP table and click Next.
     9. Under Action, select Create Trigger and click Next.
     10. Under Create Trigger, specify the following and click Next:
         1. For the Trigger name, enter BI_EMP.
         2. For Firing Point, select BEFORE.
         3. For options, select insert.
         4. Enter following as trigger body:

         select emp_seq.nextval into:new.empno from dual; 

         -- -- update EMP -- for i in 1..htmldb_application.g_f01.count loop if htmldb_application.g_f01(i) is not null then update emp set job = replace(htmldb_application.g_f03(i),'%'||'null%',NULL), mgr = replace(htmldb_application.g_f04(i),'%'||'null%',null), hiredate = htmldb_application.g_f06(i), sal = htmldb_application.g_f07(i), comm = htmldb_application.g_f08(i), deptno = replace(htmldb_application.g_f09(i),'%'||'null%',NULL) where empno = htmldb_application.g_f01(i); else if htmldb_application.g_f02(i) is not null then insert into emp (ename, job, mgr, hiredate, sal, comm, deptno) values (htmldb_application.g_f02(i), replace(htmldb_application.g_f03(i),'%'||'null%',NULL), replace(htmldb_application.g_f04(i),'%'||'null%',NULL), htmldb_application.g_f06(i), htmldb_application.g_f07(i), htmldb_application.g_f08(i), replace(htmldb_application.g_f09(i),'%'||'null%',NULL)); end if; end if; end loop; 

         Figure 7 - Tabular form with extra row for insert

         Adding Optimistic Lockingoptimistic locking

         Since you created this tabular form manually, optimistic locking is not done automatically behind the scenes. You need to add an optimistic locking to the tabular form in order to keep the data integrity. You can use HTMLDB_ITEM.MD5_CHECKSUM to include MD5 checksum values on the form. When the page is submitted, HTMLDB_ITEM.MD5_CHECKSUM populates the global HTMLDB_APPLICATION.G_FCS with a checksum of the values provided.

         To add the checksum values, you need to edit the tabular form query as follows:

         1. Navigate to the Page Definition containing the tabular form.
         2. Under Regions, click Update EMP.

         select x.empno, x.ename, x.job, x.mgr, x.hiredate, x.sal, x.comm, x.deptno, x.cks from ( select htmldb_item.hidden(1,empno) empno, wwv_flow_item.display_and_save(2,ename) ename, htmldb_item.select_list_from_query(3,job,'select distinct job, job from emp') job, htmldb_item.popupkey_from_query(4,mgr,'select ename, empno from emp',10) mgr, wwv_flow_item.date_popup(6,null,hiredate) hiredate, htmldb_item.text(7,sal,10) sal, htmldb_item.text(8,comm,10) comm, htmldb_item.select_list_from_query(9,deptno,'select dname, deptno from dept') deptno, htmldb_item.md5_checksum(job,mgr,hiredate,sal,comm,deptno) cks from emp union all select htmldb_item.hidden(1,null) empno, htmldb_item.text(2,null) ename, htmldb_item.select_list_from_query(3,null,'select distinct job, job from emp') job, htmldb_item.popupkey_from_query(4,null,'select ename, empno from emp',10) mgr, wwv_flow_item.date_popup(6,null,null) hiredate, htmldb_item.text(7,null,10) sal, htmldb_item.text(8,null,10) comm, htmldb_item.select_list_from_query(9,null,'select dname, deptno from dept') deptno, htmldb_item.md5_checksum(null,null,null,null,null,null) cks from dual) x 

         To assure the data integrity, next you need to edit the update process to get the original checksum values and compare them with the submitted checksum values. If they are not same, the application will raise an error.

         To implement this, change the update process as follows:

         1. Navigate to the Page Definition containing the tabular form.
         2. Under Processes, click Update EMP.
         3. Change the PL/SQL process as follows:

         declare l_cks wwv_flow_global.vc_arr2; j pls_integer := 1; begin -- -- Get original MD5 checksum -- select wwv_flow_item.md5(job,mgr,hiredate,sal,comm,deptno) cks BULK COLLECT INTO l_cks from emp; -- -- Compare the original checksum, l_cks, -- with submitted checksum, htmldb_application.g_fcs. -- If they are different, raise an error. -- for i in 1..l_cks.count loop if htmldb_application.g_fcs(i) != l_cks(i) then rollback; raise_application_error( -20001, 'Current version of data in database has changed '|| 'since user initiated update process.'); return; end if; end loop; -- -- update EMP -- for i in 1..htmldb_application.g_f01.count loop if htmldb_application.g_f01(i) is not null then update emp set job = replace(htmldb_application.g_f03(i),'%'||'null%',NULL), mgr = replace(htmldb_application.g_f04(i),'%'||'null%',null), hiredate = htmldb_application.g_f06(i), sal = htmldb_application.g_f07(i), comm = htmldb_application.g_f08(i), deptno = replace(htmldb_application.g_f09(i),'%'||'null%',NULL) where empno = htmldb_application.g_f01(i); else if htmldb_application.g_f02(i) is not null then insert into emp (ename, job, mgr, hiredate, sal, comm, deptno) values (htmldb_application.g_f02(i), replace(htmldb_application.g_f03(i),'%'||'null%',NULL), replace(htmldb_application.g_f04(i),'%'||'null%',NULL), htmldb_application.g_f06(i), htmldb_application.g_f07(i), htmldb_application.g_f08(i), replace(htmldb_application.g_f09(i),'%'||'null%',NULL)); end if; end if; end loop; end; 

         Adding Validation Using Collections

         Collections enable you to temporarily capture one or more non-scalar values. You can use collections to store rows and columns currently in session state so they can be accessed, manipulated, or processed during a user's specific session. Think of a collection as a bucket in which you can temporarily store and name rows of information. This section describes how to add validations to a tabular form. The tabular form does not keep session state of the user input when the validations fail. To save the user input, you can use collections.

         To start, you first create a not null validation to validate the user input.

         To create a not null validation to validate the user input:

         1. Navigate to the Page Definition containing the tabular form.
         2. Under Validations, click Create.
         3. Under Validation Attributes, specify the following and click Next.
            1. Enter not null as the validation name.
            2. For the Error Display Location, select Inline in Notification.
            3. For the Validation Type, select Function Returning Error Text.

for i in 1..htmldb_application.g_f01.count loop if htmldb_application.g_f01(i) is not null then if replace (htmldb_application.g_f03(i),'%'||'null%',null) is null then return 'Job must have some value.'; end if; if htmldb_application.g_f06(i) is null then return 'Hiredate must have some value.'; end if; if htmldb_application.g_f07(i) is null then return 'Sal must have some value.'; end if; if replace(htmldb_application.g_f09(i),'%'||'null%',null) is null then return 'Deptno must have some value.'; end if; else if htmldb_application.g_f02(i) is not null then if replace(htmldb_application.g_f03(i),'%'||'null%',null) is null then return 'Job must have some value.'; end if; if htmldb_application.g_f06(i) is null then return 'Hiredate must have some value.'; end if; if htmldb_application.g_f07(i) is null then return 'Sal must have some value.'; end if; if replace(htmldb_application.g_f09(i),'%'||'null%',null) is null then return 'Deptno must have some value.'; end if; end if; end if; end loop;