Accessing Oracle APEX Collections Externally

During the last Oracle APEX Cloud Tour, I attended a fantastic session by my friend Andre Souza , a true master in using APEX collections.

This sparked several ideas! I needed to optimize report performance in JasperReports and possibly access collection data through a REST API.

To achieve this, I developed a PL/SQL package called pkg_apex_collection to efficiently retrieve collection data externally. However, I soon realized there was room for improvement.

Let’s explore the solution! ??

Why Access APEX Collections Externally?

APEX Collections are a powerful session-based, temporary storage mechanism that allows developers to handle complex data structures within an APEX session. However, accessing them externally is not straightforward.

Common use cases include:

? Generating Reports: Extract collection data for tools like JasperReports.

? Integration with External Systems: Expose collection data via REST APIs.

? Performance Optimization: Use collections for faster data retrieval.

PL/SQL Package for External Access

This package provides a streamlined way to access and retrieve data from Oracle APEX collections externally. It allows for the following:

Key Features:

• The package dynamically fetches data from the collection, allowing flexible access to all available columns without needing to hardcode specific values.
• It accepts explicit parameters, such as application ID, session ID, and collection name, providing greater control over the data retrieval process.
• The package retrieves various types of data stored in APEX collections, including strings, numbers, dates, CLOBs, BLOBs, and XML data, making it versatile for different use cases.

-- Package Specification for pkg_apex_collection
CREATE OR REPLACE PACKAGE pkg_apex_collection AS
    -- Record type representing a row from an APEX collection.
    TYPE tac IS RECORD (
        seq_id         NUMBER,
        c001           VARCHAR2(4000),
        c002           VARCHAR2(4000),
        c003           VARCHAR2(4000),
        c004           VARCHAR2(4000),
        c005           VARCHAR2(4000),
        c006           VARCHAR2(4000),
        c007           VARCHAR2(4000),
        c008           VARCHAR2(4000),
        c009           VARCHAR2(4000),
        c010           VARCHAR2(4000),
        c011           VARCHAR2(4000),
        c012           VARCHAR2(4000),
        c013           VARCHAR2(4000),
        c014           VARCHAR2(4000),
        c015           VARCHAR2(4000),
        c016           VARCHAR2(4000),
        c017           VARCHAR2(4000),
        c018           VARCHAR2(4000),
        c019           VARCHAR2(4000),
        c020           VARCHAR2(4000),
        c021           VARCHAR2(4000),
        c022           VARCHAR2(4000),
        c023           VARCHAR2(4000),
        c024           VARCHAR2(4000),
        c025           VARCHAR2(4000),
        c026           VARCHAR2(4000),
        c027           VARCHAR2(4000),
        c028           VARCHAR2(4000),
        c029           VARCHAR2(4000),
        c030           VARCHAR2(4000),
        c031           VARCHAR2(4000),
        c032           VARCHAR2(4000),
        c033           VARCHAR2(4000),
        c034           VARCHAR2(4000),
        c035           VARCHAR2(4000),
        c036           VARCHAR2(4000),
        c037           VARCHAR2(4000),
        c038           VARCHAR2(4000),
        c039           VARCHAR2(4000),
        c040           VARCHAR2(4000),
        c041           VARCHAR2(4000),
        c042           VARCHAR2(4000),
        c043           VARCHAR2(4000),
        c044           VARCHAR2(4000),
        c045           VARCHAR2(4000),
        c046           VARCHAR2(4000),
        c047           VARCHAR2(4000),
        c048           VARCHAR2(4000),
        c049           VARCHAR2(4000),
        c050           VARCHAR2(4000),
        n001           NUMBER,
        n002           NUMBER,
        n003           NUMBER,
        n004           NUMBER,
        n005           NUMBER,
        d001           DATE,
        d002           DATE,
        d003           DATE,
        d004           DATE,
        d005           DATE,
        clob001        CLOB DEFAULT empty_clob(),
        blob001        BLOB DEFAULT empty_blob(),
        md5_original   VARCHAR2(4000),
        xmltype001     SYS.XMLTYPE
    );

    -- Table type to be used as the pipelined function return type.
    TYPE tacs IS TABLE OF tac;

    /**
     * Retrieves APEX collection data and returns it as a pipelined table of type tacs.
     *
     * @param papp_id          APEX application ID.
     * @param psession_id      APEX session ID.
     * @param pcollection_name Name of the APEX collection to retrieve.
     * @return A pipelined table of collection rows of type tacs.
     */
    FUNCTION get_collection_data(
         papp_id         NUMBER,
         psession_id     NUMBER,
         pcollection_name VARCHAR2
    ) RETURN tacs PIPELINED;

END pkg_apex_collection;
/
 
