Creating a Collection

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 use the following methods to create 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.