Oracle APEX insideout

Problem:

From time to time you run into a situation where you want to collect information in the backend and send it to the frontend. If you want to set a message in PL/SQL code and pass it to APEX, then you can set a page item or an application item using apex_util.set_session_state, but not in trigger code or in procedures or functions that are called from a trigger. This causes a "can not commit or rollback in a trigger" error. If you want to show the rows that were updated by a trigger as a result of an update on the primary table, then you can't select the rows after the update, because this will result in a mutating table error. You can deal with these issues by making use of APEX collections as you can see in the following case.

Case:

There are several item tables and several combination tables of 2 items (see example right).

Each item and combination can be set to Active or Inactive. When you set an item to inactive, the combinations with this item should also be set to inactive. When you set an item to active, the combinations with this item should not automatically be set to active. This should be evaluated one by one.

Let's take a look at the items services (diensten) and contacts (contacten) and the combinations of these (N:M). If you set a contact to inactive (see screenshot below), all the combinations of services and this contact should also be set to inactive.

After the update you want to get a message with the number of combinations affected, and you want to see a report with the affected rows (see screenshot below).

Implementation:

In the contact form the updates are performed with the automatic row processing process. In this update process a trigger fires that performs an update to the combination table, setting all combinations with this contact to inactive. A message is stored in a message collection, and the IDs of the rows affected are stored in the results collection.

In the trigger before update of each row of epf_contacten the following code is executed:

-- code will only be executed when the value of active (actief) has changed
   IF alg_trigger_validation.has_changed (:new.actief, :old.actief)
   THEN
      CASE :new.actief
         WHEN 'N'
         -- when active is set to No (:new.actief = 'N')
         THEN
         -- update the combinations of services and this contact
               UPDATE   epf_diensten_contacten
                  SET   actief = :new.actief
                WHERE   contact_id = :old.id AND actief = 'J'
            RETURNING   id BULK COLLECT INTO   aa_result_ids;  -- aa_result_ids is of type apex_application_global.n_arr

            v_message := 'Er zijn ' || sql%ROWCOUNT
                         || ' combinaties tussen deze contactpersoon en een dienst op inactief gezet.';
            v_title := 'Inactief gezette combinaties';
         WHEN 'J'
         -- when active is set to Yes (:new.actief = 'J')
         THEN
         -- the inactive combinations of services and this contact should not be updated, but only reported
            SELECT   id
              BULK   COLLECT
              INTO   aa_result_ids
              FROM   epf_diensten_contacten
             WHERE   contact_id = :old.id AND actief = 'N';

            v_message := 'Er zijn ' || aa_result_ids.COUNT
                         || ' combinaties tussen deze contactpersoon en een dienst die nog inactief zijn.';
            v_title := 'Inactief gezette combinaties';
         ELSE
            -- no other values should occur
            NULL;
      END CASE;

-- Then the message collection is filled in the following manner:
      -- first the 2 collections are cleaned
      apex_collection.create_or_truncate_collection (
         epf_constants.gc_apex_msg_collection
      );
      apex_collection.create_or_truncate_collection (
         epf_constants.gc_apex_result_collection

      -- then the new message is put into collection
      apex_collection.add_member (
         p_collection_name   => epf_constants.gc_apex_msg_collection,
         p_c001              => v_message
      );
/*
The result collection is filled by looping through the aa_result_ids collection (associative array). The title that is inserted (v_title) will be used as the region title for the report where the results will be shown. This title depends on the action that is performed and is set in the case statement above.
*/
      -- fill the result collection with the ids found
      FOR i IN 1 .. aa_result_ids.COUNT
      LOOP
         apex_collection.add_member (
            p_collection_name   => epf_constants.gc_apex_result_collection,
            p_c001              => v_title,
            p_c002              => aa_result_ids (i)
         );
      END LOOP;
   END IF;

After this update process I have created the following page process, which retrieves the message and stores it into an application item:
BEGIN
  IF APEX_COLLECTION.COLLECTION_MEMBER_COUNT (:SS_APPL_MESSAGE_COLL) > 0
  THEN
    SELECT   c001
      INTO   :F109_AI_APPL_MESSAGE
      FROM   apex_collections
     WHERE   collection_name = :SS_APPL_MESSAGE_COLL AND seq_id = 1;
  END IF;
END;
Note that I am using substitution strings for the named collections, so that these names can be centrally administered.

The application item F109_AI_APPL_MESSAGE is appended to the Success Message of the previous automatic DML process:
Action Processed. &F109_AI_APPL_MESSAGE.

After this the branch is taken to the page with a report showing the contacts.

At the top of this page the success message shows including the message generated in the trigger (screenshot).
In this report page there is a second report with the affected rows, which shows conditionally when the results collection contains records.
The results are presented based on the following query:
SELECT   edcv.id,
         edcv.dienst_id,
         edcv.dienst_naam,
         edcv.dienst_omschrijving,
         edcv.dienst_actief,
         edcv.contact_id,
         edcv.contact_naam,
         edcv.contact_e_mail,
         edcv.contact_telefoon,
         edcv.contact_actief,
         edcv.dienst_contact_actief,
         edcv.aantal_actieve_combinaties
  FROM   epf_diensten_contacten_c_vw edcv, apex_collections ac
 WHERE   edcv.id = ac.c002
   AND   ac.collection_name = :SS_APPL_RESULT_COLL
Note that column c002 of the collection contains the ID of the result set that was created with the trigger code above.

To make sure that the message and the results don't show up when you go back to this page in a different way, the application item containing the message is cleared, and also the collections are reset:
apex_collection.create_or_truncate_collection (:SS_APPL_RESULT_COLL);
apex_collection.create_or_truncate_collection (:SS_APPL_MESSAGE_COLL);

This approach gives you a good control over the message as well as over the results.