#orclAPEX 20.1 finally introduces the APEX_IG package

TLDR: play with it here

The long desired APEX_IG package for programically manipulating the Interactive Grid is finally here.

While not feature packed, it brings some vital functionality users have been asking for for a long time, mainly the adding and removing of filters.

The most interesting procedure is add_filter

procedure add_filter
    ( p_page_id             in number
    , p_region_id           in number
    , p_filter_value        in varchar2
    , p_column_name         in varchar2 default null
    , p_operator_abbr       in varchar2 default null
    , p_is_case_sensitive   boolean     default false
    , p_report_id           in number   default null
    );

Let’s see how to use it

p_page_id is easy to get.
For p_region_id we might have to do something like this:

select region_id   
  from apex_application_page_regions
 where application_id = :APP_ID
   and page_id        = :APP_PAGE_ID
   and static_id      = 'emp';

p_filter_value is the actual value for our filter.
p_column_name is the name of the column we wish filter on. Note that if we leave this null, the filter will apply to the whole row.
p_operator_abbr represents the filter operation. Note that in case of a row filter, the operation will always be “contains” and this parameter will be ignored. The following options are available:

Abbriviation	Signification
EQ	Equals
NEQ	Not Equals
LT	Less Than
LTE	Less than or equal to
GT	Greater Than
GTE	Greater than or equal to
LIKE	SQL Like operator
NLIKE	Not Like
N	Null
NN	Not Null
C	Contains
NC	Not Contains
IN	SQL In Operator
NIN	SQL Not In Operator

p_is_case_sensitive, a boolean, determines if the filter should be case sensitive. Note this is only valid for row based filters.
p_report_id is the ID of the Interactive Grid report we wish to target. We can query the apex_appl_page_ig_rpts view to find it out. More often than not however, we will only want to affect the most recently viewed (or the current) report, in which case we can leave this attribute null.

Our call will end up looking something like this:

declare
    l_region_id number;
begin
    select region_id
      into l_region_id
      from apex_application_page_regions
     where application_id = :APP_ID
       and page_id        = :APP_PAGE_ID
       and static_id      = 'emp';

    apex_ig.add_filter
        ( p_page_id           => :APP_PAGE_ID
        , p_region_id         => l_region_id
        , p_filter_value      => 'PRESIDENT'
        , p_column_name       => 'JOB'
        , p_operator_abbr     => 'EQ'
        , p_report_id         => null
        );
end;

Where does this code go?

You will probably want to call this procedure in the Pre Rendering or Processing section of your page, depending on your business requirement.

Calling this function from an AJAX context (Execute PL/SQL Dynamic Action) also works, but as of now, even if you refresh the Interactive Grid after, although the data will be filtered, the filter will not appear in the header section of the Grid. A page refresh will be needed to achieve visual consistency.

What else is in there?

Others procedures include:

• The self explanatory get_last_viewed_report_id
• validate_report_id will validate whether the report_id belongs to a specific region
• reset_report will reset all changes of the report, be it hidden/rearranged columns, filters, sorting, etc and bring it back to its initial state.
• clear_report will remove all filters. Currently there is no way to target a specific filter.
• delete_report deletes a non-primary saved report.
• change_report_owner can be used to change the owner of a non-primary saved report.