About Using Collections

Use collections to temporarily capture one or more nonscalar values. Collections enable you to store rows and columns currently in session state so they can be accessed, manipulated, or processed during a user’s specific session.

Think of a collection as a bucket in which you temporarily store and name rows of information.

The following are examples of when you might use collections:

When you are creating a data-entry wizard in which multiple rows of information first need to be collected within a logical transaction. You can use collections to temporarily store the contents of the multiple rows of information, before performing the final step in the wizard when both the physical and logical transactions are completed.
When your application includes an update page on which a user updates multiple detail rows on one page. The user can make many updates, apply these updates to a collection and then call a final process to apply the changes to the database.
When you are building a wizard where you are collecting an arbitrary number of attributes. At the end of the wizard, the user then performs a task that takes the information temporarily stored in the collection and applies it to the database.

You insert, update, and delete collection information using the PL/SQL API APEX_COLLECTION.

To view an example, install the sample app, Sample Collections. To learn more, see Installing Apps from the Gallery.

About the APEX_COLLECTION API

Every collection contains a named list of data elements (or members) which can have up to 50 character attributes (VARCHAR2(4000)), five number attributes, five date attributes, one XML Type attribute, one large binary attribute (BLOB), and one large character attribute (CLOB). You insert, update, and delete collection information using the PL/SQL API APEX_COLLECTION.

The following are examples of when you might use collections:

When you are creating a data-entry wizard in which multiple rows of information first need to be collected within a logical transaction. You can use collections to temporarily store the contents of the multiple rows of information, before performing the final step in the wizard when both the physical and logical transactions are completed.
When your application includes an update page on which a user updates multiple detail rows on one page. The user can make many updates, apply these updates to a collection and then call a final process to apply the changes to the database.
When you are building a wizard where you are collecting an arbitrary number of attributes. At the end of the wizard, the user then performs a task that takes the information temporarily stored in the collection and applies it to the database.

Beginning in Oracle AI Database 12c, database columns of data type VARCHAR2 can be defined up to 32,767 bytes. This requires that the database initialization parameter MAX_STRING_SIZE has a value of EXTENDED. If Oracle APEX was installed in Oracle AI Database 12c and with MAX_STRING_SIZE=EXTENDED, then the tables for the APEX collections will be defined to support up 32,767 bytes for the character attributes of a collection. For the methods in the APEX_COLLECTION API, all references to character attributes (c001 through c050) can support up to 32,767 bytes.

See Also: APEX_COLLECTION in Oracle APEX API Reference

Accessing a Collection

You can access the members of a collection by querying the database view APEX_COLLECTIONS. Collection names are always converted to uppercase. When querying the APEX_COLLECTIONS view, always specify the collection name in all uppercase. The APEX_COLLECTIONS view has the following definition:

COLLECTION_NAME   NOT NULL VARCHAR2(255)
SEQ_ID            NOT NULL NUMBER
C001              VARCHAR2(4000)
C002              VARCHAR2(4000)
C003              VARCHAR2(4000)
C004              VARCHAR2(4000)
C005              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
BLOB001           BLOB
XMLTYPE001        XMLTYPE
MD5_ORIGINAL      VARCHAR2(4000)

Use the APEX_COLLECTIONS view in an application just as you would use any other table or view in an application, for example:

SELECT c001, c002, c003, n001, d001, clob001
   FROM APEX_collections
 WHERE collection_name = 'DEPARTMENTS'

Determining Collection Status

The p_generate_md5 parameter determines if the MD5 message digests are computed for each member of a collection. The collection status flag is set to FALSE immediately after you create a collection. If any operations are performed on the collection (such as add, update, truncate, and so on), this flag is set to TRUE.

You can reset this flag manually by calling RESET_COLLECTION_CHANGED.

Once this flag has been reset, you can determine if a collection has changed by calling COLLECTION_HAS_CHANGED.

When you add a new member to a collection, an MD5 message digest is computed against all 50 attributes and the CLOB attribute if the p_generated_md5 parameter is set to YES. You can access this value from the MD5_ORIGINAL column of the view APEX_COLLECTION. You can access the MD5 message digest for the current value of a specified collection member by using the function GET_MEMBER_MD5.

See Also:

Clearing Collection Session State

Clearing the session state of a collection removes the collection members. A shopping cart is a good example of when you might need to clear collection session state. When a user requests to empty the shopping cart and start again, you must clear the session state for a collection. You can remove session state of a collection by calling the TRUNCATE_COLLECTION method or by using f?p syntax.

Calling the TRUNCATE_COLLECTION method deletes the existing collection and then recreates it, for example:

APEX_COLLECTION.TRUNCATE_COLLECTION(
    p_collection_name => collection name);

You can also use the sixth f?p syntax argument to clear session state, for example:

f?p=App:Page:Session::NO:collection name

See Also: TRUNCATE_COLLECTION Procedure in Oracle APEX API Reference