In some cases your Tabular Form will need to provide a column of type Checkbox to give your users a possibility to "flag" a record. Usually it will be "Yes" or "Y" for an activated checkbox and "No" or "N" for the not checked (empty) state. In earlier versions of APEX such a requirement would cause a lot of coding just to create a workaround for a simple problem. The Checkbox item is different than the other items in APEX—its value will not be submitted to the server for the empty state. This means that the array (g_f01) would contain the values for the checked items only; there was no really good way to get around this problem.

APEX 4 changed this for the better by introducing the Single Checkbox item. This item can be used like any other item and is capable of storing either a single value for checked (activated) or NULL for the empty state.

In order to demonstrate how this feature works, we will need to do a couple of preparation steps first:

ALTER TABLE emp MODIFY (ename VARCHAR2(40) NOT NULL);

ALTER TABLE emp ADD (valid VARCHAR2(1));

CREATE TABLE emp_bkp AS SELECT * FROM emp;

CREATE TABLE dept_bkp AS SELECT * FROM dept;

CREATE SEQUENCE dept_seq START WITH 50 INCREMENT BY 10 NOCACHE;

CREATE SEQUENCE emp_seq START WITH 7950 INCREMENT BY 1 NOCACHE;

CREATE OR REPLACE TRIGGER dept_tr
   BEFORE INSERT
   ON dept
   FOR EACH ROW
BEGIN
   IF :NEW.deptno IS NULL

THEN
      SELECT dept_seq.NEXTVAL
        INTO :NEW.deptno
        FROM DUAL;
   END IF;
END;
/
CREATE OR REPLACE TRIGGER emp_tr
   BEFORE INSERT
   ON emp
   FOR EACH ROW
BEGIN
   IF :NEW.empno IS NULL
   THEN
      SELECT emp_seq.NEXTVAL
        INTO :NEW.empno
        FROM DUAL;
   END IF;
END;
/

Now, editing the Report Attributes and the newly created column properties for the column VALID you can change Display As to Simple Checkbox as shown in Figure 3-2.

Figure 3-2. Using Simple Checkbox item type

Using the List of Values tab you will need to enter the required static LOV for a simple checkbox (see Figure 3-3). If you require only one value, you will need to change the list of values definition to Y instead of Y,N.

Figure 3-3. Simple Checkbox LOV

After applying the changes, running the application and opening the page with the Tabular Form, you should see a result similar to Figure 3-4. Figure 3-5 shows the raw data from the EMP table that underlies the form.

Figure 3-4. Simple Checkbox in a Tabular Form

Figure 3-5. EMP table

If you now activate a couple of checkboxes and submit the changes, the Tabular Form will update the column to the expected values for the displayed set of rows only.

The APEX 4 wizard for creating Tabular Forms will do some additional work for you and save you some time. If you have columns of type DATE or TIMESTAMP in your table and include those in a Tabular Form, APEX will automatically set it up as a Date Picker column using the new jQuery calendar, as shown in Figure 3-6.

Figure 3-6. jQuery Date Picker in a Tabular Form

Unfortunately, you cannot extend that calendar feature the way you can in simple forms and specify number of months or add a year range to it. Hopefully that functionality will be included in one of the next releases.

The Radio Group is the next Tabular Form item type which came with release 4 of APEX. In earlier versions you were able to manually create that item type by using the apex_item.radiogroup packaged function. The disadvantage of doing so was that APEX would create one array per entry in the Radio Group and you needed to write your own code in order to handle that problem while inserting or updating the record.

To show how this feature works, you will now change the item type for the column VALID to Radio Group (Static LOV), as shown in Figure 3-7. Change the "List of values definition" to

STATIC:Yes;Y,No;N

Figure 3-7. Changing Column Attributes to Radio Group

After applying the changes, running the application, and opening the page with the Tabular Form, you should see a result similar to Figure 3-8.

Figure 3-8. Tabular Form with Radio Group column

The limitation for the select list item type regarding the number of entries you could use was one of the biggest problems in earlier releases of APEX. As soon as the list grew over a certain size (a combination of the number and size of the available options) you would receive an error saying the character string buffer is too small (see Figure 3-9).

Figure 3-9. Select list error

For the simple forms, the workaround was to use a popup LOV returning the key value, but for Tabular Forms this simply wasn't possible. APEX 4 changed this by including this item type for Tabular Forms.

