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
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
Please check the following post, which is about getting the selected rows from interactive grid. And in the comment section of this post you will find the answer to your question:
https://vinish.ai/2019/10/oracle-apex-interactive-grid-get-selected-rows-example.html
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;
For the above code, I am checking if collection not exists then create, because maybe user does not want to empty the collection every time.
But yes, for the below code, we should use the apex.collection.create_or_truncate_collection method:
if not apex_collection.collection_exists('EMP_COLLECTION') then apex_collection.create_collection('EMP_COLLECTION'); else apex_collection.truncate_collection('EMP_COLLECTION'); end if;Because in the above code, I am creating collection if not exists, and truncating it if already exists, which is that one command is doing.
Thank you for the post! Very useful!
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.
Make the grid based on the collection using SQL query, as I shared in the above post.
Then populate the collection using Cursor and refresh the grid using dynamic action.
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
For your requirement, you can use global temporary table for grid instead of collection.
Or you can use a normal table for temporary purpose, I mean to populate the grid and on save transfer the data to the actual table from this temporary table.
And of-course, you will add the apex_username column in this table so that you can populate and query the temporary table based on the current user. Also, you will truncate/delete the table data before populating it with new data.
This is a good approach for this kind of requirement.
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.
You can create a dynamic action on button to execute PL/SQL code, and your code will something like below:
Insert into your_actual_table select * from your_temp_table where apex_user = :app_user;You can also use the cursor instead of direct insert statement.
It will insert the data without submit the page. No need of JavaScript here.
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
I got it, then instead of DA create a process in process tab after the header and interactive grid process.
Now when the user will save it, first the header and temporary grid table data will save and then your new process will execute and will transfer data from temp table to actual table. Thats all.
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
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?
Hi, I have the same experience.
IG over a apex_collection of ten records. After pressing a button, 8 records is transferred from time to time, 10 records from time to time. Irregularly
Is there a solution to this problem?
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 🙂
How to add date in Collection?? When i try to add date as a single value it works properly with collection but its not working when i try to add with other string values .. Any suggession ??
You can use a varchar2 type to insert date and convert them using to to_date() function.
Hi Vinish,
How to check whether the collection is empty or not
Try this:
or
Begin
IF not apex_collection.collection_exists('Criteria Result1')
THEN
apex_collection.Create_collection('Criteria Result1');
END IF;
APEX_COLLECTION.TRUNCATE_COLLECTION(p_collection_name => 'Criteria Result1');
FOR I IN 1..APEX_APPLICATION.G_F01.COUNT
LOOP
APEX_COLLECTION.ADD_MEMBER(p_collection_name => 'Criteria Result1',
p_c001 => APEX_APPLICATION.G_F01(I)
-- p_c001 => APEX_APPLICATION.CRITERIA_VALUES(I)
-- p_c002 =>I.CRITERIA_ID
);
-- xxalg_cc_item_assign_pkg.insert_temp( p_criteriaid, p_criteriatype,p_organizationid)
END LOOP;
End;
xxalg package in need to pass parameters from intractive report table inside the loop i need pass value dynamic value and Criteria Result1 is the apex collection from report table ,so please can any body help me in this
after doing this logic I noticed that it's not populating the collection with all the rows only the rows that are viewable. If I scroll to the end and then execute it does all rows how do i fix that?
Hello
I need to use APEX Collection to preserve the checkbox state while paginating the Interactive report. At the same time use a "Select all" checkbox to select all records on the report page. Since I have no idea about JavaScript I need to highlight the selected records and preserve the highlighting while paginating the report. Absolutely, I will use later the selected records to process the database.
Thank you,