How to Filter and Reset Interactive Reports and Grids from PL/SQL Using APEX_IR and APEX_IG

Interactive reports and interactive grids in Oracle APEX keep each user's settings, such as filters, sort order, columns, and highlights, in reports: the developer's saved primary and alternative reports, users' private and public reports, and for each session a working copy of the report being viewed, holding changes not yet saved. APEX_IR and APEX_IG change those reports from PL/SQL. The typical use is a page process that filters a report by a value the user picked elsewhere, such as a status chosen on a dashboard.

This guide covers both packages with tested examples and their real output, including two behaviors in APEX 26.1 that contradict the documentation.

Quick Reference

How Regions and Reports Are Identified

Both packages identify the region by page and region ID, or by the region's static ID, and the report by ID, alias or name, or static ID. Without a report, they work on the report the user viewed last. Their changes go into the session's working copy, which the views APEX_APPLICATION_PAGE_IR_RPT and APEX_APPL_PAGE_IG_RPTS show with the SESSION_ID of the session. The JavaScript side of the same regions is covered in the guides to selecting rows and paging through reports and controlling an interactive grid with JavaScript.

How to Run These Examples

The examples ran in Oracle APEX 26.1 against a test application with ID 200: page 8 has an orders interactive report with the static ID orders, and page 9 a stores interactive grid with the static ID stores. Each example needs an APEX session of application 200 on that page, created with APEX_SESSION.CREATE_SESSION as shown in the guide to creating APEX sessions and managing session state from PL/SQL. Run them as your workspace schema with server output switched on. The output under each example is exactly what the database printed.

Interactive Reports: APEX_IR

ADD_FILTER

Adds a filter on a column to the report. p_operator_abbr is one of EQ, NEQ, LT, LTE, GT, GTE, LIKE, NLIKE, N (null), NN (not null), C (contains), NC, IN, and NIN; for IN and NIN, p_filter_value is a comma-separated list.

Syntax:

apex_ir.add_filter(p_page_id in number, p_region_static_id in varchar2, p_report_column in varchar2,
    p_filter_value in varchar2, p_operator_abbr in varchar2 default null, p_report_static_id in varchar2 default null)
apex_ir.add_filter(p_page_id in number, p_region_id in number, p_report_column in varchar2, p_filter_value in varchar2,
    p_operator_abbr in varchar2 default null, p_report_id in number default null | p_report_alias in varchar2 default null)

The documentation says to enclose an IN list in backslashes, as in \Online,Phone\. In 26.1 the backslashes end up inside the first and last values, so leave them out, as the example below does.

RESET_REPORT, CLEAR_REPORT, and GET_LAST_VIEWED_REPORT_ID

RESET_REPORT discards the working copy, returning the report to how it was saved. CLEAR_REPORT removes filters, highlights, breaks, aggregates, and charts but keeps the columns and sort order. GET_LAST_VIEWED_REPORT_ID returns the ID of the saved report the user viewed last in the region, not the working copy. GET_REPORT, which returns the report's query and bind values as a t_report with sql_query and binds, is deprecated but still handy for seeing what a report actually runs.

Syntax:

apex_ir.reset_report | clear_report(p_page_id in number, p_region_static_id in varchar2,
    p_report_static_id in varchar2 default null)
apex_ir.reset_report | clear_report(p_page_id in number, p_region_id in number,
    p_report_id in number default null | p_report_alias in varchar2 default null)
apex_ir.get_last_viewed_report_id(p_page_id in number, p_region_id in number) return number

This example needs a session of application 200, page 8. It plays the role of a page process that runs before the orders report renders.

Example:

declare
    l_report apex_ir.t_report;
    procedure show(p_label varchar2) is
        l_conds varchar2(4000);
    begin
        -- the session's copy of the report ("working report") holds the user's changes
        select listagg(c.condition_type || ' ' || c.condition_column_name || ' ' || c.condition_operator
                       || ' ' || c.condition_expression, '; ') within group (order by c.condition_type, c.condition_column_name)
          into l_conds
          from apex_application_page_ir_cond c
          join apex_application_page_ir_rpt r on r.report_id = c.report_id
         where r.application_id = 200 and r.page_id = 8 and r.session_id = v('APP_SESSION');
        dbms_output.put_line(rpad(p_label, 9) || nvl(l_conds, '-'));
    end;