To test this feature you will need to modify your Tabular Form by editing the column DEPTNO and changing it to a "Popup Key LOV (query based LOV)" and adding the following query to the "List of values definition":

SELECT dname d, deptno r
  FROM dept

If you now apply the changes, run the application, and sort the Tabular Form using the DEPTNO column, it should show the popup key LOV for that column as in Figure 3-10.

Figure 3-10. Tabular Form with a Popup Key LOV

One additional change was added to the select list item type in general. In earlier versions of APEX the sorting on a column containing a select list would sort the values on the return value. Often this was not desirable. Since APEX 4, this behavior has changed and the sorting is done on the displayed value, as you can see in Figure 3-10.

After creating a Tabular Form, you will find a couple of validations created by the wizard. An example of these validations is shown in Figure 3-12.

The APEX wizard will use the table definition and sort out the constraints you defined for your table. It will create a validation for each single column containing a NOT NULL definition (unless the column is a primary key used for the DML processes), or for columns of type numeric and columns of type date.

Figure 3-12. Tabular Form validations—created by the wizard

As you can see in Figure 3-12, the APEX Tabular Form wizard created several validations automatically. If you remember the changes made in Listing 3-1, you will notice that this change resulted in a validation checking the ENAME column (Validation with sequence 30 – ENAME IS NOT NULL). The other validations are related to the data type of all existing numeric and date columns included in the Tabular Form.

If you want to test the validations created by the wizard, you can make a couple of changes in your table to see how they work, as shown in Figure 3-13.

Figure 3-13. Tabular Form validations

Change the entry for the column ENAME to NULL and modify the entry for the column SALARY to an alphanumeric character. If you try to save that, you should receive an error like the one shown in Figure 3-14.

Figure 3-14. Tabular Form validations – error highlighting

If you remember how such validations worked before version 4, you will notice a few changes:

In earlier versions of APEX a lot of custom coding was required to get a similar functionality. To avoid losing the changes you made after an unsuccessful validation, it was required to display the validation errors on an error page and press the back button.

One more important thing needs to be mentioned. If you edit one of the validations created by the wizard, you will notice that there is a new substitution string in the Error Message. You can use #COLUMN_HEADER# for the heading of the associated Tabular Form column in order to display the header name as a part of the error message, as shown in Figure 3-15.

Figure 3-15. Tabular Form validations—substitution string for column names

APEX 4 will inform you about the changes you made and about the risk of losing your changes if you try to change the sorting of the Tabular Form or try to paginate through the Tabular Form. Figure 3-16 shows this warning message.

Figure 3-16. Tabular Form— lost update protection

In earlier versions, the process for adding new rows to a Tabular Form would require you to submit and load your page once per new row. This process had to validate and save changes each time you added a new row. The Add Row functionality is done by using new JavaScript function:

javascript:addRow();

You can find the function's invocation in the ADD button, as the URL Target.

After the initial rendering of a Tabular Form, APEX will remove that new row from the DOM and put it in a JavaScript variable. After pressing the ADD button, JavaScript will replace some of the substitution strings in that variable (index, names, etc.) and add that row again to the end of the table. If you want to add multiple rows to a tabular form, the only thing you need to do is to press the button again.

The addition of functionality to add new rows using client-side code brings the additional benefit of reducing the number of processes required for DML. Earlier versions of APEX required four submit processes for DML:

APEX 4 requires only two of these processes. Only one MRU process is now needed, as new rows are no longer added using server-side code. For the same reason, the AddRow process is redundant, and has been removed.

APEX 4 Tabular Form wizard creates a hidden checksum item for every Tabular Form. This checksum will be used for later automatic MRU and MRD processes. Using the Firebug extension of Firefox, you can see that hidden item if you investigate the generated HTML, as shown in Figure 3-19.

Figure 3-19. Tabular Form— checksum

When talking about checksum and data integrity, you need to keep several issues in mind:

Following the rules outlined in the previous section, you can start extending your package by adding some new functions:

Let's extend the package tab_form_emp_pkg by adding the first function described, shown in Listing 3-4.

FUNCTION compare_checksum_change (p_array IN NUMBER)
 RETURN BOOLEAN;

