Press "Enter" to skip to content

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.

Oracle Apex report based on a collection.

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.

Oracle Apex interactive grid.

Give a static ID to the interactive grid as igemp.

Assign static id to interactive grid.

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;

Oracle Apex ajax callback process.

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.

Reference: Oracle Apex Collection API Reference

Have you found the answer to your question? If not, you can discuss it with me in the comments section below or join my Q&A community OrclQA.com for developers and ask your question. It is FREE.

Vinish Kapoor

Follow

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

guest
16 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
fmagna
fmagna
3 months ago

hello and congratulations for the post, with the population with Javascript could I add only the data of a selected row with a chekbox? if it is possible how can I do?
Thanks again

Goran Zebec
Goran Zebec
3 months ago

Hi,

instead of this part
if not apex_collection.collection_exists(‘EMP_COLLECTION’) then
apex_collection.create_collection(‘EMP_COLLECTION’);
end if;

you could and maybe should use
Begin
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
p_collection_name => ‘EMPLOYEES’);
End;

Doris Serruto
Doris Serruto
3 months ago

Thank you for the post! Very useful!

FAHAD AKHTAR
FAHAD AKHTAR
3 months ago

Thanks for the post, I have a requirement to populate the data into collection using cursor and then insert record in interactive grid using java script. How can I achieve this.

FAHAD AKHTER
FAHAD AKHTER
Reply to  Vinish Kapoor
3 months ago

Hi Vinish,

I have a master detail page,in detail region user can manually add record in grid or user can press POPULATE button for auto fill grid,as per your suggestion if I create grid based on collection then user is unable to manually add record in grid.

Regards

FAHAD AKHTER
FAHAD AKHTER
Reply to  Vinish Kapoor
3 months ago

Hi Vinish

After inserting the data in global temporary table how to get value from temporary table and insert it using java script without submitting the page.

FAHAD AKHTER
FAHAD AKHTER
Reply to  Vinish Kapoor
3 months ago

Hi Vinish

I dint want to insert in interactive grid table because header record still not saved so foreign key create problem. Due to this I want to use java script to append the data in interactive grid from temporary table.

Once user press create button then header and detail record saved in database.

Regards

FAHAD AKHTER
FAHAD AKHTER
Reply to  Vinish Kapoor
3 months ago

Hi Vinish

Thanks for prompt response.

But I need to show the data to user in interactive grid because they can edit it before save.

I want to follow your thread regarding add manual row in interactive grid using java script and the source for data is temporary table or collection.

Regards

Tor
Tor
1 month ago

Hi Vinish! I have a requirement to select all records in a grid when the user clicks a button and then perform a server side action on those records. So I tried your solution and I have not been able to get it to yield consistent results. Sometimes all expected rows are returned into the collection but other times only some are. It seems to work fine if you only have 4-5 records in the gird but seems to start having problems past that. Seems like I am hitting some kind of time out or something. Have you had success with this solution even with hundreds of records in a grid?

Ahmed Haroon
Ahmed Haroon
1 month ago

hi Vinish Kapoor, thanks a lot for this guide to help me understanding Apex Collections what and how it works. your illustrations are very helpful 🙂