CREATE_COLLECTION_FROM_QUERY Procedure

Creates a collection from a supplied query. The query is parsed as the application owner.

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

If a collection exists with the same name for the current user in the same session for the current Application ID, an application error occurs.

Syntax

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
    p_collection_name       IN VARCHAR2,
    p_query                 IN VARCHAR2,
    p_generate_md5          IN VARCHAR2 DEFAULT 'NO',
    p_truncate_if_exists    IN VARCHAR2 DEFAULT 'NO' )

Parameters

Parameter	Description
`p_collection_name`	The name of the collection. The maximum length is 255 characters. An error is returned if this collection exists with the specified name of the current user and in the same session.
`p_query`	Query to execute to populate the members of the collection.
`p_generate_md5`	Valid values include `YES` and `NO`. `YES` to specify if the message digest of the data of the collection member should be computed. Use this parameter to compare the MD5 of the collection member with another member or to see if that member has changed.
`p_truncate_if_exists`	If `YES`, then members of the collection are first truncated if the collection exists and no error occurs. If `NO` (or not `YES`), and the collection exists, an error occurs.

Example

The following example creates a collection named AUTO and populates it with data from the AUTOS table. Because p_generate_md5 is YES, the MD5 checksum is computed to enable comparisons to determine change status.

BEGIN
    l_query := 'select make, model, year from AUTOS';
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
        p_collection_name => 'AUTO', 
        p_query => l_query,
        p_generate_md5 => 'YES');
END;