Managing Collections

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