APEX_COLLECTION: APEX's Super Power!

Apex Collections (APEX_COLLECTION) is a package that comes with Oracle Apex and is one of the most important features that comes with Oracle APEX. It can be used in many ways for different purposes, but in general, you can think of Apex Collection as a temporary space to store complex data Types — non-scalar — for instance, full row of data from a table or a view (When you work with Apex Temporary table is not a real option). This data can be manipulated in any way and then stored in Table(s). Each Apex Collection can can has 50 varchar2 attributes (c001 -> c0050), 5 number Attributes (n001->n005), 5 date attributes (d001->d005), 1 CLOB attribute (clob001), and 1 BLOB Attribute (blob001). Each member has a sequence number and each collection must have a unique name.

In this tutorial, I will build a collection from the data stored in a table, add more rows from the table to the existing collection, and then I will show you how to add new member to the collection, how to edit a Member, how to delete a member, and how to save the collection back to the table. I will use some simple Dynamic SQL, and we will use dynamic actions too (many of them actually!).

Setup

The setup and the page used in this tutorial are the same as in my previous article (Super Dynamic APEX Forms Using APEX_ITEM and Dynamic Actions), and this article can be considered the second part of that one. The table, as you can see, is very simple (child_id, name, age, gender, school), and the page has four regions; we will use two of them in this article, 'Table Now' and 'Collection Now.'

Let's Do It!

The first region we will need is the 'Table Now' region. It's a very simple interactive report region based on our only table 'TEST_CHILDREN' with only two buttons 'Append Collection' and 'Build & Reset Collection.' There is also a text item for entering the condition dynamically, as we will see.

The second region is 'Collection Now.' It's a classic report region based on a 'special query' on the collection that we will talk about soon. There are also three buttons: 'Add To Collection,' 'Empty Collection,' and 'Save Collection,' and there is an edit icon for each row. This icon will open the edit form that we will use to edit or delete collection members. Dynamic action will be the engine behind the scenes here.

Moving Data From the Table to the Collection

First, I will create a dynamic action that will handle inserting the data into the collection from the table and also delete the data from the table. I will give the user the option to enter a simple condition or predicate to select the rows that will be inserted into the collection when the button 'Build & Reset Collection' is pressed.

The dynamic action that is connected to the button will have a main action that will hold our code and then three refresh actions that I will use to refresh the regions after running the code.

Inside the PL/SQL code area in our PL/SQL action, I will add the following code:

DECLARE

    l_query varchar2(200) := 'SELECT child_name, GENDER, school, AGE from test_children';
    cond_var varchar2(200) := ' 1 = 1 ';
begin
    IF APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => 'CHILDREN') THEN
            APEX_COLLECTION.DELETE_COLLECTION (p_collection_name => 'CHILDREN');

    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
        p_collection_name => 'CHILDREN', 
        p_query => l_query||' where '||cond_var,
        p_generate_md5 => 'YES');

     execute immediate 'DELETE TEST_CHILDREN where '||cond_var;
End;

Let's break it down:

• l_query is a variable where I put my main query
• con_var is a variable that I use to store my default condition '1 = 1' which, of course, means select *
• Then I use the function APEX_COLLECTION.COLLECTION_EXISTS. This function checks to see if the collection exists, and it returns TRUE or FALSE. Then, it exists and I delete it using DELETE_COLLECTION, which only asks for the name of the collection that needs to be deleted.
  • Now I create my collection using this handy procedure CREATE_COLLECTION_FROM_QUERY, which is a procedure that will create a new collection from the query I pass, and each returned column will be stored in the varchar2 attributes and in order: the first returned column in c001, the second column in c002, and so on. Be careful because a number column will be stored as varchar2. The query has two parts; my default query merged with the condition, and I can also choose to generate md5 hash.
• Finally, I use native dynamic SQL to delete the same rows that I insert into my collection to avoid duplication when we return the rows (or some of them) to the table, as we will see later.

Before testing the button, you should have something like this where the collection is still hidden. As I put a server side condition in my collection's region, it shows the region only when there is at least one row in the collection.

