Apex - Collections - Max Row Count

Search For

The apex_collection.create_collection_from_query_b procedure has a p_max_row_count parameter (in newer Apex versions anyway).
This can come in handy if you want to limit the number of rows that are put into the collection. If you're dealing with very large tables this can cause performance issues.

It does have a drawback however, which I ran into recently when I wanted to raise an exception (to tell the user to apply more filters) whenever more than X rows were queried.
The p_max_row_count parameter works fine for that, but I also needed an "order by" in the query to sort the result set. For large result sets, this slows things down quite a bit.
You could use apex_collection.sort_members for that (and remove the "order by" from the query), but that procedure turned out to be quite slow and also it is limited to 1 column.

The resulting code looks something like the example below.
# 1 : Query with "order by" and using p_max_row_count. Slow because the query sorts ALL rows before create_collection_from_query_b gets the subset of l_max_row_count rows.
# 2 : Query with a rownum limit and "order by", and NOT using p_max_row_count. Faster because the query only gets the subset of l_max_row_count rows and then sorts only that.

declare

  l_sql           varchar2(4000);
  l_col           varchar2(100) := 'TEST_COL';
  l_count         number;
  l_max_row_count number := 1000;

begin

  -- # 1

  l_sql := 'select id
            from   table
            where  column = 123
            order by id';

  apex_collection.create_collection_from_query_b(p_collection_name    => l_col
                                                ,p_query              => l_sql
                                                ,p_max_row_count      => l_max_row_count + 1 -- get 1 more row than l_max_row_count, so we can check later if we exceeded the limit
                                                ,p_truncate_if_exists => 'YES');

  if l_max_row_count is not null then
    l_count := apex_collection.collection_member_count(p_collection_name => l_col);
    if l_count > l_max_row_count then
      raise_application_error(-20000, 'The maximum row count for this query is ' || l_max_row_count || '. Please apply a filter to reduce the number of records.');
    end if;
  end if;

  -- # 2

  l_sql := 'select id
            from
            ( select id
              from   table
              where  column = 123
              and    rownum <= ' || to_char(l_max_row_count + 1) || '
            )
            order by id';

  apex_collection.create_collection_from_query_b(p_collection_name    => l_col
                                                ,p_query              => l_sql
                                                ,p_truncate_if_exists => 'YES');

  if l_max_row_count is not null then
    l_count := apex_collection.collection_member_count(p_collection_name => l_col);
    if l_count > l_max_row_count then
      raise_application_error(-20000, 'The maximum row count for this query is ' || l_max_row_count || '. Please apply a filter to reduce the number of records.');
    end if;
  end if;

end;