Using Collections

Collections enable you to temporarily capture one or more nonscalar values. You can use collections to store rows and columns currently in session state so they can be accessed, manipulated, or processed during a user's specific session. You can 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.

Topics in this section include:

About the APEX_COLLECTION API
Creating a Collection
Truncating a Collection
Accessing a Collection
Deleting a Collection
Adding Members to a Collection
Updating Collection Members
Deleting Collection Members
Determining Collection Status
Merging Collections
Managing Collections
Clearing Collection Session State

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)), and one large character attribute (CLOB). You insert, update, and delete collection information using the PL/SQL API APEX_COLLECTION.

About Collection Naming

When you create a collection, you must give it a name that cannot exceed 255 characters. Note that collection names are not case-sensitive and will be converted to uppercase.

Once the collection is named, you can access the values in the collection by running a SQL query against the view APEX_COLLECTIONS.

See Also:

"Accessing a Collection"

Creating a Collection

CREATE_COLLECTION
CREATE_OR_TRUNCATE_COLLECTION
CREATE_COLLECTION_FROM_QUERY
CREATE_COLLECTION_FROM_QUERY_B

The CREATE_COLLECTION method raises an exception if the named collection exists, for example:

APEX_COLLECTION.CREATE_COLLECTION(
    p_collection_name => collection name );

The CREATE_OR_TRUNCATE_COLLECTION method creates a collection if the named collection does not exist. If the named collection exists, this method truncates it. Truncating a collection empties it, but leaves it in place, for example:

APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
    p_collection_name => collection name);

The CREATE_COLLECTION_FROM_QUERY method creates a collection and then populates it with the results of a specified query, for example:

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
    p_collection_name => collection name,
    p_query           => your query ,
    p_generate_md5    => 'YES' or 'NO' );

The CREATE_COLLECTION_FROM_QUERY_B method also creates a collection and then populates it with the results of a specified query, for example:

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
    p_collection_name => collection name,
    p_query           => your query );

The CREATE_COLLECTION_FROM_QUERY_B method offers significantly faster performance than the CREATE_COLLECTION_FROM_QUERY method by performing bulk SQL operations, but has the following limitations:

No column value in the select list of the query can be more than 2,000 bytes. If a row is encountered that has a column value of more than 2,000 bytes, an error will be raised during execution.
The MD5 checksum will not be computed for any members in the collection.

About the Parameter p_generate_md5

Use the p_generate_md5 flag to specify if the message digest of the data of the collection member should be computed. By default, this flag is set to NO. Use this parameter to check the MD5 of the collection member (that is, compare it with another member or see if a member has changed).

See Also:

"Determining Collection Status" for information about using the GET_MEMBER_MD5 function

Truncating a Collection

If you truncate a collection, you remove all members from the specified collection, but the named collection remains in place, for example:

APEX_COLLECTION.TRUNCATE_COLLECTION(
    p_collection_name => collection name );

Accessing a Collection

You can access the members of a collection by querying the database view APEX_COLLECTIONS. 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)
CLOB001                    CLOB   
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
   FROM APEX_collections
 WHERE collection_name = 'FIREARMS'

Deleting a Collection

If you delete a collection, you delete the collection and all of its members, for example:

APEX_COLLECTION.DELETE_COLLECTION (
    p_collection_name => collection name );

Be aware that if you do not delete a collection, it will eventually be deleted when the session is purged. For example:

Deleting All Collections for the Current Application

Use the DELETE_ALL_COLLECTIONS method to delete all collections defined in the current application, for example:

APEX_COLLECTION.DELETE_ALL_COLLECTIONS;

Deleting All Collections in the Current Session

Use the DELETE_ALL_COLLECTIONS_SESSION method to delete all collections defined in the current session., for example:

APEX_COLLECTION.DELETE_ALL_COLLECTIONS_SESSION;

Adding Members to a Collection

When data elements (or members) are added to a collection, they are assigned a unique sequence ID. As you add members to a collection, the sequence ID will change in increments of 1, with the newest members having the largest ID.

You add new members to a collection using the ADD_MEMBER function. Calling this function returns the sequence ID of the newly added member. The following example demonstrates how to use the ADD_MEMBER function.

