Naming, Creating and Accessing Collections

Topics:

Naming Collections
Creating a Collection
About the Parameter p_generate_md5

Naming Collections

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.

Creating a Collection

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_COLLECTIONThis 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_QUERYThis 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 will populate the 50 character attributes of the collection (C001 through C050).
CREATE_COLLECTION_FOM_QUERY2This 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_BThis 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.
CREATE_COLLECTION_FROM_QUERYB2This 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.

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

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