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. Think of a collection as a bucket in which you can temporarily store and name rows of information.

Examples of when you might use collections include:

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, prior to performing the final wizard step 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. They can make many updates, apply these updates to a collection, 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:

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

About the HTMLDB_COLLECTION API

Every collection contains a named list of data elements (or members) which can have up to 50 attributes (or columns). You insert, update, and delete collection information using the PL/SQL API HTMLDB_COLLECTION.

About Collection Naming

When you create a new 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 named, you can access the values in a collection by running a SQL query against the view HTMLDB_COLLECTIONS.

See Also:

"Accessing a Collection"

Creating a Collection

Every collection contains a named list of data elements (or members) which can have up to 50 attributes (or columns). Use the following methods to create a collection:

CREATE_COLLECTION
CREATE_OR_TRUNCATE_COLLECTION
CREATE_COLLECTION_FROM_QUERY

CREATE_COLLECTION raises an exception if the named collection already exists. For example:

HTMLDB_COLLECTION.CREATE_COLLECTION(
    p_collection_name => collection name );

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

HTMLDB_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
    p_collection_name => collection name );
    p_generate_md5    => YES or NO );

CREATE_COLLECTION_FROM_QUERY creates a collection and then populates it with the results of a specified query. For example:

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

CREATE_COLLECTION_FROM_QUERY_B also creates a collection and then populates it with the results of a specified query. For example:

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

CREATE_COLLECTION_FROM_QUERY_B offers significantly faster performance than CREATE_COLLECTION_FROM_QUERY 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 function GET_MEMBER_MD5

Truncating a Collection

Truncating a collection removes all members from the specified collection, but leaves the named collection in place. For example:

HTMLDB_COLLECTION.TRUNCATE_COLLECTION(
    p_collection_name => collection name );

Accessing a Collection

Accessing the members of a collection can be accomplished by querying the database view HTMLDB_COLLECTIONS. The HTMLDB_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 HTMLDB_COLLECTIONS view in an Oracle HTML DB application just as you would use any other table or view in an application. For example:

SELECT c001, c002, c003
   FROM htmldb_collections
 WHERE collection_name = 'FIREARMS'

Deleting a Collection

Deleting a collection deletes the collection and all of its members. Be aware that if you do not delete a collection, it will eventually be deleted when the session is purged. For example:

HTMLDB_COLLECTION.DELETE_COLLECTION (
    p_collection_name => collection name );

Deleting All Collections for the Current Application

Use the method DELETE_ALL_COLLECTIONS to delete all collections defined in the current application. For example:

HTMLDB_COLLECTION.DELETE_ALL_COLLECTIONS;

Deleting All Collections in the Current Session

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

HTMLDB_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 member to a collection using the function ADD_MEMBER. Calling this method returns the sequence ID of the newly added member. The following example demonstrates how to use the procedure ADD_MEMBER.

HTMLDB_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);

The next example demonstrates how to use the function ADD_MEMBER. This function returns the sequence number assigned to the newly created member.

l_id := HTMLDB_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 method ADD_MEMBERS. This method raises an exception 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. For example:

HTMLDB_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);

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 UPDATE_MEMBER and referencing the desired collection member by its sequence ID. This procedure replaces an entire collection member, not individual member attributes. This procedure raises an exception if the named collection does not exist. For example:

HTMLDB_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],

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

If you want to update a single attribute of a collection member, use UPDATE_MEMBER_ATTRIBUTE. Calling this procedure raises an exception if the named collection does not exist. For example:

HTMLDB_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 )

HTMLDB_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 );

Note that the only valid value for p_clob_number is 1.

Deleting a Collection Member

You can delete a collection member by calling DELETE_MEMBER and referencing the desired collection member by its sequence ID. For example:

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

Be aware that this procedure leaves a gap in the sequence IDs in the specified collection. Also, calling this procedure results in 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:

HTMLDB_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);

Be aware that this procedure also leaves a gap in the sequence IDs in the specified collection. Also, this procedure raises an exception if:

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

If the supplied attribute value is null, then all members of the named collection will deleted.

Determining Collection Status

The p_generate_md5 parameter determines whether 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:

HTMLDB_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 := HTMLDB_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 HTMLDB_COLLECTION using the function GET_MEMBER_MD5. For example:

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

Merging Collections

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

HTMLDB_COLLECTION.MERGE_MEMBERS
p_collection_name => collection_name

Be aware, however, 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 14-1 describes the available arguments you can use when merging collections.

Table 14-1 Available Arguments for Merging Collections

Argument	Description
`p_c001`	Array of first attribute values to be merged. Maximum length can be 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. Be aware that if P_C001 is null, an application error will be raised.
`p_c0xx`	Attribute of `XX` attributes values to be merged. Maximum length can be 4,000 characters. If the maximum length is greater, it will be truncated to 4,000 characters.
`p_collection_name`	Name of the collection. See Also: "About Collection Naming"
`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. Use `p_null_index` in conjunction with.
`p_null_value`	Use this argument in conjunction 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:

Obtaining a Member Count
Resequencing a Collection
Verifying Whether a Collection Exists
Adjusting 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. Be aware that this count does not imply the highest sequence in the collection. For example:

l_count := HTMLDB_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:

HTMLDB_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 := HTMLDB_COLLECTION.COLLECTION_EXISTS  (
  p_collection_name => collection name );

Adjusting 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 one. For example, if you were to move the ID 2 up, 2 would become 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:

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

Be aware 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 sequence ID p_seq does not exist

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

Sorting Collection Members

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

HTMLDB_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 his or her cart and start again, you would need to clear the session state for a collection. You can remove session state of a collection by calling the CREATE_OR_TRUNCATE_COLLECTION method or by using f?p syntax.

Calling CREATE_OR_TRUNCATE_COLLECTION deletes the existing collection and then recreates it. For example:

HTMLDB_COLLECTION.CREATE_OR_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:1,2,3,collection name

See Also:

"Understanding URL Syntax"