Oracle APEX Collections: Efficiently Managing Temporary Data

Introduction

In Oracle APEX, there are situations where we need to manage temporary data before deciding whether to store it permanently. APEX Collections provides an efficient way to handle this by offering an in-memory storage mechanism that remains active throughout a user’s session.

Rather than immediately writing data to a database table, collections allow us to group and organize information dynamically, making it accessible for processing or modification as needed. You can think of them as flexible containers where rows of data can be temporarily held and referenced during an active session.

In this article, we’ll take a look at how APEX Collections work, their benefits, and the best scenarios to use them to improve both performance and data handling in your applications.

Let’s dive in!

Definition and Characteristics

As I said before, Collections in APEX are essentially in-memory data sets that can temporarily store and manipulate data within a session. This is particularly useful for scenarios where you must manage intermediate data before committing it to permanent tables.

Below are some key characteristics that make APEX Collections a valuable tool for developers:

Session-Specific

Each collection is specific to a single user session. This means that the data stored in a collection is not shared between users or sessions, ensuring data isolation and security. Once the session expires or the user logs out, the collection is automatically deleted, so the data does not persist across sessions. This also guarantees that sensitive or temporary data doesn’t leak between users, providing an additional layer of privacy and data protection.

Table-Like Structure

APEX Collections offer a table-like structure, allowing data to be organized in rows and columns. The collection consists of named columns (up to 63), and each column can store different types of data. This makes collections versatile and easy to integrate into applications that need a structured yet temporary storage solution.

• Columns: These can hold a wide variety of data types, including text, numbers, dates, and more.
• Rows: Each row represents a single data record, and you can have multiple rows in a collection.

This table-like design makes it easy to query and manipulate data in a way that is similar to interacting with regular database tables but without the overhead of permanent storage

Flexible Data Handling

You can insert, update, delete, and query data in collections using PL/SQL procedures and built-in APEX utility functions. This makes them ideal for staging data before committing it to permanent storage.

To carry out all these actions, we’ll use the APEX_COLLECTION API PL/SQL.

Column Structure

APEX Collections support up to 63 columns, divided as follows:

Main procedures and functions

The APEX_COLLECTION API offers various procedures and functions to manage collections efficiently. Below is a reference guide to the most commonly used operations:

Collection Management

• CREATE_OR_TRUNCATE_COLLECTION (p_collection_name IN VARCHAR2)
  Creates a collection if it does not exist or clears it if it already exists.

• CREATE_COLLECTION (p_collection_name IN VARCHAR2)
  Creates a new empty collection. Returns an error if the collection already exists.

• DELETE_COLLECTION (p_collection_name IN VARCHAR2)
  Completely removes a collection. All members that belong to the collection are removed, and the named collection is dropped.

Data Insertion & Modification

• ADD_MEMBER (p_collection_name IN VARCHAR2, p_c001 IN VARCHAR2, …)
  Adds a new record to a collection.

• UPDATE_MEMBER (p_collection_name IN VARCHAR2, p_seq IN NUMBER, p_c001 IN VARCHAR2, …)
  Updates an existing record in a collection using its sequence number.

• UPDATE_MEMBER_ATTRIBUTE (p_collection_name IN VARCHAR2, p_seq IN NUMBER, p_attr_number IN NUMBER, p_attr_value IN VARCHAR2)
  Updates a single attribute of a record without modifying the entire row.

• TRUNCATE_COLLECTION (p_collection_name IN VARCHAR2)
  Removes all records from a collection without deleting the collection itself.

Deleting Records

• DELETE_MEMBER (p_collection_name IN VARCHAR2, p_seq IN NUMBER)
  Deletes a specific record based on its sequence number.

• DELETE_MEMBERS (p_collection_name IN VARCHAR2, p_attr_number IN NUMBER, p_attr_value IN VARCHAR2)
  Deletes all members from a given named collection where the attribute specified by the attribute number equals the supplied value.

Practical use cases

APEX Collections are perfect for scenarios where data needs to be temporarily stored and processed before being saved permanently.

Some common use cases include:

Multi-step wizards

Use Case:
When working with a multi-step form (wizard), data should only be stored in the database once the user completes all steps.

Example:
A loan application wizard where the user enters information in multiple steps:

1. Personal details.
2. Financial information.
3. Document upload, etc.

First, we create the collection:

When the final step is completed, all the information is inserted into the permanent table:

Session-Specific Operations

Use Case:
Store temporary user-specific data, such as user preferences, search filters, or intermediate form data.

Example:
Saving a user’s shopping cart selection without inserting data into the database until the purchase is confirmed.

When the user completes the purchase:

Copy to Clipboard

Syntax HighlighterINSERT INTO ORDERS (PRODUCT_ID, QUANTITY) SELECT C001, C002 FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'SHOPPING_CART';

Bulk Data Processing

Use Case:
When processing large volumes of data, collections can be used as staging areas before inserting or updating records in bulk to improve performance.

Example:
A user uploads a CSV file with product data. Instead of inserting each row immediately, the data is first stored in a collection for validation before being inserted in bulk:

Report Generation

Use Case:
When data needs to be consolidated from multiple sources or complex calculations are required before displaying a report.

Example:
Generating a sales report from different tables and applying custom calculations before presenting the data.

Complete Example

Next, we will see a complete example of using APEX Collections in an integration with Oracle Integration Cloud (OIC). In this case, we will use an APEX Collection to temporarily store invoices retrieved from a REST service exposed in OIC and then allow the user to approve and send the selected invoices back to OIC for processing in the ERP system.

Step 1: Create a Web Credential

In APEX, create a Web Credential for authenticating with the OIC service.

1. Go to Shared Components > Web Credentials.
2. Create a new Web Credential:

• • Authentication Type: Use OAuth2 or Basic Authentication, depending on the authentication method configured in OIC.
  • Static ID: OIC_Integration_Credentials.

Step 2: Call the OIC Service and Store the Results in a Collection

We use the APEX_WEB_SERVICE.MAKE_REST_REQUEST procedure to call the OIC REST service, fetch invoice data, and store it in an APEX Collection:

Step 3: Display the Data in an Interactive Grid

In APEX, we create an Interactive Grid with the following SQL query to display the invoices stored in the collection:

We have added a new column (APPROVE) to allow users to select the invoices they wish to approve.

Step 4: Send Approved Invoices to OIC for Processing

We define a button (Approve Selected) on the page so that when the user clicks on it, the app runs this PL/SQL block to send the approved invoices back to OIC for further processing in the ERP system:

Conclusion

Oracle APEX Collections are a powerful tool for handling temporary data in APEX applications. By providing in-memory storage within a session, they enhance performance and flexibility in data management without requiring physical tables. They are an ideal solution for various APEX development scenarios!