CREATE_COLLECTION_FROM_QUERY Procedure

7.30 CREATE_COLLECTION_FROM_QUERY Procedure

Use this procedure to create 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 populates 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',
    p_truncate_if_exists IN VARCHAR2 default 'NO');

Parameters

Table 7-9 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 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.
`p_truncate_if_exists`	If `YES`, then members of the collection will first be truncated if the collection exists and no error will be raised. If `NO` (or not `YES`), and the collection exists, an error will be raised.

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 is 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;