How do I use the APP_MULTI package to support multi-selection in my Oracle Form?

Question

Oracle provides the APP_MULTI package (found in the APPCORE library) to support multi-selection of records in Oracle Forms. What must I do to my form in order to take advantage of this functionality? For example:

• Which triggers must I implement?
• Which APP_MULTI methods should I call and when?

Answer 1

The APP_MULTI package allows you to add multi-selection functionality to your form. This includes:

• A single record may be selected by merely clicking on it.
• Multiple records may be selected by holding down the Ctrl key and clicking on various records.
• Multiple records may be selected in bulk by choosing "Select All" from the application menu.
• Multiple records may be deselected in bulk by choosing "Deselect All" from the application menu.
• Multiple records may be selected in bulk by clicking the first record, then holding down the Shift key, then clicking the last record.

Enabling the functionality

The APP_MULTI package works by responding to the following events on your form:

• DESELECT_ALL
• KEY-CLRFRM
• KEY-CLRREC
• KEY-CREREC
• KEY-DELREC
• POST-BLOCK
• PRE-BLOCK
• SELECT_ALL
• WHEN-CLEAR-BLOCK
• WHEN-MOUSE-CLICK
• WHEN-NEW-RECORD-INSTANCE

Unfortunately, the package cannot automatically "hook" into these events. Rather, you are responsible for creating the necessary triggers and explicitly calling the APP_MULTI.EVENT procedure in each of them, passing along the name of the event being handled. This procedure is declared as follows:

  PROCEDURE event(event_name VARCHAR2,
                  blockname  VARCHAR2 DEFAULT NULL);

For example, your KEY-CLRFRM trigger could be as simple as this:

APP_MULTI.EVENT('KEY-CLRFRM');

Notice that the procedure accepts an optional blockname argument. If blockname is NULL, the procedure will use the value of SYSTEM.TRIGGER_BLOCK instead.

If you want all data blocks on your form to receive multi-selection functionality, you can create your triggers on the form rather than on individual data blocks. If you only want certain data blocks to receive the functionality, you can create the triggers on those data blocks or you can create a trigger on the form and perform your own check to determine if APP_MULTI.EVENT should be called.

Responding to selection/deselection events

You can be notified of selections and deselections by implementing the custom MULTI_RETURN_ACTION trigger (either in your data block or at the top level of your form). This trigger will be fired after every selection or deselection of a record (even when records are being selected/deselected in bulk). In your trigger, you may take advantage of these values to better understand the context:

• The GLOBAL.APPCORE_MULTI_BLOCK global contains the name of the data block being affected by the selection/deselection.
• The GLOBAL.APPCORE_MULTI_ACTION global may be any of the following:
  • 'RECORD_SELECTED' when a record has been selected
  • 'RECORD_DESELECTED' when a record has been deselected
  • 'LABEL_CHANGE' when the "Select All" and "Deselect All" menu items are being enabled or disabled (typically occurs when focus enters or leaves a data block respectively)
• The APP_MULTI.LOOPING package variable may be either TRUE or FALSE, depending on whether or not MULTI_RECORD_ACTION was called within a loop (that is, if records are being selected/deselected in bulk).

During bulk operations, APP_MULTI will call the MULTI_RETURN_ACTION trigger once more after all selections/deselections are complete. During this last call, GLOBAL.APPCORE_MULTI_ACTION will be set to 'LABEL_CHANGE' and APP_MULTI.LOOPING will be set to FALSE. For performance reasons, you may wish to take advantage of this final call to avoid unnecessary calculations during bulk operations. For example, your MULTI_RETURN_ACTION could look something like this:

IF NOT APP_MULTI.LOOPING THEN
  costly_calculation();
END IF;

Determining which records are currently selected

For each data block, APP_MULTI maintains a record group that contains the currently selected record numbers. For a given data block, you may access this record group's only column via the APP_MULTI.GET_GROUP_COL function. You can also determine the number of selected records via the APP_MULTI.GET_GROUP_COUNT function. Both these functions accept the name of a data block as their only parameter.

For example, suppose you have a data block named CUSTOMER with a field named CUSTOMER_NAME. You can print out the names of the selected customers via this code:

PROCEDURE PRINT_SELECTED_CUSTOMER_NAMES IS
  record_number_column GROUPCOLUMN;
  selected_record_count NUMBER;
BEGIN
  record_number_column := APP_MULTI.GET_GROUP_COL('CUSTOMER');
  selected_record_count := APP_MULTI.GET_GROUP_COUNT('CUSTOMER');

  FOR i IN 1 .. selected_record_count LOOP
    DECLARE
      record_number NUMBER;
    BEGIN
      record_number := GET_GROUP_NUMBER_CELL(record_number_column, i);
      GO_RECORD(record_number);
      print(:CUSTOMER.CUSTOMER_NAME);
    END;
  END LOOP;
END;

Implementation details

For each data block with multi-selection enabled, the APP_MULTI package maintains a separate record group whose name is blockname || '_MULTI'. This record group contains one row for each selected record. It's only column (REC_NUM) stores the record number of the selected record. This is the GROUPCOLUMN returned by the APP_MULTI.GET_GROUP_COLUMN function mentioned earlier.

For example, suppose you have a data block named CUSTOMER and you have selected the 1^st, 4^th and 9^th records. The CUSTOMER_MULTI record group will contain this data:

+---------+
| REC_NUM |
+---------+
|       1 |
|       4 |
|       9 |
+---------+

After deselecting the 4^th record, the CUSTOMER_MULTI record group will now look like this:

+---------+
| REC_NUM |
+---------+
|       1 |
|       9 |
+---------+

Although you are able to navigate this record group using the standard built-ins (FIND_GROUP, FIND_COLUMN, GET_GROUP_ROW_COUNT), it is preferable to use the GET_GROUP_COL and GET_GROUP_COUNT functions of the APP_MULTI package instead.