FUNCTION compare_checksum_change (p_array IN NUMBER)
      RETURN BOOLEAN
   IS
   BEGIN
      IF apex_application.g_f02 (p_array) IS NOT NULL
      THEN
         IF apex_application.g_fcs (p_array) <>
          wwv_flow_item.md5 (apex_application.g_f02 (p_array),
                             apex_application.g_f03 (p_array),
                             apex_application.g_f04 (p_array),
                             apex_application.g_f05 (p_array),
                             apex_application.g_f06 (p_array),
                             apex_application.g_f07 (p_array),
                             apex_application.g_f08 (p_array),
                             apex_application.g_f09 (p_array),
                             apex_application.g_f11 (p_array)
                             )
         THEN
            RETURN FALSE;
         ELSE
            RETURN TRUE;
         END IF;
      ELSE
         RETURN TRUE;
      END IF;
END compare_checksum_change;

After that, add the second function, shown in Listing 3-5.

FUNCTION compare_checksum_table (p_array IN NUMBER)
  RETURN BOOLEAN;

FUNCTION compare_checksum_table (p_array IN NUMBER)
      RETURN BOOLEAN

IS
      v_empno          NUMBER;
      v_emp_checksum   VARCHAR2 (40);
   BEGIN
      IF apex_application.g_f02 (p_array) IS NOT NULL
      THEN
         v_empno := apex_application.g_f02 (p_array);

         SELECT wwv_flow_item.md5 (empno,
                                   ename,
                                   job,
                                   mgr,
                                   hiredate,
                                   sal,
                                   comm,
                                   deptno,
                                   valid
                                  )
           INTO v_emp_checksum
           FROM emp
          WHERE empno = v_empno;

         IF apex_application.g_fcs (p_array) <> v_emp_checksum
         THEN
            RETURN FALSE;
         ELSE
            RETURN TRUE;
         END IF;
      ELSE
         RETURN TRUE;
      END IF;
END compare_checksum_table;

As the last function, you can now create a validation function returning VARCHAR2 which you will then call on the page, as shown in Listing 3-6.

FUNCTION validate_data_integrity
      RETURN VARCHAR2;

   FUNCTION validate_data_integrity
      RETURN VARCHAR2
   IS
      v_error   VARCHAR2 (4000);
   BEGIN
      FOR i IN 1 .. apex_application.g_f02.COUNT
      LOOP
         IF NOT compare_checksum_change (i)
            –- we changed the row
            AND NOT compare_checksum_table (i)
            -– however the table data has changed

THEN
            v_error :=
                  v_error
               || '<br/>'
               || 'Row '
               || i
               || ': The version of the data in the '
               || 'table has been change since the last page '
               || 'rendering. Click <a href="f?p='
               || v ('APP_ID')
               || ':'
               || v ('APP_PAGE_ID')
               || ':'
               || v ('APP_SESSION')
               || '">here</a> to reload the page.';
         END IF;
      END LOOP;
      v_error := LTRIM(v_error, '<br/>'),
      RETURN v_error;
   END validate_data_integrity;

You can test this code by creating a page validation of type PL/SQL Function Returning Error Message, using sequence number 1 for the process, and naming the process Check Data Integrity. Set the process to unconditional and enter the required call to the package procedure. Here is the PL/SQL code for the page validation:

BEGIN
   RETURN tab_form_emp_pkg.validate_data_integrity;
END;

Next, change one of the records using some other tool such as SQL Workshop, and commit your changes. Go to the Tabular Form, change the same record and submit the change. You should see an error message similar to that in Figure 3-20.

Figure 3-20. Tabular Form—Data Integrity Validation Error

You don't need to use the technique described in this section in a standard Tabular Form. A similar message will be generated automatically in a standard form. You need the technique and code in this section only in a manually generated Tabular Form.

The goal here is to show how to work with collections using Tabular Forms. This section will demonstrate a couple of possible ways and techniques to put collections to use. Specifically, you will:

You will continue with the methods used earlier in this chapter, and keep all of your code in a package. You will use APEX regions and processes only to make calls into your package code.

First, create a procedure and a view. Listing 3-9 shows a procedure for creating a collection containing all employees of one department. Also in the listing is a view created upon that collection.

PROCEDURE create_emp_collection (p_deptno IN NUMBER,
                                 p_message OUT VARCHAR2);

PROCEDURE create_emp_collection (p_deptno IN NUMBER,
                                 p_message OUT VARCHAR2)
IS
   v_collection   VARCHAR2 (40) := 'EMP_DEPT';