begin
    -- as a page process before the Orders report (page 8) renders
    apex_ir.add_filter(p_page_id => 8, p_region_static_id => 'orders',
                       p_report_column => 'STATUS', p_filter_value => 'Shipped', p_operator_abbr => 'EQ');
    apex_ir.add_filter(p_page_id => 8, p_region_static_id => 'orders',
                       p_report_column => 'CHANNEL', p_filter_value => 'Online,Phone', p_operator_abbr => 'IN');
    show('filters');
    dbms_output.put_line('last viewed report: ' || apex_ir.get_last_viewed_report_id(
                             p_page_id => 8, p_region_id => apex_region.get_id(p_page_id => 8, p_dom_static_id => 'orders')));

    -- the query the report runs now (GET_REPORT is deprecated)
    l_report := apex_ir.get_report(p_page_id => 8, p_region_id => apex_region.get_id(p_page_id => 8, p_dom_static_id => 'orders'));
    dbms_output.put_line(regexp_substr(l_report.sql_query, 'where .*', 1, 1, 'n'));
    for i in 1 .. l_report.binds.count loop
        dbms_output.put_line('  :' || l_report.binds(i).name || ' = ' || l_report.binds(i).value);
    end loop;

    apex_ir.reset_report(p_page_id => 8, p_region_static_id => 'orders');   -- back to the saved report
    show('reset');
    apex_ir.clear_report(p_page_id => 8, p_region_static_id => 'orders');   -- no filters, highlights, breaks
    show('cleared');
end;
/

Output:

filters  Filter CHANNEL in Online,Phone; Filter STATUS = Shipped; Highlight STATUS = Pending Approval
last viewed report: 77303834231394171
where 1=1

 and "STATUS"=:apex$f1

 and "CHANNEL"in (:apex$f2,:apex$f3)
order by "ORDER_DATE" desc nulls first
  :APEX$F1 = Shipped
  :APEX$F2 = Online
  :APEX$F3 = Phone
reset    -
cleared  -

Both filters landed in the working copy alongside the report's existing highlight, and GET_REPORT shows them turned into bind variables in the report's query, so user-supplied filter values are never concatenated into SQL. After RESET_REPORT, the working copy is gone. The usual pattern is a Before Header process that calls CLEAR_REPORT and then ADD_FILTER with a page item's value, so the report always opens filtered by what the user chose on the previous page. For more on the region itself, see the complete interactive report guide.

CLONE_REPORT, CHANGE_REPORT_OWNER, and DELETE_REPORT

CLONE_REPORT copies a saved report under a new name, as a private or public report of an owner, and returns its ID; p_replace_report true replaces an existing report of that name. CHANGE_REPORT_OWNER gives a report to another user, for example when an employee leaves, and DELETE_REPORT deletes one.

Syntax:

apex_ir.clone_report(p_report_id in number, p_new_name in varchar2, p_new_description in varchar2 default null,
    p_new_owner in varchar2 default null, p_new_is_public in boolean default null,
    p_replace_report in boolean default false) return number
apex_ir.change_report_owner(p_report_id in number, p_old_owner in varchar2, p_new_owner in varchar2)
apex_ir.delete_report(p_report_id in number)

This example needs a session of application 200, page 8.

Example:

declare
    l_primary number;
    l_new     number;
begin
    select report_id into l_primary from apex_application_page_ir_rpt
     where application_id = 200 and page_id = 8 and report_type = 'PRIMARY_DEFAULT';

    -- a copy of the primary report, saved as a private report of KIM.LEE
    l_new := apex_ir.clone_report(p_report_id => l_primary, p_new_name => 'My Orders',
                                  p_new_owner => 'KIM.LEE', p_new_is_public => false);
    apex_ir.change_report_owner(p_report_id => l_new, p_old_owner => 'KIM.LEE', p_new_owner => 'JO.PARK');

    for r in (select report_name, application_user, report_type, status from apex_application_page_ir_rpt
               where report_id = l_new) loop
        dbms_output.put_line(r.report_name || ' - ' || r.application_user || ' - ' || r.report_type || ' - ' || r.status);
    end loop;

    apex_ir.delete_report(p_report_id => l_new);
    select count(*) into l_new from apex_application_page_ir_rpt where application_id = 200 and report_name = 'My Orders';
    dbms_output.put_line('left after delete_report: ' || l_new);
end;
/

Output:

My Orders - JO.PARK - PRIVATE - PRIVATE
left after delete_report: 0

Cloning is a neat way to hand every new user a ready-made private report, created in the post-authentication procedure or an onboarding process.

Subscriptions, Export, and Import

Users can subscribe to a report and receive it by email on a schedule; the subscriptions are listed in APEX_APPLICATION_PAGE_IR_SUB. Saved reports can also be moved between applications or instances with a signed export, where the signature comes from a Web Credential of type Key Pair whose public key the importing side checks.

When a user leaves, a small cleanup script that reassigns their saved reports and updates or deletes their subscriptions keeps scheduled emails from bouncing. Web Credentials are covered in the guide to calling REST APIs with APEX_WEB_SERVICE and APEX_CREDENTIAL.

Interactive Grids: APEX_IG

APEX_IG offers the same for interactive grids, without cloning, subscriptions, and export.

