Refresh a Classic Report based on a collection

Question

I am currently learning apex but got stuck. I am using Oracle apex 4.2. My app consists of 2 reports. In first I am choosing some checkboxes and clicking button which fires DA with some js code and uses apex.server.process with AJAX Callback like in this link.

Next the "On Demand" process launches PL/SQL procedure (I know there is no reason for that, but I did it for learning purpose) which processes checked values and concatenates them into one clob which is then being added to collection. So each time I am clicking the button, exactly 1 new clob is being added.

My second report just reads ALL clobs from collection, and I want to refresh it each time after I click the button without submitting all of the page content. I have added DA with a True Action: Refresh but it works with delay. When I first click it - nothing happens, on the second click the clob which was generated previously shows up in the report region and so on. I guess I need to submit collections value to session state but I have nothing to add to the reports "Page Items to Submit" option.

Answer 1

Not seeing "most recent" data

The only reason you would see "outdated" data is because you use an ajax call to update the collection. It's asynchronous! Similar, the refresh is async too. The actions don't wait for eachother to complete.

It can go kind of like this:

• collect the checkboxes and send them off to some process, which somewhere down the line will return me something
• sometimes, it depends, the ajax call is fast enough to have finished before the region refresh occurs
• refresh the region
• other times, the ajax call has taken a bit longer, and the refresh has already occured

To solve this, you'll have to add something to your apex.server.process call: the refresh of the collection-based report should only occur when the callback has completed. Firstly though: add a static report id to your collection-based report. Do this by editing it. Providing a static id allows for easier selection of the region through a selector.

apex.server.process('Process Checked Rows', {f01: arr_f01}).done(function(){
  //do something when the callback has finished
  //like triggering the refresh event on the report required
  apex.jQuery("#your_report_static_id_goes_here").trigger("apexrefresh");
});

Now the report will only refresh when you're sure the checkboxes have been processed into the collection.

---

Using a clob for storing IDs

As I've pointed out in the comments, it makes no sense at all to use a clob to keep the selected IDs together. There's just no argument to make for it beside perhaps pure experimentation. Honestly, time better spent elsewhere if you want to experiment with CLOBs.
Collections are meant to be a Global Temporary Table replacement within the context of Apex sessions. Use them like you would use a table. Simply insert the IDs of the selected rows into the collection.

FOR i IN 1..apex_application.g_f01.count
LOOP
  apex_collection.add_member
  ( p_collection_name => 'CHECKBOX_COLLECTION'
  , p_n001            => apex_application.g_f01(i) --NOTE: using N001 here to store a numerical ID
  );
END LOOP;

If you only want to have uniques then you can do that here by adding a select on the collection, or add a distinct in the query used to loop or select over the collection.

FOR i IN 1..apex_application.g_f01.count
LOOP
  DECLARE
    l_exists BINARY_INTEGER;
  BEGIN
    SELECT 1 
      INTO l_exists
      FROM apex_collections
     WHERE collection_name = 'CHECKBOX_COLLECTION'
       AND n001 = apex_application.g_f01(i);
  EXCEPTION WHEN no_data_found THEN
    apex_collection.add_member
    ( p_collection_name => 'CHECKBOX_COLLECTION'
    , p_n001            => apex_application.g_f01(i) --NOTE: using N001 here to store a numerical ID
    );
  END;
END LOOP;

You can then eeasily loop over the records in this collection or use it to limit rows in other queries (eg another report).

SELECT n001 selected_id
  FROM apex_collections
 WHERE collection_name = 'CHECKBOX_COLLECTION';