How to Create a Collection in Oracle Apex?

You can use apex_collection.create_collection procedure to create a collection in Oracle Apex. In this tutorial, you will learn how to create collections and add data to them in Oracle Apex.

What is Collection in Oracle Apex

Before we learn how to create a collection in Oracle Apex, let's understand what it is. In simple terms, a collection is like a virtual container in your Apex application where you can put data temporarily. It's a bit like having a basket to hold things while you're shopping.

You can store different types of information, like numbers or words, in this basket, and you can use it to keep track of things while you're working on your application. Collections are handy when you need to save and use data without using a formal database table. They help make your Apex apps more dynamic and flexible.

Create a Collection in Oracle Apex Step-by-Step

Step-1: Create a Collection

In Oracle Apex, to create a collection use apex_collection.create_collection procedure, below is an example:

apex_collection.create_collection('EMP_COLLECTION');

But it is safe that before you create a collection in Oracle Apex, check before that if it already exists, and if exists, then truncate it (make it empty), and if not exist, then create it. To do this, see the following example:

if apex_collection.collection_exists('EMP_COLLECTION') then 
    apex_collection.truncate_collection('EMP_COLLECTION');
else
    apex_collection.create_collection('EMP_COLLECTION');
end if;

Now your question could be, where will you add the above PL/SQL code in Oracle Apex? And that is correct, suppose you are creating a collection to process and manipulate in only one page, create in the Before Header process. I prefer to do this. Below is a screenshot:

The collection has been created, now you use this collection on your page. Suppose you want to add the records, you can use any dynamic action to execute server-side code and use apex_collection.add_member procedure to add records to it. You can even add records in the header process code as well. I am just saying that there are multiple options for you to manage your collection.

Step-2: Add records to the Collection

Here is an example of apex_collection.add_member procedure to add the record in Oracle Apex. Suppose you have three fields on a page, EMPNO, EMPNAME, and SAL. And on a button click you want to add the data entered by the user to the collection. To do this, create a button and then create a dynamic action on it set the action as Execute Server-side code, and add the below PL/SQL code to it:

apex_collection.add_member(
   p_collection_name => 'EMP_COLLECTION',
   p_c001 => :P9_EMPNO,
   p_c002 => :P9_EMPNAME,
   p_c003 => :P9_SAL
);

Also, add these three items to the Page Submit property. Below is the screenshot:

Now you may be required to show the added record in a report, follow this step:

Step-3: Create a Report on the Collection

Create an Interactive Report and add the below SQL query to it:

Select c001 empno,
       c002 empname,
       c003 sal
    from apex_collectionS
    where collection_name = 'EMP_COLLECTION';

You can also add a Refresh Action to the dynamic action we created above to refresh the report region. Now whenever the user clicks on the button it will add the values to the collection and will refresh the report to display it. Below is the output:

I hope, using the above steps you will be able to create a collection in Oracle Apex.