Overview
Apex Collections (APEX_COLLECTION) is a package that comes with Oracle Apex and is one of the most important features that comes with Oracle APEX. Each member has a sequence number and each collection must have a unique name.
Technologies and Tools Used
The following technology has been used to achieve this in oracle apex.
- Oracle Apex.
Use Case
We can use the Apex Collection in our PL/SQL programs, in reports such as Interactive reports, interactive grid, etc. 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.
Steps
Steps to be followed
Step1: Create a collection.
Step1: Check if a collection already exists.
Step1: Truncate a collection.
Step1: Populate it with data using PL/SQL.
Step1: Query a collection.
Create a collection
Use apex_collection.create_collection method to create a 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.
Example:
apex_collection.create_collection(‘EMPLOYEE_COLLECTION’);
The above example is used to create a collection.
Check if a collection already exists
Before creating a collection, make sure it does not exists already.
Use the apex_collection.collection_exists method to check whether a collection exists already.
Refer the below example:
Example:
if not apex_collection.collection_exists(‘EMPLOYEE_COLLECTION’) then
apex_collection.create_collection(‘EMPLOYEE_COLLECTION’);
end if;
The above PL/SQL command will create a collection named EMPLOYEE_COLLECTION.
Truncate a collection
If a collection already exists and you want to make it empty so that you can add the new data.
Use the apex_collection.truncate_collection method to delete all the data from a particular collection.
Example:
if not apex_collection.collection_exists(‘EMPLOYEE_COLLECTION’) then
apex_collection.create_collection(‘EMPLOYEE_COLLECTION’);
else
apex_collection.truncate_collection(‘EMPLOYEE_COLLECTION’);
end if;
The above PL/SQL code will check if the collection EMPLOYEE_COLLECTION already exists, if not, then it will create a collection, and if it exists, then it will truncate the collection.
Populate it with data using PL/SQL
We have created a collection. Now we are going to add data into it. Use apex_collection.add_member to add a data row in it.
Example:
apex_collection.add_member(
p_collection_name => ‘EMPLOYEE_COLLECTION’,
p_c001 => :P3_EMP_NO,
p_c002 => :P3_EMP_NAME,
p_c003 => :P3_EMP_SAL
);
The above PL/SQL code will add a row to the collection EMPLOYEE_COLLECTION, with three columns p_c001 as EMP_NO, p_c002 as EMP_NAME, and p_c003 as EMP_SAL.
We will populate the collection EMPLOYEE_COLLECTION from the EMP table using the cursor in the below Example.
Example:
DECLARE
CURSOR c_emp
IS
SELECT emp_no, emp_name, emp_sal
FROM emp;
BEGIN
IF NOT apex_collection.collection_exists (‘EMPLOYEE_COLLECTION’)
THEN
apex_collection.create_collection (‘EMPLOYEE_COLLECTION’);
ELSE
apex_collection.truncate_collection (‘EMPLOYEE_COLLECTION’);
END IF;
FOR c IN c_emp
LOOP
apex_collection.add_member (p_collection_name => ‘EMPLOYEE_COLLECTION’,
p_c001 => c.emp_no,
p_c002 => c.emp_name,
p_c003 => c.emp_sal
);
END LOOP; END;
I have added the above code in a process in oracle apex.
In the execution option we should select “Before Regions” point as shown in the below picture.
Query a collection.
SELECT c001 empno, c002 ename, c003 salary
FROM apex_collections
WHERE collection_name = ‘EMPLOYEE_COLLECTION’
We can use this SQL query in our PL/SQL programs, in reports such as Interactive reports, interactive grid, etc.
Now I have created a region and mention type is ‘Interactive Report’. Add the above query in the source. Please refer the below screenshot.
If you run the page you will get the output as shown in the below.