Now I can test the button using "CHILD_ID >= 707 as a predicate...

Notice the number of rows under the table before and after I use the button as well as the number of rows of the collection that appeared after the refresh...

The 'Collection Now' region is built using this query:

select seq_id editlink,
    seq_id ID,
    c001 Name, 
    c002 Gender, 
    c003 School, 
    c004 AGE
from apex_collections
where collection_name = 'CHILDREN';

Append More Data to the Existing Collection

Now let's demonstrate how to add or append more rows from the table to the collection. As we did with the button 'Build & Reset Collection', I will add a dynamic action under the button 'Append Collection'

Now I will enter the following code in the PL/SQL region in my dynamic action:

DECLARE

    l_query varchar2(200) := 'SELECT child_name, GENDER, school, AGE from test_children';
    cond_var varchar2(200) := ' 1 = 1 ';
begin
    IF APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => 'TEMP_COLLECTION') THEN
            APEX_COLLECTION.DELETE_COLLECTION (p_collection_name => 'TEMP_COLLECTION');
    END IF;

    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
        p_collection_name => 'TEMP_COLLECTION', 
        p_query => l_query||' where '||cond_var,
        p_generate_md5 => 'YES');

     execute immediate 'DELETE TEST_CHILDREN where '||cond_var;

    IF not APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => 'CHILDREN') THEN
        APEX_COLLECTION.CREATE_COLLECTION(p_collection_name => 'CHILDREN');
     END IF;

    FOR new_item in (select c001, c002, c003, c004 from apex_collections 
                     where collection_name = 'TEMP_COLLECTION')  loop

         apex_collection.add_member(
             p_collection_name => 'CHILDREN',
             p_c001 => new_item.c001,
             p_c002 => new_item.c002,
             p_c003 => new_item.c003,
             p_c004 => new_item.c004
         );
     end loop;
     apex_collection.delete_collection(p_collection_name => 'TEMP_COLLECTION');
End;

Code Breakdown

• The variables at the start are the same as before, one to hold the base query and one to hold the default condition.
• Again, there are two familiar steps, checking the existence of a collection using COLLECTION_EXISTS and then creating a collection using CREATE_COLLECTION_FROM_QUERY. But here, I'm creating a temporary collection to hold the new rows. The question, of course, is why the temporary collection? Here, we need to get some rows from the table into the collection, but in this case, the collection already exists, so we can't use CREATE_COLLECTION_FROM_QUERY because this will create a new collection, and it will raise an error if the collection already exists. So, a good solution would be to create a temporary collection based on my query and then copy the members or the rows to the main collection.
• After inserting the rows into the TEMP_COLLECTION, I remove the same rows from the table...
• ...checking if my main collection exists, and if it does not exist, I create it.
• Here, how we can copy the members from the temporary collection to the main collection, I'm using an implicit cursor FOR loop using a query on the temp collection and inside the loop I introducing a new procedure add_member, this procedure will add a new member to the collection and it accepts the name of the collection as the first parameter then the values for the attributes, as you can see 'p_coo1' is going to hold the value for the attribute c001 and so on.
• Finally, do not forget to delete the temporary collection!

When I press the button, the selected rows will be moved from the table and get added to the collection.

The Collection Form Page

In order to be able to add, edit, or delete a member in the collection, we need a simple form to enter or edit the data, so I will create a blank page and the needed items to it. Then, I will write the code needed to edit, add, or delete a collection member in processes linked to the three buttons: 'Add member,' 'Update member,' and 'Delete member.' Of course, each button will have a server side condition that controls when it will appear on the page. For example, the 'Edit member' button will only appear if the hidden item 'P11_CHILD_ID' is not null, indicating that there is a member to edit.

I will not talk in detail about the design of this page nor the dynamic actions, as it's beyond the scope of this article, and I mentioned much of the details before in my previous article as well. Instead, I will focus on the PL/SQL needed to use the collections.

Add New Member to the Collection

To add a new member to the collection I will create a process that is linked to the button and I will use the follwoing code:

