Oracle APEX Collection Example

In this tutorial, you will learn how to create a collection in Oracle APEX.

This Oracle APEX collection example consists of the following topics:

• Create a collection.
• Check if a collection already exists.
• Truncate a collection.
• Populate it with data using PL/SQL.
• Query a collection.
• Populate it with data using JavaScript.

Creating a Collection in Oracle APEX

Use apex_collection.create_collection method to create a collection. The following is an example:

apex_collection.create_collection('EMP_COLLECTION');

The above PL/SQL command will create a collection named EMP_COLLECTION.

Collections in Oracle APEX are the temporary storage for the current session, in which you can add the data, access the data, and can do other lots of things.

But before creating a collection, make sure it does not exists already. Check the below example:

Checking if a Collection Exists Already

Use the apex_collection.collection_exists method to check whether a collection exists already. The following is an example:

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

Now it will create the collection if not already exists.

But if a collection already exists and you want to make it empty so that you can add the new data, check the following example:

Truncating a Collection

Use the apex_collection.truncate_collection method to delete all the data from a particular collection. The following is an example:

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

The above PL/SQL code will check if the collection EMP_COLLECTION already exists, if not, then it will create a collection, and if it exists, then it will truncate the collection.

You have created a collection, now add data into it.

Populating a Collection

Use apex_collection.add_member to add a data row in it. Below is an example:

apex_collection.add_member(
   p_collection_name => 'EMP_COLLECTION',
   p_c001 => :P3_EMPNO,
   p_c002 => :P3_ENAME,
   p_c003 => :P3_ESAL
);

The above PL/SQL code will add a row to the collection EMP_COLLECTION, with three columns p_c001 as EMPNO, p_c002 as ENAME, and p_c003 as ESAL (Salary).

The following is the full syntax of apex_collection.add_member method, which you can use to add the data to a collection:

APEX_COLLECTION.ADD_MEMBER (
p_collection_name IN VARCHAR2,
p_c001 IN VARCHAR2 DEFAULT NULL,
...
p_c050 IN VARCHAR2 DEFAULT NULL,
p_n001 IN NUMBER DEFAULT NULL,
p_n002 IN NUMBER DEFAULT NULL,
p_n003 IN NUMBER DEFAULT NULL,
p_n004 IN NUMBER DEFAULT NULL,
p_n005 IN NUMBER DEFAULT NULL,
p_d001 IN DATE DEFAULT NULL,
p_d002 IN DATE DEFAULT NULL,
p_d003 IN DATE DEFAULT NULL,
p_d004 IN DATE DEFAULT NULL,
p_d005 IN DATE DEFAULT NULL,
p_clob001 IN CLOB DEFAULT EMPTY_CLOB(),
p_blob001 IN BLOB DEFAULT EMPTY_BLOB(),
p_xmltype001 IN XMLTYPE DEFAULT NULL,
p_generate_md5 IN VARCHAR2 DEFAULT 'NO');

Below is a complete example of creating a collection and populating it with data.

Complete Oracle APEX Collection Example

In the following example, we will populate the collection EMP_COLLECTION from the EMP table using the cursor:

declare
   Cursor c_emp
   is 
   select empno, ename, sal from emp;
begin

if not apex_collection.collection_exists('EMP_COLLECTION') then 
   apex_collection.create_collection('EMP_COLLECTION');
else
   apex_collection.truncate_collection('EMP_COLLECTION');
END IF;

for c in c_emp loop

   apex_collection.add_member(
    p_collection_name => 'EMP_COLLECTION',
    p_c001 => c.empno,
    p_c002 => c.ENAME,
    p_c003 => c.sal
   );

end loop;

end;

Now in the current session from any page, you can access the data from the collection EMP_COLLECTION using the SQL query.

Querying a Collection

select 
   c001 empno,
   c002 ename,
   c003 salary
from apex_collections
where collection_name = 'EMP_COLLECTION'

You can use this SQL query in your PL/SQL programs, in reports such as Interactive reports, interactive grid, etc.

Populating a Collection Using JavaScript

The collection will be populated using PL/SQL only, but in the following example, we will see how we can pass the values to a PL/SQL program using JavaScript to populate the collection.

Loop Through Interactive Grid using JavaScript and Populate the Collection

In the below example, we will loop through an interactive grid using JavaScript. The interactive grid is based on the EMP table. We will loop through and will pass the values to an Ajax callback PL/SQL process.

To do this, follow these steps:

Create an Interactive Grid on the EMP table.

Give a static ID to the interactive grid as igemp.

We will read three columns only from the interactive grid, which are EMPNO, ENAME, and SAL.

So create three hidden page items, for example, P2_EMPNO, P2_ENAME, P_ESAL.

Turn-off the Value Protected setting for these three hidden items.

Now on the same page, click on the process tab and create an Ajax Callback process and give a name as populate_collection and add the following code in it:

Declare
    n_empno Number;
Begin
    If Not apex_collection.collection_exists('EMP_GRID_DATA') Then
        apex_collection.create_collection('EMP_GRID_DATA');
    End If;

    apex_collection.add_member(
     p_collection_name => 'EMP_GRID_DATA', 
     p_c001 => :p2_empno, 
     p_c002 => :p2_ename, 
     p_c003 => :p2_esal

    );

End;

Create a button and create a dynamic action on it to execute the JavaScript code.

On this button click, we will loop through the interactive grid we created above and will pass the data to the collection EMP_GRID_DATA using the Ajax callback process populate_collection.

Add the following JavaScript code in the JavaScript code section of the dynamic action:

var model = apex.region("igemp").widget().interactiveGrid("getViews", "grid").model;
var n_sal, n_totsal = 0;
col_empno = model.getFieldKey("EMPNO");
col_ename = model.getFieldKey("ENAME");
col_salary = model.getFieldKey("SAL");
model.forEach(function (igrow) {

   apex.item("P2_EMPNO").setValue(igrow[col_empno]);
   apex.item("P2_ENAME").setValue(igrow[col_ename]);
   apex.item("P2_ESAL").setValue(igrow[col_salary]);

   apex.server.process('populate_collection', {
      pageItems: '#P2_EMPNO,#P2_ENAME,#P2_ESAL'
   }, {
      dataType: 'text',
      success: function (data) {
         if (data != 'SUCCESS');
      }
   });
});

The collection has been populated with interactive grid data.

You can query the collection EMP_GRID_DATA as follows:

select 
  c001 empno,
  c002 ename,
  c003 salary
  from apex_collections
where collection_name = 'EMP_GRID_DATA'

To test, you can create an interactive report based on above SQL query on the same page or any other page.