6.4.2.1 Getting Filtered Data Primary Keys

A helper function can return the primary keys of a filtered region's results.

The region_pks function below accepts the static ID of the filtered region as a parameter. It represents the source whose filtered results you want to present in an alternative way using another region. If ID is the primary key, then only the first parameter is required. If not, pass its name in the optional p_pk_column_name parameter. The function returns a pipelined list of numbers representing the primary keys of the indicated region's filtered results. It uses the OPEN_QUERY_CONTEXT() function in the APEX_REGION package to reference the source region's filtered results. Then it iterates over them with NEXT_ROW() and GET_NUMBER() in the APEX_EXEC package. The pipe row returns the primary key of each result.

create or replace function region_pks(
    p_region_static_id in varchar2,
    p_pk_column_name   in varchar2 default 'ID')
return apex_t_number
pipelined
is
    c_page_id constant number := V('APP_PAGE_ID');
    l_context          apex_exec.t_context;
begin
    l_context := apex_region.open_query_context(
                    p_page_id   => c_page_id,
                    p_region_id => apex_region.get_id(
                                     p_page_id       => c_page_id,
                                     p_dom_static_id => p_region_static_id));
    while apex_exec.next_row(l_context) loop
        pipe row (apex_exec.get_number(l_context, p_pk_column_name));
    end loop;
    apex_exec.close(l_context);
    return;
exception
    when no_data_needed then
        apex_exec.close(l_context);
        return;
end region_pks;