APEX_COLLECTION.ADD_MEMBER(
    p_collection_name => collection name,
    p_c001          => [member attribute 1],
    p_c002          => [member attribute 2],
    p_c003          => [member attribute 3],
    p_c004          => [member attribute 4],
    p_c005          => [member attribute 5],
    p_c006          => [member attribute 6],
    p_c007          => [member attribute 7],
    ...
    p_c050          => [member attribute 50]);
    p_clob001       => [CLOB member attribute 1],
    p_generate_md5  => 'YES' or 'NO');

You can also add new members (or an array of members) to a collection using the ADD_MEMBERS method, for example:

APEX_COLLECTION.ADD_MEMBERS(
    p_collection_name => collection name,
    p_c001          => member attribute array 1,
    p_c002          => member attribute array 2,
    p_c003          => member attribute array 3,
    p_c004          => member attribute array 4,
    p_c005          => member attribute array 5,
    p_c006          => member attribute array 6,
    p_c007          => member attribute array 7,
    ...
    p_c050          => member attribute array 50);
    p_generate_md5  => 'YES' or 'NO');

This method raises an error if the specified collection does not exist with the specified name of the current user and in the same session. Also any attribute exceeding 4,000 characters will be truncated to 4,000 characters. The number of members added is based on the number of elements in the first array.

About the Parameters p_generate_md5 and p_clob001

Use p_clob001 for collection member attributes which exceed 4,000 characters.

See Also:

"Determining Collection Status" for information about using the function GET_MEMBER_MD5

Updating Collection Members

You can update collection members by calling the UPDATE_MEMBER procedure and referencing the desired collection member by its sequence ID, for example:

APEX_COLLECTION.UPDATE_MEMBER (
    p_collection_name => collection name,
    p_seq             => member sequence number,
    p_c001            => member attribute 1,
    p_c002            => member attribute 2,
    p_c003            => member attribute 3,
    p_c004            => member attribute 4,
    p_c005            => member attribute 5,
    p_c006            => member attribute 6,
    p_c007            => member attribute 7,
    ...
    p_c050            => member attribute 50),
    p_clob001         => [CLOB member attribute 1];

The UPDATE_MEMBER procedure replaces an entire collection member, not individual member attributes. This procedure causes an error if the named collection does not exist.

Use the p_clob001 parameter for collection member attributes which exceed 4,000 characters.

To update a single attribute of a collection member, use the UPDATE_MEMBER_ATTRIBUTE procedure, for example:

APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE(
    p_collection_name       => collection_name,
    p_seq                   => member sequence number,
    p_attr_number           => member attribute number,
    p_attr_value            => member attribute value )

APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE(
    p_collection_name        => collection_name,
    p_seq                    => member sequence number,
    p_clob_number            => CLOB member attribute number,
    p_clob_value             => CLOB member attribute value );

Calling the UPDATE_MEMBER_ATTRIBUTE procedure causes an error if the named collection does not exist.

Note that the only valid value for the p_clob_number parameter is 1.

Deleting Collection Members

You can delete a collection member by calling the DELETE_MEMBER procedure and referencing the desired collection member by its sequence ID, for example:

APEX_COLLECTION.DELETE_MEMBER(
    p_collection_name => collection name,
    p_seq             => member sequence number);

Note that this procedure leaves a gap in the sequence IDs in the specified collection. In addition, calling this procedure causes an error if the named collection does not exist.

You can also delete all members from a collection by when an attribute matches a specific value, for example:

APEX_COLLECTION.DELETE_MEMBERS(
    p_collection_name => collection name,
    p_attr_number     => number of attribute used to match for the specified
                         attribute value for deletion, 
    p_attr_value      => attribute value of the member attribute used to 
                         match for deletion);

Note that the DELETE_MEMBERS procedure also leaves a gap in the sequence IDs in the specified collection. This procedure causes an error if:

The named collection does not exist.
The specified attribute number is outside the range of 1 to 50, or not valid.

If the supplied attribute value is null, then all members of the named collection will be deleted where the attribute (specified by p_attr_number) is null.

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, for example:

APEX_COLLECTION.RESET_COLLECTION_CHANGED (
    p_collection_name => collection name)

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

