Oracle Apex Collection Example | Vinish Kapoor’s Blog

In this tutorial you will learn how to create a collection in Oracle Apex.

This example Oracle Apex Collection covers the following topics:

• Pick up a collection.
• Check if the collection already exists.
• Shorten the collection.
• Fill it with data using PL/SQL.
• Ask for the pickup.
• Fill it with data using JavaScript.

Building a collection in Oracle Apex

Use the apex_collection.create_collection method to create a collection. Below is an example:

apex_collection.create_collection(‘EMP_COLLECTION’) ;

The PL/SQL command above creates a set called EMP_COLLECTION.

Oracle Apex Collections is a temporary repository for the current session where you can add data, access data and do more.

But before you make a collection, you have to make sure it doesn’t exist anymore. Look at the following example:

Checking the availability of the collection already.

Use the apex_collection.collection_exist method to check if the collection already exists. Below is an example:

As apex_collection.collection_exist (‘EMP_COLLECTION’),
apex_collection.create_collection(‘EMP_COLLECTION’);
ends as ;

He’ll make a collection now if it doesn’t exist yet.

However, if the collection already exists and you want to empty it so that you can add new data, see the following example:

Completion of the collection

Use the apex_collection.truncate_collection method to remove all data from a specific collection. Below is an example:

otherwise apex_collection.collection_exists(‘EMP_COLLECTION’);
apex_collection.create_collection(‘EMP_COLLECTION’);
otherwise
apex_collection.truncate_collection(‘EMP_COLLECTION’);
end if ;

The PL/SQL code above checks whether the EMP_COLLECTION collection already exists. Otherwise, the collection will be created, and if so, the collection will be truncated.

You made the collection, now add data.

Population of the collection

Use apex_collection.add_member to add a data string. 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 PL/SQL code above adds a line to the EMP_COLLECTION set with three columns p_c001 as EMPNO, p_c002 as ENAME and p_c003 as ESAL (Salary).

Below is the full syntax of the apex_collection.add_member method, which can be used to add data to the 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 DEFAULT NULL,
p_n002 in DEFAULT NULL,
p_n003 in DEFAULT NULL,
p_n004 in ZERO NUMBER,
p_n005 in ZERO NUMBER,
p_d001 in NERO NUMBER,
p_d002 in NERO DEFAULT NUMBER,
p_d003 in DATA NULL,
p_d004 in DATA NULL,
p_d005 in DATA NULL,
p_clob001 in 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 how you can create a collection and fill it with data.

Completion of the Oracle Vertex Collection Example

In the following example, we fill the table EMP_COLLECTION with a cursor :

declare
c_emp
cursor is
select empno, ename, sal of emp ; start

otherwise apex_collection.collection_exists(‘EMP_COLLECTION’), then
apex_collection.create_collection(‘EMP_COLLECTION’);
otherwise
apex_collection.truncate_collection(‘EMP_COLLECTION’);
END IF ;

for c in loop c_emp

apex_collection.add_member(
p_collection_name => EMP_COLLECTION,
p_c001 => c.empno,
p_c002 => c.ENAME,
p_c003 => c.sal
) ;

the last loop;

the end;

In the current session you can now access the data of the EMP_COLLECTION collection from any page using the SQL query.

Recovery request

Select
c001 empno,
c002 ename,
c003 salary
in 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.

Enrich your collection with JavaScript

The collection is only filled with PL/SQL, but in the following example we see how we can pass values to the PL/SQL program using JavaScript to fill the collection.

Hinge on the interactive web using JavaScript and filling thecollection

In the following example, we create a loop through an interactive grid using JavaScript. The interactive grid is based on an EMP table. We go through the cycle and pass the values to the Ajax PL/SQL recall process.

Complete the following steps:

Create an interactive grid on the EMP table.

Enter the identifier of the static interactive grid as igemp.

We only read three columns of the interactive grid: EMPNO, ENAME and SAL.

So create three hidden page elements, for example P2_EMPNO, P2_ENAME, P_ESAL.

Disable the Protected value option for these three hidden objects.

Now click on the Process tab on the same page and create the Ajax callback process, give it a name like populate_collection and add the following code

Explain the Sien_empno number;Do not start with apex_collection.collection_exists(‘EMP_GRID_DATA’);Exit as ;

apex_collection.add_member(
p_collection_name => ‘EMP_GRID_DATA’,
p_c001 => :p2_empno,
p_c002 => :p2_name,
p_c003 => :p2_esal

);

The end;

Create a button and create a dynamic action to execute JavaScript code.

When you click this button, we will go through the interactive grid we created above and transfer the data to the EMP_GRID_DATA collection using the Ajax populate_collection callback process.

Add the following JavaScript code to the Dynamic JavaScript section

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 was filled with interactive mesh data.

The EMP_GRID_DATA set can be queried as follows :

Select
c001 empno,
c002 ename,
c003 salary
in apex_collections
where collection_name = ‘EMP_GRID_DATA’.

For the test you can create an interactive report based on the SQL query above on the same page or on another page.

Link: Link to the Oracle Apex Collection API

Related Tags:

apex 20.1 installation,apxrtins sql,how to install apex on windows 10,oracle apex: highlight row based on condition,oracle apex report on collection,oracle apex update collection,oracle apex 20.1 tutorial,oracle apex 19,oracle apex installation step by step in linux,oracle apex 20.1 download,oracle apex 19.1 installation,install oracle apex on windows