BEGIN
   IF apex_collection.collection_exists (v_collection)
   THEN
      apex_collection.delete_collection (v_collection);
      p_message := 'Collection deleted.';
   END IF;

   apex_collection.create_collection_from_query
     (v_collection,
      'SELECT a.*,  wwv_flow_item.md5(empno, ename, job, '
   || 'mgr, hiredate, sal, comm, deptno, valid)  '
   || 'FROM EMP a WHERE deptno = '
   || p_deptno
      );
   p_message := p_message || '<br/>' || 'Collection created.';
   p_message := LTRIM (p_message, '<br/>'),
 END create_emp_collection;

 CREATE OR REPLACE VIEW emp_coll_v
 AS
    SELECT seq_id, c001 empno, c002 ename,
           c003 job, c004 mgr, c005 hiredate,
           c006 sal, c007 comm, c008 deptno,
           c009 valid, c010 checksum, c011 delete_flag
      FROM apex_collections
     WHERE collection_name = 'EMP_DEPT';

The procedure will check whether the collection exists. If the collection does exist, the procedure will delete the existing collection and create a new one based on the input. Otherwise, the procedure creates a new collection.

The view will make it easier to deal with the collection. You will not need to remember the member number in order to insert, update, or delete a row. The view takes care of the member number for you.

You can now start creating a new page, which will be page 3. Use a standard Tabular Form based on the view emp_coll_v. Include all the columns. The primary key will be the combination of the SEQ_ID and EMPNO column. Make all columns editable. Make the region title Tabular Form Collection. After creating the page, change the item type for the columns CHECKSUM and DELETE_FLAG to Hidden.

You will also need to edit the generated SQL for the Tabular Form and add a condition as follows:

WHERE delete_flag IS NULL

This condition will exclude those records from the collection that are marked as deleted.

You will create a select list within the Tabular Form region: P3_DEPTNO. This select list will show a list of available departments based on the SQL query:

SELECT dname, deptno
  FROM dept

