APEX 5.0 Example use of collections

This is my first attempt in using collections within Oracle APEX.
I just save this code for later use.

I used APEX 5.0 on the Oracle cloud.

I created 2 pages:

• Overview Page
• Edit Page

In the Overview page I load through a select-statement the data into a collection during the Pre-Rendering > Before Header > Processes > PL/SQL action, with the following code:

Declare
l_query varchar2(300) := 'select department_id, department_name, manager_id from departments';
l_coll_name varchar2(50) := 'DEPARTMENTS';
Begin
 APEX_DEBUG.ENABLE();
 APEX_DEBUG.INFO('Start fetchData');
    if not APEX_COLLECTION.COLLECTION_EXISTS(l_coll_name)
    then
    APEX_DEBUG.INFO('Collection ' ||l_coll_name||' does not exist, creating a new one');
      -- Create the collection from the query 
      APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B (
        p_collection_name => l_coll_name, 
        p_query => l_query);
    end if;
 APEX_DEBUG.INFO('End fetchData');
End;

I'm creating the collection only the first time that I'm entering the page.  So when changes are made on the second page, these changes are immediately shown on this page on returning.

To show the data in a report, you can use a classic report with a query which looks like this one:

select 
    c001,
    c002,
    c003,
    c004,
    c005
  from apex_collections
  where collection_name = 'DEPARTMENTS'
;

In this simple example, I just copy the values from the collections to the editable fields on the Edit Page through the link-functionality.
When showing the Edit page, I add a save button, which on submitting the page saves the changes in the collection and the db if needed.  These operations are performed in the Processing > Processes > PL/SQL code with the following code:

declare
  cursor c_get_seq
  is
  SELECT seq_id
   FROM APEX_collections
  WHERE collection_name = 'DEPARTMENTS'
    AND c001 = :P6_DEPTID
  ;
  r_seq c_get_seq%rowtype;
begin
   open c_get_seq;
   fetch c_get_seq into r_seq;
   if c_get_seq%found
   then
   APEX_DEBUG.INFO('Collection found, updating);
      APEX_COLLECTION.UPDATE_MEMBER (
        p_collection_name => 'DEPARTMENTS',
        p_seq => r_seq.seq_id,
        p_c001 => :P6_DEPTID,
        p_c002 => :P6_DEPTNAME,
        p_c003 => :P6_MANAGER);
        commit;
     -- update departments set department_name = :P6_DEPTNAME, manager_id = :P6_MANAGER where department_id = :P6_DEPTID;
   else
     APEX_DEBUG.ERROR('Collection not found!!');
   end if;
   close c_get_seq;
end;

After clicking on the save-button, the collection gets updated and the user returned to the previous page.