Oracle APEX Collections: Rediscover

Oracle APEX Collections are a feature that has stood the test of time. They are a simple yet powerful tool in Oracle APEX. They allow developers to manage and manipulate data within a session. Collections are an example of a developer centric feature of Oracle APEX (not really a Low Code feature). Despite the evolving landscape of Oracle APEX, Collections remain relevant. This post explores their history, evolution, and modern applications.

What are Oracle APEX Collections?

Oracle APEX Collections are in-memory data structures that exist within a session. Think of them as temporary tables that live only as long as the session does. You can create, manipulate, and query these collections using PL/SQL. They provide a way to store and manage data temporarily without needing to create actual database tables.

Here's an example of fetching all the planets from SWAPI (Star Wars API):

DECLARE
  l_url           VARCHAR2(1000) := 'https://swapi.dev/api/planets/';
  l_response      CLOB;
  l_planets       JSON_OBJECT_T;
  l_planet_array  JSON_ARRAY_T;
  l_planet        JSON_OBJECT_T;
  l_next_page     VARCHAR2(4000);
BEGIN

  -- Create or truncate the PLANETS collection
  APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION('PLANETS');

  -- Loop through all pages of results
  LOOP
    -- Make the API call
    l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
      p_url         => l_url,
      p_http_method => 'GET'
    );

    -- Parse the JSON response
    l_planets := JSON_OBJECT_T.PARSE(l_response);
    l_planet_array := l_planets.GET_ARRAY('results');

    -- Loop through the planets in the current page
    FOR i IN 0 .. l_planet_array.GET_SIZE - 1 LOOP
      l_planet := TREAT(l_planet_array.GET(i) AS JSON_OBJECT_T);
      
      -- Add planet to the collection
      APEX_COLLECTION.ADD_MEMBER(
        p_collection_name => 'PLANETS',
        p_c001            => l_planet.GET_STRING('url'),  -- Using URL as ID
        p_c002            => l_planet.GET_STRING('name')
      );
    END LOOP;

    -- Check if there's a next page
    l_next_page := l_planets.GET_STRING('next');
    EXIT WHEN l_next_page IS NULL;
    
    l_url := l_next_page;
  END LOOP;
END;        

Once the collection is populated you can simply query it like a table:

SELECT c001 AS planet_id, c002 AS planet_name
    FROM APEX_COLLECTIONS
    WHERE COLLECTION_NAME = 'PLANETS'
    ORDER BY c002;        

The Origins of APEX Collections

Oracle introduced APEX Collections to solve a specific problem: managing multi-row data in web applications without committing it to the database. They were first available in earlier versions of Oracle APEX (back when it was called HTML DB). The goal was to allow developers to collect and work with data across multiple pages or processes without relying on database tables for storage.

Key Features and Functionalities of APEX Collections

APEX Collections come with a set of built-in functionalities:

1. Creating Collections: You can create a collection using several different API methods (example: APEX_COLLECTION.CREATE_COLLECTION). Once created, you can add, modify, or delete data in the collection. Also, see the method used in the example above of APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION.
2. Manipulating Collections: APEX provides a variety of procedures to manipulate collections, such as ADD_MEMBER, UPDATE_MEMBER, and DELETE_MEMBER.
3. Querying Collections: You can query collections just like you would with a regular SQL table using the APEX_COLLECTIONS view (including using them in JOINs!).

These features made Collections an essential tool in early APEX applications, especially for handling multi-row data in forms, shopping carts, and complex reports.

Modern Use Cases for APEX Collections

Even with the advancements in Oracle APEX, Collections remain useful in many scenarios. Here are a few modern applications:

1. Temporary Data Storage: Use Collections to hold temporary data that doesn’t need to be stored permanently in the database. This is useful for complex calculations or multi-step forms where data is accumulated over several pages. One example might be flags calculated on session data to drive features of the app or security requirements.
2. Multi-Row Processing: Collections allow you to handle multiple rows of data in a session. This is ideal for scenarios like batch processing, where you need to collect and process data in chunks before committing it to the database.
3. Data Staging: Before committing data to the database, you can stage it in a Collection. This allows for validation and modification before finalizing the transaction.

Migration and Compatibility Considerations

APEX Collections have remained consistent across different versions of Oracle APEX. This makes migrating legacy applications that use Collections relatively straightforward. However, it’s essential to review your Collections during migration to ensure they align with the best practices of the newer APEX versions.

If you’re maintaining or upgrading an application that uses APEX Collections, ensure that the Collections are used efficiently. Avoid keeping large amounts of data in a Collection for too long, as this can affect session performance.

Case Studies and Real-World Examples

1. Multi-Page Form Handling: In one legacy APEX application, a multi-page form needed to collect data across three pages before submission. Instead of writing the data to the database after each page, a Collection was used. This allowed the user to review and edit the data before the final submission, alleviating potential locks and increasing performance.
2. Shopping Cart Implementation: A typical e-commerce application used APEX Collections to manage the shopping cart. Items added to the cart were stored in a Collection, allowing users to modify quantities or remove items before checkout.

Common Pitfalls and Challenges

While APEX Collections are powerful, they have limitations:

1. Session Dependency: Collections exist only within a session. If the session ends, the Collection data is lost. This makes them unsuitable for long-term data storage.
2. Performance: Large Collections can/will impact session performance. It’s crucial to manage the size and lifespan of Collections carefully.

When using Collections, ensure that they are the right tool for the job. If your data needs to persist beyond a session, consider using temporary tables or other database structures.

Future Outlook: The Role of APEX Collections in Emerging APEX Features

As Oracle APEX continues to evolve, the role of Collections may change. However, their ability to manage data within a session will likely remain valuable. Developers should keep an eye on new APEX features that might integrate with or enhance Collections.

For example, you might store user responses in a collection to use as part of a RAG process for enhanced LLM interaction!

Conclusion

Oracle APEX Collections have been a reliable tool for many years. They offer a flexible way to manage data within a session, making them useful in various scenarios. Whether you’re working with legacy applications or building new ones, APEX Collections are worth exploring.

References and Further Reading

1. Oracle APEX Collections Documentation
2. YouTube Video Library - APEX Collections
3. Pushing Oracle APEX Collections to the Limit (cloudnueva.com) (one of my favorite blogs)