You will also need a button displayed after the select list (create a button displayed among this region's items) which you will use to trigger the process. Name this button P3_GO and assign it a request GO.

Now you can start creating a page process (Create Collection) on submit for creating a collection. You will use the following PL/SQL block for this:

BEGIN
   tab_form_emp_pkg.create_emp_collection (:p3_deptno, :t_message);
END;

Make the block conditional to run based on the following PL/SQL Expression:

:REQUEST IN ('GO')

Do not forget to code the following as the success message of the process:

&T_MESSAGE.

Finally, everything is set for testing what you have done. If you select the sales department from the list and press the GO button, you should get a result similar to the one shown in Figure 3-27.

Figure 3-27. Tabular Form on APEX Collection

The question now is, how do you update your collection? Following are three possible methods for updating the collection:

The sections to follow describe each of these methods in detail.

The ideal way to update the collection would be to create three instead of triggers on the view for updating, deleting, and inserting of rows. Thinking further, imagine writing a package that would automatically create those triggers for you. You would just need to provide your collection query and the package would generate all of the code for use. You would just place the resulting code in your application and it would run with the automatic DML process of Tabular Form. Such an approach is, sadly, too good to be true. Currently, you can't get the instead of triggers to work with collections. If you try using instead of triggers, you will receive error messages such as those shown in Figure 3-28.

Figure 3-28. Tabular Form on APEX Collection—Instead of trigger error

You'll immediately notice that the error in Figure 3-28 has to do with privileges. An APEX Collection is accessible only from the session context, and the trigger is not running in that session context. You would need to grant access privileges on WWV_FLOW_COLLECTION to your schema, and this is definitely not the way to go since you would need to change the APEX source code as provided by Oracle Corporation. Such a change would also pose a security issue. For these reasons, the instead of trigger approach, while nice to contemplate, is simply not feasible.

Before you start writing packaged procedures, you should delete the automatic DML processes that the wizard created for you on page 3 of your application. You will need to create two procedures in your package. These procedures are similar to those you wrote for the manual Tabular Forms. Add the code shown in Listing 3-10 to the package.

PROCEDURE save_emp_coll_custom (p_message OUT VARCHAR2);

PROCEDURE delete_emp_coll_custom (p_message OUT VARCHAR2);
PROCEDURE save_emp_coll_custom (p_message OUT VARCHAR2)
IS
   v_ins_count    INTEGER       := 0;

v_upd_count    INTEGER       := 0;
   v_collection   VARCHAR2 (40) := 'EMP_DEPT';
BEGIN
   FOR i IN 1 .. apex_application.g_f02.COUNT
   LOOP
      BEGIN
         IF apex_application.g_f02 (i) IS NOT NULL
         THEN
            IF apex_application.g_f12 (i) <>
                  wwv_flow_item.md5 (apex_application.g_f03 (i),
                                     apex_application.g_f04 (i),
                                     apex_application.g_f05 (i),
                                     apex_application.g_f06 (i),
                                     apex_application.g_f07 (i),
                                     apex_application.g_f08 (i),
                                     apex_application.g_f09 (i),
                                     apex_application.g_f10 (i),
                                     apex_application.g_f11 (i)
                                     )
   THEN
   apex_collection.update_member
   (p_collection_name  => v_collection,
    p_seq              => apex_application.g_f02(i),
    p_c001             => apex_application.g_f03(i),
    p_c002             => apex_application.g_f04(i),
    p_c003             => apex_application.g_f05(i),
    p_c004             => TO_NUMBER(apex_application.g_f06(i)),
    p_c005             => TO_DATE(apex_application.g_f07(i)),
    p_c006             => TO_NUMBER(apex_application.g_f08(i)),
    p_c007             => TO_NUMBER(apex_application.g_f09(i)),
    p_c008             => TO_NUMBER(apex_application.g_f10(i)),
    p_c009             => apex_application.g_f11(i),
    p_c010             => apex_application.g_f12(i),
    p_c011             => apex_application.g_f13(i));
               v_upd_count := v_upd_count + 1;
            END IF;
   ELSE
   apex_collection.add_member
   (p_collection_name  => v_collection,
    p_c001             => emp_seq.NEXTVAL,
    p_c002             => apex_application.g_f04(i),
    p_c003             => apex_application.g_f05(i),
    p_c004             => TO_NUMBER(apex_application.g_f06(i)),
    p_c005             => TO_DATE(apex_application.g_f07(i)),
    p_c006             => TO_NUMBER(apex_application.g_f08(i)),
    p_c007             => TO_NUMBER(apex_application.g_f09(i)),
    p_c008             => TO_NUMBER(apex_application.g_f10(i)),
    p_c009             => apex_application.g_f11(i)
                                        );
            v_ins_count := v_ins_count + 1;
         END IF;
      EXCEPTION

WHEN OTHERS
         THEN
            p_message :=    p_message
                         || '<br/>'
                         || 'Row: '
                         || i
                         || ' > '
                         || SQLERRM;
            p_message := LTRIM (p_message, '<br/>'),
      END;
   END LOOP;

   IF v_ins_count > 0 OR v_upd_count > 0
   THEN
      p_message :=
            p_message || '<br/>'
         || v_ins_count
         || ' row(s) inserted. '
         || v_upd_count
         || ' row(s) updated.';
   END IF;
            p_message := LTRIM (p_message, '<br/>'),
EXCEPTION
   WHEN OTHERS
   THEN p_message := SQLERRM;
END save_emp_coll_custom;

PROCEDURE delete_emp_coll_custom (p_message OUT VARCHAR2)
IS
   v_row          INTEGER;
   v_count        INTEGER       := 0;
   v_collection   VARCHAR2 (40) := 'EMP_DEPT';
BEGIN
   FOR i IN 1 .. apex_application.g_f01.COUNT
   LOOP
      v_row := apex_application.g_f01 (i);
      apex_collection.update_member
        (p_collection_name      => v_collection,
        p_seq                  => apex_application.g_f02(v_row),
        p_c011                 => 'Y'),
      v_count := v_count + 1;
   END LOOP;
   p_message := v_count || ' row(s) deleted.';
END delete_emp_coll_custom;

Before you can start testing this code, you will create two on submit processes on application page 3:

:REQUEST IN ('MULTI_ROW_DELETE')

You are going to use the following PL/SQL blocks to run these processes. The first PL/SQL block is for the update process and the second one is for the delete member process:

BEGIN
   tab_form_emp_pkg.save_emp_coll_custom (:t_message);
END;

BEGIN
   tab_form_emp_pkg.delete_emp_coll_custom (:t_message);
END;

And, of course, do not forget to put the following into the Process Success Message section:

&T_MESSAGE.

Now, you will test your new version of page 3 and change one of the records in your Tabular Form. Figure 3-29 shows a change being made.

Figure 3-29. Making a change to a Tabular Form built atop a collection

You should get a similar result to the one shown in Figure 3-30.

Figure 3-30. A success message from making the change shown in Figure 3-29

Since a collection is only a snapshot of the original data, you can use a different approach for an update. You can create some JavaScript code and combine that with an On-Demand Process to update the collection when a single Tabular Form item changes. The validation of the collection values will be done only if you decide to save the collection back to the source. You will include only some basic validations for numeric and date columns in this code.

The code you need to write for this demonstration consists of

The procedure code you will add to your package is shown in Listing 3-11.

PROCEDURE update_emp_coll_member (
   p_seq_id             IN       NUMBER,
   p_attribute_number   IN       NUMBER,
   p_attribute_value    IN       VARCHAR2
);
PROCEDURE update_emp_coll_member (

p_seq_id             IN   NUMBER,
   p_attribute_number   IN   NUMBER,
   p_attribute_value    IN   VARCHAR2
)
IS
   v_collection   VARCHAR2 (40)   := 'EMP_DEPT';
   v_number       NUMBER;
   v_date         DATE;
   v_message      VARCHAR2 (4000);
BEGIN
   IF p_seq_id IS NOT NULL
   THEN
      IF p_attribute_number IN (4, 6, 7, 8)
      THEN
         v_number := TO_NUMBER (p_attribute_value);
      ELSIF p_attribute_number IN (5)
      THEN
         v_date := TO_DATE (p_attribute_value);
      END IF;

      apex_collection.update_member_attribute
          (p_collection_name      => v_collection,
           p_seq                  => p_seq_id,
           p_attr_number          => p_attribute_number,
           p_attr_value           => p_attribute_value
                                        );
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      v_message := SQLERRM;
      HTP.p (v_message);
END update_emp_coll_member;

The JavaScript code shown in Listing 3-12, added to the page HTML header of application page 3, will call the On-Demand Process and check the return value of that process for errors.

<script language="JavaScript" type="text/javascript">
 function LPad(ContentToSize,PadLength,PadChar)
  {
     var PaddedString=ContentToSize.toString();
     for(i=ContentToSize.length+1;i<=PadLength;i++)
     {PaddedString=PadChar+PaddedString;}
     return PaddedString;
  }

 function f_update_emp_coll_member(pThis,pRownum,pAttribNo){
    var v_seq_array = 'f02_' + LPad(pRownum, 4, "0");
    var SeqID =  $x(v_seq_array).value;
    var get = new htmldb_Get(null,$x('pFlowId').value,

'APPLICATION_PROCESS=update_emp_coll_member',0);
    get.add('T_COL_VAL_ITEM',pThis);
    get.add('T_COL_SEQ_ITEM',SeqID);
    get.add('T_COL_ATTR_ITEM',pAttribNo);
    gReturn = get.get();
    if(gReturn) {alert(gReturn)}
    get = null;
}
</script>

The code to implement the On-Demand Process update_emp_coll_member will be a simple PL/SQL block:

BEGIN
   tab_form_emp_pkg.update_emp_coll_member (:t_col_seq_item,
                          :t_col_attr_item,
                          :t_col_val_item);
END;

The last thing you need to do is to call this code from the Tabular Form. You do that by entering code similar to the following for each column on the form. Place the code in the Element Attributes field under Column Properties. Following is the code for the ENAME column:

onchange="f_update_emp_coll_member(this.value,'#ROWNUM#',2);"

The third parameter of the JavaScript function is the attribute number of the column in question. Take care to adjust that value to the right one for each column on the form. The mapping in the view code (Listing 3-9) can help you do that.

Now run page 3 and try to update some of the rows in the Tabular Form, as shown in Figure 3-31.

Figure 3-31. Updating rows on the tabular form

You can also activate Firebug and watch what happens at the browser level, as shown in Figure 3-32. Activate Firebug and switch to the console tab. After updating a column, you should see the process running in Firebug. Opening that process will display more detail.

Figure 3-32. Watching the On-Demand Process from Firebug

The final step in this exercise is to save the collection data back to the table. For that you will create

Listing 3-13 shows the code for these procedures.

FUNCTION validate_collection_data
   RETURN VARCHAR2;

PROCEDURE update_table_from_collection (
   p_deptno    IN       NUMBER,
   p_message   OUT      VARCHAR2
);

FUNCTION validate_collection_data
   RETURN VARCHAR2
IS
   v_message    VARCHAR2 (4000);
   v_checksum   VARCHAR2 (400);
BEGIN
   FOR c IN (SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno,
                    valid, checksum, delete_flag
               FROM emp_coll_v
              WHERE checksum IS NOT NULL)
   LOOP
      SELECT wwv_flow_item.md5 (empno,
                                ename,
                                job,
                                mgr,
                                hiredate,
                                sal,
                                comm,
                                deptno,

valid
                                )
        INTO v_checksum
        FROM emp
       WHERE empno = c.empno;

      IF c.checksum <> v_checksum
      THEN
         v_message :=
               v_message
            || '<br/>'
            || 'Empno: '
            || c.empno
            || ': Snapshot too old.';
      END IF;
   END LOOP;

   v_message := LTRIM (v_message, '<br/>'),
   RETURN v_message;
END validate_collection_data;

PROCEDURE update_table_from_collection (
   p_deptno    IN       NUMBER,
   p_message   OUT      VARCHAR2
)
IS
   v_ins_count   INTEGER         := 0;
   v_upd_count   INTEGER         := 0;
   v_del_count   INTEGER         := 0;
   v_message     VARCHAR2 (4000);
BEGIN
   FOR c IN (SELECT empno, ename, job, mgr, hiredate,
                    sal, comm, deptno,
                    valid, checksum, delete_flag
               FROM emp_coll_v)
   LOOP
      IF c.delete_flag IS NULL AND c.checksum IS NOT NULL
      THEN
         IF c.checksum <>
               wwv_flow_item.md5 (c.empno,
                                  c.ename,
                                  c.job,
                                  c.mgr,
                                  c.hiredate,
                                  c.sal,
                                  c.comm,
                                  c.deptno,
                                  c.valid
                                 )
         THEN
            UPDATE emp
               SET ename = c.ename,

job = c.job,
                   mgr = c.mgr,
                   hiredate = c.hiredate,
                   sal = c.sal,
                   comm = c.comm,
                   deptno = c.deptno
            WHERE empno = c.empno;

            v_upd_count := v_upd_count + 1;
         END IF;
      ELSIF c.delete_flag IS NULL AND c.checksum IS NULL
      THEN
         INSERT INTO emp
                      (empno, ename, job, mgr,
                      hiredate, sal,
                      comm, deptno, valid
                     )
              VALUES (c.empno, c.ename, c.job,
                      c.mgr, c.hiredate, c.sal,
                      c.comm, c.deptno, c.valid
                     );

         v_ins_count := v_ins_count + 1;
      ELSIF c.delete_flag IS NOT NULL AND c.checksum IS NOT NULL
      THEN
         DELETE FROM emp
               WHERE empno = c.empno;

         v_ins_count := v_ins_count + 1;
      END IF;
   END LOOP;

   p_message :=
         p_message
      || '<br/>'
      || v_ins_count
      || ' row(s) inserted. '
      || v_upd_count
      || ' row(s) updated. '
      || v_del_count
      || ' row(s) deleted.';

   create_emp_collection (p_deptno, v_message);
   p_message :=
         p_message
      || '<br/>' || v_message;
   p_message := LTRIM (p_message, '<br/>'),
EXCEPTION
   WHEN OTHERS
   THEN
      p_message := SQLERRM;
END update_table_from_collection;

To be able to do an update of the table, you will need to create a button on your page 3 and name it

SAVE_DATA

You can now call the validation function and the update process on application page 3. You will create a page level validation of type PL/SQL Function Returning Error Message – Validate Collection Data and make it conditional upon the new button you created (SAVE_DATA):

BEGIN
   RETURN tab_form_emp_pkg.validate_collection_data;
END;

The process Save Collection to Table will be on submit, and it will be conditional when the button is pressed (SAVE_DATA). You will also enter

&T_MESSAGE.

in the Success Message of the process

BEGIN
tab_form_emp_pkg.update_table_from_collection
                                (:p3_deptno,:t_message);
END;

Now make a couple of changes in your collection and confirm that the code works as expected. Figure 3-33 shows some changes to be saved to the source table. Figure 3-34 shows the success message from saving those changes.

Figure 3-33. Changes to be saved to the source table

Figure 3-34. The success message