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 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 Application Express was installed in Oracle Database 12c and with MAX_STRING_SIZE = EXTENDED, then the tables for the Application Express 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.
Topics:
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 are 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.
"Accessing a Collection", "Table 4-7", "Table 4-8"
Every collection contains a named list of data elements (or members) which can have up to 50 character attributes (VARCHAR2(4000)), five number attributes, one XML Type attribute, one large binary attribute (BLOB), and one large character attribute (CLOB). You use the following methods to create a collection:
CREATE_COLLECTION
This method creates an empty collection with the provided name. An exception is raised if the named collection exists.
CREATE_OR_TRUNCATE_COLLECTION
If the provided named collection does not exist, this method creates an empty collection with the given name. If the named collection exists, this method truncates it. Truncating a collection empties it, but leaves it in place.
CREATE_COLLECTION_FROM_QUERY
This method creates a collection and then populates it with the results of a specified query. An exception is raised if the named collection exists. This method can be used with a query with up to 50 columns in the SELECT clause. These columns in the SELECT clause populate the 50 character attributes of the collection (C001 through C050).
CREATE_COLLECTION_FOM_QUERY2
This method creates a collection and then populates it with the results of a specified query. An exception is raised if the named collection exists. It is identical to the CREATE_COLLECTION_FROM_QUERY, however, the first 5 columns of the SELECT clause must be numeric. After the numeric columns, there can be up to 50 character columns in the SELECT clause.
CREATE_COLLECTION_FROM_QUERY_B
This 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 is raised during execution.
The MD5 checksum is not computed for any members in the collection.
CREATE_COLLECTION_FROM_QUERYB2
This method also creates a collection and then populates it with the results of a specified query. An exception is raised if the named collection exists. It is identical to the CREATE_COLLECTION_FROM_QUERY_B, however, the first five columns of the SELECT clause must be numeric. After the numeric columns, there can be up to 50 character columns in the SELECT clause.
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).
"Determining Collection Status" for information about using the GET_MEMBER_MD5 function, "GET_MEMBER_MD5 Function"
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) N001 NUMBER N002 NUMBER N003 NUMBER N004 NUMBER N005 NUMBER 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, clob001 FROM APEX_collections WHERE collection_name = 'DEPARTMENTS'
Topics:
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.
If you truncate a collection, you remove all members from the specified collection, but the named collection remains in place.
If you delete a collection, you delete the collection and all of its members. Be aware that if you do not delete a collection, it is eventually deleted when the session is purged.
Use the DELETE_ALL_COLLECTIONS method to delete all collections defined in the current application.
Use the DELETE_ALL_COLLECTIONS_SESSION method to delete all collections defined in the current session.
Topics:
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 is 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.
You can also add new members (or an array of members) to a collection using the ADD_MEMBERS procedure. The number of members added is based on the number of elements in the first array.
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).
Use p_clob001 for collection member attributes which exceed 4,000 characters. Use p_blob001 for binary collection member attributes. Use p_xmltype001 to store well-formed XML.
"Determining Collection Status" for information about using the function GET_MEMBER_MD5
You can update collection members by calling the UPDATE_MEMBER procedure and referencing the desired collection member by its sequence ID. The UPDATE_MEMBER procedure replaces an entire collection member, not individual member attributes.
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.
You can delete a collection member by calling the DELETE_MEMBER procedure and referencing the desired collection member by its sequence ID. Note that this procedure leaves a gap in the sequence IDs in the specified collection.
You can also delete all members from a collection by when an attribute matches a specific value. Note that the DELETE_MEMBERS procedure also leaves a gap in the sequence IDs in the specified collection. If the supplied attribute value is null, then all members of the named collection are deleted where the attribute (specified by p_attr_number) is null.
Topics:
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.
Use RESEQUENCE_COLLECTION to resequence a collection to remove any gaps in sequence IDs while maintaining the same element order.
Use COLLECTION_EXISTS to determine if a collection exists.
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.
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.
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
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.