{"id":905,"date":"2026-02-21T11:54:17","date_gmt":"2026-02-21T11:54:17","guid":{"rendered":"https:\/\/forgefuse.wasmer.app\/?p=905"},"modified":"2026-02-21T11:54:17","modified_gmt":"2026-02-21T11:54:17","slug":"collections-in-oracle-apex","status":"publish","type":"post","link":"https:\/\/www.forgefuse.net\/index.php\/collections-in-oracle-apex\/","title":{"rendered":"Collections in Oracle APEX"},"content":{"rendered":"\n

When I first started working with Oracle APEX<\/strong>, I struggled with handling temporary data, especially when building multi-step forms, shopping-cart-like functionality, or wizard-style pages. That\u2019s when I discovered APEX Collections<\/strong>, a powerful yet often underused feature that makes session-based data management incredibly simple.<\/p>\n\n\n\n

\n\n\n\n

What Are Collections in Oracle APEX?<\/h4>\n\n\n\n

Oracle Application Express (APEX) provides a feature called Collections<\/strong> that allows developers to store temporary data in memory during a user session.<\/p>\n\n\n\n

Think of a collection as:<\/p>\n\n\n\n

\n

A temporary, session-based table that exists only for the duration of the user\u2019s session.<\/p>\n<\/blockquote>\n\n\n\n

They are extremely useful when:<\/p>\n\n\n\n

    \n
  • You need temporary storage<\/li>\n\n\n\n
  • You want to stage data before committing to a real table<\/li>\n\n\n\n
  • You\u2019re building multi-step processes<\/li>\n\n\n\n
  • You want to manipulate datasets dynamically<\/li>\n<\/ul>\n\n\n\n
    \n\n\n\n

    Understanding How Collections Work<\/h4>\n\n\n\n

    Internally, collections are stored in:<\/p>\n\n\n\n

    WWV_FLOW_COLLECTIONS$<\/pre>\n\n\n\n
    But you should never directly manipulate this table<\/strong>. Instead, you use the APEX_COLLECTION<\/code> API.<\/p>\n\n\n\n
    Key characteristics:<\/p>\n\n\n\n
      \n
    • Session-specific<\/li>\n\n\n\n
    • User-specific<\/li>\n\n\n\n
    • Stored in database session<\/li>\n\n\n\n
    • Automatically cleared when session ends<\/li>\n\n\n\n
    • Not committed permanently unless you copy data to real tables<\/li>\n<\/ul>\n\n\n\n
      \n\n\n\n
      Datatypes Supported in Collections<\/h4>\n\n\n\n
      Each collection row can hold:<\/p>\n\n\n\n
      Character Attributes<\/h6>\n\n\n\n
        \n
      • C001<\/code> to C050<\/code> (50 VARCHAR2 columns)<\/li>\n<\/ul>\n\n\n\n
        Numeric Attributes<\/h6>\n\n\n\n
          \n
        • N001<\/code> to N005<\/code> (5 NUMBER columns)<\/li>\n<\/ul>\n\n\n\n
          Date Attributes<\/h6>\n\n\n\n
            \n
          • D001<\/code> to D005<\/code> (5 DATE columns)<\/li>\n<\/ul>\n\n\n\n
            Large Object<\/h6>\n\n\n\n
              \n
            • CLOB001<\/code><\/li>\n<\/ul>\n\n\n\n
              Binary Attribute<\/h6>\n\n\n\n
                \n
              • BLOB001<\/li>\n<\/ul>\n\n\n\n
                XMLTYPE Attribute<\/h6>\n\n\n\n
                  \n
                • XMLTYPE001<\/li>\n<\/ul>\n\n\n\n
                  MD5_ORIGINAL Attribute<\/h6>\n\n\n\n
                    \n
                  • Varchar of size of 4000 chars<\/li>\n<\/ul>\n\n\n\n
                    So a collection row can hold structured data similar to a table row. And additionally it has following two columns:

                    COLLECTION_NAME   NOT NULL VARCHAR2(255)
                    SEQ_ID            NOT NULL NUMBER <\/p>\n\n\n\n
                    by which you can identify any row easily.<\/p>\n\n\n\n
                    NOTE: there are updates coming, so hopefully soon in newer versions of Apex we’ll get more number columns (compared to current limit of five, where if the need arises, you have to use character columns for numbers… not very effient, right?)<\/p>\n\n\n\n
                    \n\n\n\n
                    Creating a Collection<\/h4>\n\n\n\n
                    BEGIN
                        APEX_COLLECTION.CREATE_COLLECTION(
                            p_collection_name => 'EMP_COLLECTION'
                        );
                    END;<\/pre>\n\n\n\n
                    Or safer version:<\/p>\n\n\n\n
                    BEGIN
                        APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
                            p_collection_name => 'EMP_COLLECTION'
                        );
                    END;<\/pre>\n\n\n\n
                    This ensures the collection exists and is empty.<\/p>\n\n\n\n
                    \n\n\n\n
                    Inserting Data into Collection<\/h4>\n\n\n\n
                    BEGIN
                        APEX_COLLECTION.ADD_MEMBER(
                            p_collection_name => 'EMP_COLLECTION',
                            p_c001 => 'John',
                            p_c002 => 'IT',
                            p_n001 => 5000,
                            p_d001 => SYSDATE
                        );
                    END;<\/pre>\n\n\n\n
                    Here:<\/p>\n\n\n\n
                      \n
                    • C001<\/code> = Employee Name<\/li>\n\n\n\n
                    • C002<\/code> = Department<\/li>\n\n\n\n
                    • N001<\/code> = Salary<\/li>\n\n\n\n
                    • D001<\/code> = Join Date<\/li>\n<\/ul>\n\n\n\n
                      \n\n\n\n
                      Querying Collection Data<\/h4>\n\n\n\n
                      SELECT
                          c001 AS employee_name,
                          c002 AS department,
                          n001 AS salary,
                          d001 AS join_date
                      FROM
                          APEX_COLLECTIONS
                      WHERE
                          collection_name = 'EMP_COLLECTION';<\/pre>\n\n\n\n
                      APEX_COLLECTIONS<\/code> is a view used to access collection data.<\/p>\n\n\n\n
                      \n\n\n\n
                      Deleting a Single Member<\/h5>\n\n\n\n
                      Each row has a SEQ_ID<\/code>.<\/p>\n\n\n\n
                      BEGIN
                          APEX_COLLECTION.DELETE_MEMBER(
                              p_collection_name => 'EMP_COLLECTION',
                              p_seq => 1
                          );
                      END;<\/pre>\n\n\n\n
                      \n\n\n\n
                      Truncating a Collection<\/h4>\n\n\n\n
                      BEGIN
                          APEX_COLLECTION.TRUNCATE_COLLECTION(
                              p_collection_name => 'EMP_COLLECTION'
                          );
                      END;<\/pre>\n\n\n\n
                      Removes all members but keeps collection definition.<\/p>\n\n\n\n
                      \n\n\n\n
                      Deleting Entire Collection<\/h4>\n\n\n\n
                      BEGIN
                          APEX_COLLECTION.DELETE_COLLECTION(
                              p_collection_name => 'EMP_COLLECTION'
                          );
                      END;<\/pre>\n\n\n\n
                      Completely removes it from session.<\/p>\n\n\n\n
                      \n\n\n\n
                      Session Persistence -> How Long Do Collections Live?<\/h4>\n\n\n\n
                      Collections:<\/p>\n\n\n\n
                        \n
                      • Exist per user session<\/li>\n\n\n\n
                      • Persist across page navigations<\/li>\n\n\n\n
                      • Survive commits and rollbacks<\/li>\n\n\n\n
                      • Are automatically deleted when session expires<\/li>\n<\/ul>\n\n\n\n
                        \u26a0\ufe0f Important:
                        They are NOT permanent storage.<\/p>\n\n\n\n
                        If the user logs out or session times out \u2192 collection disappears.<\/p>\n\n\n\n
                        \n\n\n\n
                        Updating Collection Members<\/h4>\n\n\n\n
                        BEGIN
                            APEX_COLLECTION.UPDATE_MEMBER(
                                p_collection_name => 'EMP_COLLECTION',
                                p_seq => 1,
                                p_c001 => 'Michael',
                                p_n001 => 7000
                            );
                        END;<\/pre>\n\n\n\n
                        \n\n\n\n
                        Checking If Collection Exists<\/h4>\n\n\n\n
                        SELECT APEX_COLLECTION.COLLECTION_EXISTS('EMP_COLLECTION')
                        FROM dual;<\/pre>\n\n\n\n
                        I found this useful to have on page load (if it doesn’t exist, then create it)<\/p>\n\n\n\n
                        \n\n\n\n
                        Loading Data from a Query<\/h4>\n\n\n\n
                        BEGIN
                            APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
                                p_collection_name => 'EMP_COLLECTION',
                                p_query => 'SELECT ename, deptno, sal FROM emp'
                            );
                        END;<\/pre>\n\n\n\n
                        This is powerful for staging real table data into a temporary structure.<\/p>\n\n\n\n
                        \n\n\n\n
                        Some examples of using collections:<\/h4>\n\n\n\n
                        1\ufe0f. Wizard-Style Multi-Step Forms<\/h5>\n\n\n\n
                        Use collections to:<\/p>\n\n\n\n
                          \n
                        • Store step 1 data<\/li>\n\n\n\n
                        • Store step 2 data<\/li>\n\n\n\n
                        • Validate everything<\/li>\n\n\n\n
                        • Insert to real tables only at final step<\/li>\n<\/ul>\n\n\n\n
                          \n\n\n\n
                          2\ufe0f. Shopping Cart Implementation<\/h5>\n\n\n\n
                            \n
                          • Add products to collection<\/li>\n\n\n\n
                          • Update quantity<\/li>\n\n\n\n
                          • Calculate totals using SUM(n001)<\/li>\n\n\n\n
                          • Insert order when user checks out<\/li>\n<\/ul>\n\n\n\n
                            \n\n\n\n
                            3\ufe0f. Bulk Processing<\/h5>\n\n\n\n
                            Instead of committing row by row:<\/p>\n\n\n\n
                              \n
                            • Collect data in collection<\/li>\n\n\n\n
                            • Process in batch PL\/SQL block<\/li>\n\n\n\n
                            • Commit once<\/li>\n<\/ul>\n\n\n\n
                              \n\n\n\n
                              4\ufe0f. Complex Validations Before Insert<\/h5>\n\n\n\n
                              Use collection as:<\/p>\n\n\n\n
                              \n
                              Staging table before final commit<\/p>\n<\/blockquote>\n\n\n\n
                              Validate:<\/p>\n\n\n\n
                                \n
                              • Duplicates<\/li>\n\n\n\n
                              • Business rules<\/li>\n\n\n\n
                              • Cross-record validation<\/li>\n<\/ul>\n\n\n\n
                                \n\n\n\n
                                Performance Considerations<\/h4>\n\n\n\n
                                  \n
                                • Collections are stored in database tables<\/li>\n\n\n\n
                                • Large collections may impact performance<\/li>\n\n\n\n
                                • Clean them when not needed<\/li>\n\n\n\n
                                • Avoid storing huge CLOBs unnecessarily<\/li>\n<\/ul>\n\n\n\n
                                  \n\n\n\n
                                  When Should You Use Collections?<\/h4>\n\n\n\n
                                  Use Collections When:<\/p>\n\n\n\n
                                  \u2714 You need temporary storage
                                  \u2714 Data is session-scoped
                                  \u2714 Multi-step forms
                                  \u2714 Staging before insert
                                  \u2714 Complex validations<\/p>\n\n\n\n
                                  Avoid When:<\/p>\n\n\n\n
                                  \u2718 Data must be persistent
                                  \u2718 Large-scale reporting
                                  \u2718 Cross-session sharing required<\/p>\n\n\n\n
                                  \n\n\n\n
                                  Addition, using Apex collection with file uploads<\/h4>\n\n\n\n
                                  Once I found myself I need to use Apex collections as temporary session storage, but I needed to upload a file as well (of course prior of commiting it to actual database table), and is there a way? There is, let me simplify it for you… <\/p>\n\n\n\n
                                  Note that you can use file upload to apex temporary file table without using collections, but since this blog post is about collections, let do it completely.<\/p>\n\n\n\n
                                  How File Upload Works in APEX<\/h5>\n\n\n\n
                                  When you use a File Browse<\/strong> item in APEX:<\/p>\n\n\n\n
                                    \n
                                  1. User selects a file<\/li>\n\n\n\n
                                  2. APEX uploads it into a temporary table<\/li>\n\n\n\n
                                  3. The file is stored in:<\/li>\n<\/ol>\n\n\n\n
                                    APEX_APPLICATION_TEMP_FILES<\/pre>\n\n\n\n
                                    This table holds the file for the duration of the session<\/strong>.<\/p>\n\n\n\n
                                    \n\n\n\n
                                    Structure of APEX_APPLICATION_TEMP_FILES<\/h5>\n\n\n\n
                                    Important columns:<\/p>\n\n\n\n
                                    Column<\/th>Description<\/th><\/tr><\/thead>
                                    NAME<\/td>Internal APEX file reference<\/td><\/tr>
                                    FILENAME<\/td>Original file name<\/td><\/tr>
                                    MIME_TYPE<\/td>File content type<\/td><\/tr>
                                    BLOB_CONTENT<\/td>Actual file data<\/td><\/tr>
                                    CREATED_ON<\/td>Upload timestamp<\/td><\/tr>
                                    CREATED_BY<\/td>User<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
                                    This table is session-specific.<\/p>\n\n\n\n
                                    \u26a0\ufe0f Data is automatically cleaned up when the session ends. Just to note again \ud83d\ude42<\/p>\n\n\n\n
                                    \n\n\n\n
                                    Basic Example -> Fetch Uploaded File<\/h5>\n\n\n\n
                                    Step 1: Create File Browse Item<\/h4>\n\n\n\n
                                    Create a page item:<\/p>\n\n\n\n
                                    P1_FILE
                                    Type: File Browse
                                    Storage Type: Table APEX_APPLICATION_TEMP_FILES<\/pre>\n\n\n\n
                                    \n\n\n\n
                                    Step 2: Fetch File from Temporary Table<\/h4>\n\n\n\n
                                    After submit:<\/p>\n\n\n\n
                                    DECLARE
                                        l_blob        BLOB;
                                        l_filename    VARCHAR2(255);
                                        l_mime_type   VARCHAR2(255);
                                    BEGIN
                                        SELECT blob_content,
                                               filename,
                                               mime_type
                                        INTO   l_blob,
                                               l_filename,
                                               l_mime_type
                                        FROM   apex_application_temp_files
                                        WHERE  name = :P1_FILE;    -- Now you can insert into your own table
                                        INSERT INTO my_documents (
                                            file_name,
                                            mime_type,
                                            file_content,
                                            uploaded_on
                                        ) VALUES (
                                            l_filename,
                                            l_mime_type,
                                            l_blob,
                                            SYSDATE
                                        );END;<\/pre>\n\n\n\n
                                    \u2705 This permanently stores the file.<\/p>\n\n\n\n
                                    \n\n\n\n
                                    Using Collections with File Uploads (Very Powerful)<\/h4>\n\n\n\n
                                    Now let\u2019s combine uploads with APEX Collections<\/strong>.<\/p>\n\n\n\n
                                    Common use case:<\/p>\n\n\n\n
                                    \n
                                    Upload multiple files \u2192 validate \u2192 show preview \u2192 commit later<\/p>\n<\/blockquote>\n\n\n\n
                                    \n\n\n\n
                                    Store Uploaded File in Collection<\/h4>\n\n\n\n
                                    BEGIN
                                        FOR rec IN (
                                            SELECT filename,
                                                   mime_type,
                                                   blob_content
                                            FROM   apex_application_temp_files
                                            WHERE  name = :P1_FILE
                                        )
                                        LOOP
                                            APEX_COLLECTION.ADD_MEMBER(
                                                p_collection_name => 'FILE_UPLOAD_COLLECTION',
                                                p_c001            => rec.filename,
                                                p_c002            => rec.mime_type,
                                                p_blob001         => rec.blob_content
                                            );
                                        END LOOP;
                                    END;<\/pre>\n\n\n\n
                                    Now the file is stored in a session-based collection instead of directly committing to DB.<\/p>\n\n\n\n
                                    \n\n\n\n
                                    Display Files from Collection<\/h4>\n\n\n\n
                                    SELECT
                                        seq_id,
                                        c001 AS file_name,
                                        c002 AS mime_type
                                    FROM
                                        apex_collections
                                    WHERE
                                        collection_name = 'FILE_UPLOAD_COLLECTION';<\/pre>\n\n\n\n
                                    You can create an Interactive Report on this query. (and even functionality to download the file while in collection -> download blob type)<\/p>\n\n\n\n
                                    \n\n\n\n
                                    Cleaning Up Temporary Files<\/h4>\n\n\n\n
                                    After processing, you may explicitly remove temp files:<\/p>\n\n\n\n
                                    DELETE FROM apex_application_temp_files
                                    WHERE name = :P1_FILE;<\/pre>\n\n\n\n
                                    Or let APEX handle it automatically at session end.<\/p>\n\n\n\n
                                    \n\n\n\n
                                    Multiple File Upload Example<\/h4>\n\n\n\n
                                    If File Browse item allows multiple files:<\/p>\n\n\n\n
                                    FOR rec IN (
                                        SELECT *
                                        FROM   apex_application_temp_files
                                        WHERE  created_by = :APP_USER
                                    )
                                    LOOP
                                        -- process each file
                                    END LOOP;<\/pre>\n\n\n\n
                                    \n\n\n\n
                                    Difference: Temp Files vs Collections<\/h4>\n\n\n\n
                                    Feature<\/th>Temp Files<\/th>Collections<\/th><\/tr><\/thead>
                                    Stores BLOB<\/td>\u2705<\/td>\u2705<\/td><\/tr>
                                    Session-based<\/td>\u2705<\/td>\u2705<\/td><\/tr>
                                    Structured columns<\/td>\u274c<\/td>\u2705<\/td><\/tr>
                                    Good for staging data<\/td>\u26a0\ufe0f Limited<\/td>\u2705<\/td><\/tr>
                                    Good for file upload<\/td>\u2705<\/td>\u2705 (after copy)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
                                    Best practice:<\/p>\n\n\n\n
                                    \ud83d\udc49 Use APEX_APPLICATION_TEMP_FILES<\/code> only as entry point
                                    \ud83d\udc49 Move data into collections or your own tables<\/p>\n\n\n\n
                                    \n\n\n\n
                                    Performance & Best Practices<\/h4>\n\n\n\n
                                    \u2714 Move files to permanent table ASAP
                                    \u2714 Avoid keeping huge BLOBs in collection unnecessarily
                                    \u2714 Clean up collections when done
                                    \u2714 Limit file size in File Browse item<\/p>\n\n\n\n
                                    \n\n\n\n
                                    Understanding file uploads in APEX completes the picture of APEX Collections<\/strong>.<\/p>\n\n\n\n
                                    The typical enterprise pattern is:<\/p>\n\n\n\n
                                    File Upload
                                        \u2193
                                    APEX_APPLICATION_TEMP_FILES
                                        \u2193
                                    APEX_COLLECTION (validation \/ staging)
                                        \u2193
                                    Permanent Tables<\/pre>\n\n\n\n
                                    \n\n\n\n
                                    Final Thoughts<\/h4>\n\n\n\n
                                    APEX Collections are one of the most powerful features in Oracle APEX that many beginners overlook.<\/p>\n\n\n\n
                                    When I first found them, I was trying to solve:<\/p>\n\n\n\n
                                    \n
                                    \u201cHow do I temporarily store rows without committing them?\u201d<\/p>\n<\/blockquote>\n\n\n\n
                                    Collections were the answer.<\/p>\n\n\n\n
                                    They are:<\/p>\n\n\n\n
                                      \n
                                    • Simple<\/li>\n\n\n\n
                                    • Powerful<\/li>\n\n\n\n
                                    • Session-aware<\/li>\n\n\n\n
                                    • Highly flexible<\/li>\n<\/ul>\n\n\n\n
                                      Mastering them significantly improves your ability to build professional-grade APEX applications.<\/p>\n\n\n\n
                                      And don’t forget, you can have multiple collections in your session, just name them distinctively.<\/p>\n\n\n\n
                                      <\/p>\n","protected":false},"excerpt":{"rendered":"
                                      In this post, I\u2019ll walk you through what APEX Collections are, how to create and manage them, what datatypes they support, and how to use them from beginner to advanced level, complete with practical examples.<\/p>\n","protected":false},"author":1,"featured_media":906,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":109,"footnotes":""},"categories":[2,14],"tags":[16,17,15],"class_list":["post-905","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-article","category-oracle-apex","tag-apex","tag-collections","tag-oracle"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/posts\/905","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/comments?post=905"}],"version-history":[{"count":0,"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/posts\/905\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/media\/906"}],"wp:attachment":[{"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/media?parent=905"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/categories?post=905"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/tags?post=905"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}