l_changed := APEX_COLLECTION.COLLECTION_HAS_CHANGED(
  p_collection_name => collection_name);

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. For example:

APEX_COLLECTION.GET_MEMBER_MD5 (
    p_collection_name => collection name,
    p_seq             => member sequence number );
    RETURN VARCHAR2;

Merging Collections

You can merge members of a collection with values passed in a set of arrays. By using the p_init_query argument, you can create a collection from the supplied query.

Note that if the collection exists, the following occurs:

Rows in the collection not in the arrays will be deleted.
Rows in the collection and in the arrays will be updated.
Rows in the array and not in the collection will be inserted.

Any attribute value exceeding 4,000 characters will be truncated to 4,000 characters. Table 15-1 describes the available arguments you can use when merging collections.

Table 15-1 Available Arguments for Merging Collections

Argument	Description
`p_collection_name`	Name of the collection. See Also: "About Collection Naming"
`p_seq`	Identifies the sequence number of the member to be merged.
`p_c001`	Array of first attribute values to be merged. Maximum length is 4,000 characters. If the maximum length is greater, it will be truncated to 4,000 characters. The count of elements in the P_C001 PL/SQL table is used as the total number of items across all PL/SQL tables. For example, if P_C001.count = 2 and P_C002.count = 10, only 2 members will be merged. Note that if P_C001 is null, an application error will be raised.
`p_c0xx`	Attribute of `XX` attributes values to be merged. Maximum length is 4,000 characters. If the maximum length is greater, it will be truncated to 4,000 characters.
`p_null_index`	Use this argument to identify rows the merge function should ignore. This argument identifies an row as null. Null rows are automatically removed from the collection.
`p_null_value`	Use this argument with the `p_null_index`. Identifies the null value. If used this value cannot be null. A typical value for this argument is 0.
`p_init_query`	Use the query defined by this argument to create a collection if the collection does not exist.

Managing Collections

You can use the following utilities to manage collections.

Topics in this section include:

Obtaining a Member Count
Resequencing a Collection
Verifying Whether a Collection Exists
Adjusting a Member Sequence ID
Sorting Collection Members

Obtaining a Member Count

Use COLLECTION_MEMBER_COUNT to return the total count of all members in a collection. Note that this count does not indicate the highest sequence in the collection, for example:

l_count := APEX_COLLECTION.COLLECTION_MEMBER_COUNT (
  p_collection_name => collection name );

Resequencing a Collection

Use RESEQUENCE_COLLECTION to resequence a collection to remove any gaps in sequence IDs while maintaining the same element order, for example:

APEX_COLLECTION.RESEQUENCE_COLLECTION (
   p_collection_name => collection name )

Verifying Whether a Collection Exists

Use COLLECTION_EXISTS to determine if a collection exists, for example:

l_exists := APEX_COLLECTION.COLLECTION_EXISTS  (
  p_collection_name => collection name );

Adjusting a Member Sequence ID

You can adjust the sequence ID of a specific member within a collection by moving the ID up or down. When you adjust a sequence ID, the specified ID is exchanged with another ID. For example, if you were to move the ID 2 up, 2 becomes 3, and 3 would become 2.

Use MOVE_MEMBER_UP to adjust a member sequence ID up by one. Alternately, use MOVE_MEMBER_DOWN to adjust a member sequence ID down by one, for example:

APEX_COLLECTION.MOVE_MEMBER_DOWN(
    p_collection_name => collection name,
    p_seq             => member sequence number);

Note that while using either of these methods an application error displays:

If the named collection does not exist for the current user in the current session
If the member specified by the p_seq sequence ID does not exist

However, an application error will not be returned if the specified member has the highest or lowest sequence ID in the collection (depending on if you are calling MOVE_MEMBER_UP or MOVE_MEMBER_DOWN).

Sorting Collection Members

Use the SORT_MEMBERS method to reorder members of a collection by the column number. This method sorts the collection by a particular column number and also reassigns the sequence IDs for each member to remove gaps, for example:

APEX_COLLECTION.SORT_MEMBERS(
    p_collection_name       => collection name,
    p_sort_on_column_number => column number to sort by);

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 re-creates 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:

"Understanding URL Syntax"