CREATE_COLLECTION_FROM_QUERY Procedure

Use this procedure to create a collection from a supplied query. The query will be 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 will 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 is raised.

Syntax

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
    p_collection_name IN VARCHAR2,
    p_query IN VARCHAR2,
    p_generate_md5 IN VARCHAR2 default 'NO');

Parameters

Table: CREATE_COLLECTION_FROM_QUERY Procedure Parameters describes the parameters available in the CREATE_COLLECTION_FROM_QUERY procedure.

CREATE_COLLECTION_FROM_QUERY Procedure 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 in order to populate the members of the collection. If p_query is numeric, it is assumed to be a DBMS_SQL cursor.
`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.

Example

The following example shows how to use the CREATE_COLLECTION_FROM_QUERY procedure to create a collection named AUTO and populate it with data from the AUTOS table. Because p_generate_md5 is 'YES', the MD5 checksum will be computed to allow 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;