Use this procedure to create a collection from a supplied query using bulk operations. This method offers significantly faster performance than the CREATE_COLLECTION_FROM_QUERY_2 method. The query is parsed as the application owner. 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. It is identical to the CREATE_COLLECTION_FROM_QUERY_B, however, the first five columns of the SELECT clause must be numeric and the next five columns must be date. After the date columns, there can be up to 50 character columns in the SELECT clause

This procedure uses bulk dynamic SQL to perform the fetch and insert operations into the named collection. Two limitations are imposed by this procedure:

  1. The MD5 checksum for the member data is not computed.

  2. No column value in query p_query can exceed 2,000 bytes. If a row is encountered that has a column value of more than 2,000 bytes, an error is raised during execution. In Oracle Database 11g Release 2 ( or later, this column limit is 4,000 bytes.


    p_collection_name    IN VARCHAR2,
    p_query              IN VARCHAR2,
    p_names IN apex_application_global.vc_arr2,
    p_values IN apex_application_global.vc_arr2,
    p_max_row_count      IN NUMBER default null,
    p_truncate_if_exists IN VARCHAR2 default 'NO');


Table 13-13 CREATE_COLLECTION_FROM_QUERYB2 Procedure Parameters

Parameter Description


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.


Query to execute to populate the members of the collection.


Array of bind variable names used in the query statement.


Array of bind variable values used in the bind variables in the query statement.


Maximum number of rows returned from the query in p_query which should be added to the collection.


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.


The following example shows how to use the CREATE_COLLECTION_FROM_QUERYB2 procedure to create a collection named EMPLOYEES and populate it with data from the EMP table. The first five columns (mgr, sal, comm, deptno, and null) are all numeric and the next five are all date.

 l_query varchar2(4000);
 l_query := 'select empno, sal, comm, deptno, null, hiredate, null,
null, null, null, ename, job, mgr from emp where deptno = :b1';
        p_collection_name => 'EMPLOYEES', 
        p_query => l_query,
        p_names => apex_util.string_to_table('b1'),
         p_values => apex_util.string_to_table('10'));