APEX_COLLECTION.ADD_MEMBER (p_collection_name => 'CHILDREN',
                            p_c001 => :P11_CHILD_NAME,
                            p_c002 => :P11_GENDER,
                            p_c003 => :P11_SCHOOL,
                            p_c004 => :P11_AGE
                            );

Breakdown

Here, I am using a very important procedure, ADD_MEMBER. It adds a member to an existing collection and, as usual, it requires the collection name as the first parameter and then you can pass the data to the attributes you want, so p_c002 is going to store the value in c002 and p_d001 will store the value in the date attribute d001 and so on.

Loading a Member's Data Into the Form Page

To be able to edit an existing collection's member the data must be loaded from the collection into the form page through a process that runs after the header or, in other words, a process that runs automatically when the is page is loaded, I'm using the following query:

select c001, -- Name, 
        c002, -- Gender, 
        c003, -- School, 
        c004 --AGE
    into :P11_CHILD_NAME, :P11_GENDER, :P11_SCHOOL, :P11_AGE from apex_collections
    where collection_name = 'CHILDREN' AND seq_id = to_number(:P11_CHILD_ID);

As you can see, this simple query is written in PL/SQL style, and I am inserting the attributes of the selected member into the item or the field of the form. Of course, there is a server-side condition here that makes the process run only if the hidden item "P11_CHILD_ID" is not null, and this is the item where I pass the ID of the member.

Now I can edit the selected member, as you can see below:

To edit a member I need to pass the ID to the form page and store it in the hidden item "P11_CHILD_ID" , now the form can be used to edit the attributes and when I'm done the code below is what I need to update the selected member

APEX_COLLECTION.UPDATE_MEMBER (
        p_collection_name => 'CHILDREN',
        p_seq => :P11_CHILD_ID,
        p_c001 => :P11_CHILD_NAME,
        p_c002 => :P11_GENDER,
        P_c003 => :P11_SCHOOL,
        P_c004 => :P11_AGE

);

To update a member I can use the procedure UPDATE_MEMBER it's really straight forward, the collection name is a must and it's the first parameter 'p_collection_name', next the 'p_seq' which is the sequence number of the member then I pass the new updated values. Here I am inserting the values stored in the page items.

Delete a Member of the Collection

Deleting is very easy (as usual!) all you need is this small piece of code:

APEX_COLLECTION.DELETE_MEMBER (
    p_collection_name => 'CHILDREN',
    p_seq =>  :P11_CHILD_ID);

The breakdown of this code very simple, the procedure DELETE_MEMBER is what you need to delete a member. First, pass the collection name and then the sequence number of the member you want do delete.

Saving the Collection Into the Table

Now we finished playing with our data inside the collection, and it's time to save it to the table. Usually, this is what you will do with the data stored in a collection: load the data into a collection, manipulate the data, then store it in a table(s).

To save our collection, I will use an implicit cursor FOR loop, and inside the loop, I insert the data member-by-member or row-by-row.

for item in (select c001, c002, c003, c004 from apex_collections 
             where collection_name = 'CHILDREN')  loop

         insert into test_children (CHILD_NAME, GENDER, SCHOOL, AGE)
             values(item.c001, item.c002, item.c003, item.c004);
end loop;

APEX_COLLECTION.DELETE_COLLECTION (
    p_collection_name => 'CHILDREN');

After the insert is done I delete the collection to free up the memory.

Empty the Collection

Finally, if you wish to completely empty the collection and remove all the members all you need to do is this:

APEX_COLLECTION.DELETE_COLLECTION (
    p_collection_name => 'CHILDREN');

Just use the procedure DELETE_COLLECTION and pass the collection name using the parameter 'p_collection_name.' Of course, this is permanent, so the ID you run for this code, all of the data stored in the collection will be gone!

Conclusion

Collections can be very handy when you need to store data in the session, and when you work with APEX, you will face many situations where such need will be part of your application design. Mastering collections alongside other main features of APEX and PL/SQL will give your application an edge.

I would love to hear any comments or questions regarding this article.