ADD_FILTER

Adds a filter to the grid's report: on a column, p_column_name, with an operator (EQ, NEQ, LT, LTE, GT, GTE, N, NN, C, NC, IN, and NIN), or, without a column, a row filter that searches all columns, case-sensitively when p_is_case_sensitive is true. p_multi_value_separator separates the values of IN. The signature with p_report_name is deprecated.

Syntax:

apex_ig.add_filter(p_page_id in number, p_region_static_id in varchar2, p_filter_value in varchar2,
    p_column_name in varchar2 default null, p_operator_abbr in varchar2 default null,
    p_is_case_sensitive in boolean default false, p_report_static_id in varchar2 default null,
    p_multi_value_separator in varchar2 default null)
apex_ig.add_filter(p_page_id in number, p_region_id in number, p_filter_value in varchar2, ...,
    p_report_id in number default null, p_multi_value_separator in varchar2 default null)

Call it in a page submit process rather than while the page renders: a grid changes its reports through Ajax requests, so a filter added during rendering can differ from what a download of the grid uses. In 26.1 the static-ID signature also needs p_report_static_id, and adds nothing without it, while the region-ID signature uses the last viewed report when p_report_id is null.

RESET_REPORT, CLEAR_REPORT, GET_LAST_VIEWED_REPORT_ID, CHANGE_REPORT_OWNER, and DELETE_REPORT

These work as in APEX_IR: reset or clear the report by static ID, report ID, or name, return the last viewed report, and change the owner of or delete a saved report, with p_application_id defaulting to the current application.

Syntax:

apex_ig.reset_report | clear_report(p_page_id in number, p_region_static_id in varchar2, p_report_static_id in varchar2 default null)
apex_ig.reset_report | clear_report(p_page_id in number, p_region_id in number,
    p_report_id in number default null | p_report_name in varchar2 default null)
apex_ig.get_last_viewed_report_id(p_page_id in number, p_region_id in number) return number
apex_ig.change_report_owner(p_application_id in number default {current}, p_report_id in number,
    p_old_owner in varchar2, p_new_owner in varchar2)
apex_ig.delete_report(p_application_id in number default {current}, p_report_id in number)

This example needs a session of application 200, page 9.

Example:

declare
    procedure show(p_label varchar2) is
        l_filters varchar2(4000);
    begin
        select listagg(f.type || ' ' || (select c.name from apex_appl_page_ig_columns c where c.column_id = f.column_id)
                       || ' ' || f.operator || ' ' || f.expression, '; ') within group (order by f.filter_id)
          into l_filters
          from apex_appl_page_ig_rpt_filters f
          join apex_appl_page_ig_rpts r on r.report_id = f.report_id
         where r.application_id = 200 and r.page_id = 9 and r.session_id = v('APP_SESSION');
        dbms_output.put_line(rpad(p_label, 9) || nvl(l_filters, '-'));
    end;
begin
    -- the Stores grid of page 9; in a page submit process, not while the page renders
    apex_ig.add_filter(p_page_id => 9, p_region_static_id => 'stores', p_report_static_id => 'primary',
                       p_column_name => 'STATE', p_operator_abbr => 'EQ', p_filter_value => 'CO');
    -- by region ID: without a report ID, the last viewed report
    apex_ig.add_filter(p_page_id => 9, p_region_id => apex_region.get_id(p_page_id => 9, p_dom_static_id => 'stores'),
                       p_filter_value => 'Denver', p_report_id => null);            -- no column: a row search
    show('filters');
    dbms_output.put_line('last viewed report: ' || apex_ig.get_last_viewed_report_id(
                             p_page_id => 9, p_region_id => apex_region.get_id(p_page_id => 9, p_dom_static_id => 'stores')));

    apex_ig.reset_report(p_page_id => 9, p_region_static_id => 'stores', p_report_static_id => 'primary');
    show('reset');
end;
/

Output:

filters  Column STATE EQ CO; Row   Denver
last viewed report: 120349923938833876
reset    -

The first call is a column filter and the second, with no column, a row search across all columns. Note that the static-ID call passes p_report_static_id explicitly; without it, it would have silently added nothing in 26.1. The grid itself is covered in the complete interactive grid guide.

Conclusion

APEX_IR and APEX_IG change the reports of interactive reports and grids in the user's session: add column or row filters, reset to the saved report or clear the settings, and find the last viewed report. Filters become bind variables in the report's query, so they are safe to fill from page items. APEX_IR also clones, reassigns, and deletes saved reports, manages subscriptions, and exports and imports saved reports with a signature, while APEX_IG reassigns and deletes saved grid reports. In 26.1, leave the backslashes off APEX_IR IN lists, and pass p_report_static_id to APEX_IG's static-ID ADD_FILTER.