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.

Recent Posts

Start typing and press Enter to search