-- Package Body for pkg_apex_collection
CREATE OR REPLACE PACKAGE BODY pkg_apex_collection AS

    FUNCTION get_collection_data(
         papp_id          NUMBER,
         psession_id      NUMBER,
         pcollection_name VARCHAR2
    ) RETURN tacs PIPELINED
    IS
        -- Variable to hold the workspace ID for the given application.
        v_ws_id      APEX_WORKSPACES.workspace_id%TYPE;
        -- Local copies of input parameters.
        v_app_id     NUMBER := papp_id;
        v_session_id NUMBER := psession_id;
        -- Variable of type tac to map each collection row.
        v_tac      tac;
    BEGIN
        -- Retrieve the workspace ID using the application ID.
        SELECT MAX(workspace_id)
          INTO v_ws_id
          FROM apex_applications
         WHERE application_id = v_app_id;

        -- Set the security context for the current APEX session.
        wwv_flow_api.set_security_group_id(v_ws_id);
        APEX_APPLICATION.g_flow_id   := v_app_id;
        APEX_APPLICATION.g_instance  := v_session_id;

        -- Loop through each row of the specified APEX collection.
        FOR apx IN (
            SELECT *
              FROM apex_collections
             WHERE collection_name = pcollection_name
        ) LOOP
            -- Map each column from the apex_collections table to the local record.
            v_tac.seq_id       := apx.seq_id;
            v_tac.c001         := apx.c001;
            v_tac.c002         := apx.c002;
            v_tac.c003         := apx.c003;
            v_tac.c004         := apx.c004;
            v_tac.c005         := apx.c005;
            v_tac.c006         := apx.c006;
            v_tac.c007         := apx.c007;
            v_tac.c008         := apx.c008;
            v_tac.c009         := apx.c009;
            v_tac.c010         := apx.c010;
            v_tac.c011         := apx.c011;
            v_tac.c012         := apx.c012;
            v_tac.c013         := apx.c013;
            v_tac.c014         := apx.c014;
            v_tac.c015         := apx.c015;
            v_tac.c016         := apx.c016;
            v_tac.c017         := apx.c017;
            v_tac.c018         := apx.c018;
            v_tac.c019         := apx.c019;
            v_tac.c020         := apx.c020;
            v_tac.c021         := apx.c021;
            v_tac.c022         := apx.c022;
            v_tac.c023         := apx.c023;
            v_tac.c024         := apx.c024;
            v_tac.c025         := apx.c025;
            v_tac.c026         := apx.c026;
            v_tac.c027         := apx.c027;
            v_tac.c028         := apx.c028;
            v_tac.c029         := apx.c029;
            v_tac.c030         := apx.c030;
            v_tac.c031         := apx.c031;
            v_tac.c032         := apx.c032;
            v_tac.c033         := apx.c033;
            v_tac.c034         := apx.c034;
            v_tac.c035         := apx.c035;
            v_tac.c036         := apx.c036;
            v_tac.c037         := apx.c037;
            v_tac.c038         := apx.c038;
            v_tac.c039         := apx.c039;
            v_tac.c040         := apx.c040;
            v_tac.c041         := apx.c041;
            v_tac.c042         := apx.c042;
            v_tac.c043         := apx.c043;
            v_tac.c044         := apx.c044;
            v_tac.c045         := apx.c045;
            v_tac.c046         := apx.c046;
            v_tac.c047         := apx.c047;
            v_tac.c048         := apx.c048;
            v_tac.c049         := apx.c049;
            v_tac.c050         := apx.c050;
            v_tac.n001         := apx.n001;
            v_tac.n002         := apx.n002;
            v_tac.n003         := apx.n003;
            v_tac.n004         := apx.n004;
            v_tac.n005         := apx.n005;
            v_tac.d001         := apx.d001;
            v_tac.d002         := apx.d002;
            v_tac.d003         := apx.d003;
            v_tac.d004         := apx.d004;
            v_tac.d005         := apx.d005;
            v_tac.clob001      := apx.clob001;
            v_tac.blob001      := apx.blob001;
            v_tac.md5_original := apx.md5_original;
            v_tac.xmltype001   := apx.xmltype001;

            -- Pipe the current record to the output.
            PIPE ROW(v_tac);
        END LOOP;

        RETURN;
    END get_collection_data;

END pkg_apex_collection;
/        

How to Use This Package?

Now that we have our package, here’s how we can retrieve collection data efficiently:

SELECT * FROM TABLE(
    pkg_apex_collection.get_collection_data(
        papp_id => 100,
        psession_id => 123456,
        pcollection_name => 'MY_COLLECTION'
    )
);        

Whether you’re building JasperReports, integrating with REST APIs, or just optimizing data retrieval, this approach ensures better performance and flexibility.

Hope this helps! Let me know if you have any questions. ??