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.
Reference: Oracle Apex Collection API Reference