APEX_COLLECTION Using CREATE_COLLECTION_FROM_QUERY_B and CREATE_COLLECTION_FROM_QUERY_B2

October 1, 2021Posted inAPEX

Referred from Oracle Standard Document .

CREATE_COLLECTION_FROM_QUERY_B Procedure

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

  • The MD5 checksum for the member data is not computed.
  • 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 (11.2.0.1) or later, this column limit is 4,000 bytes.
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B (
    p_collection_name IN VARCHAR2,
    p_query IN VARCHAR2.
    p_names IN apex_application_global.vc_arr2 DEFAULT,
    p_values IN apex_applicatioN_globa.vc_arr2 DEFAULT,
    p_max_row_count IN NUMBER DEFAULT);

CREATE_COLLECTION_FROM_QUERYB2 Procedure

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.

  • The MD5 checksum for the member data is not computed.
  • 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 (11.2.0.1) or later, this column limit is 4,000 bytes.
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERYB2 (
    p_collection_name IN VARCHAR2,
    p_query IN VARCHAR2.
    p_names IN apex_application_global.vc_arr2 DEFAULT,
    p_values IN apex_applicatioN_globa.vc_arr2 DEFAULT,
    p_max_row_count IN NUMBER DEFAULT);
Tags: