How to locate all interactive reports in my application?

Today my client decided to remove the email functionality from all interactive reports in their APEX application.

I could step through all the pages of my application and check whether there are any interactive reports used. This very manual approach can take some time and I can make mistakes by overlooking some of the interactive reports to modify.

A different approach would be to use the (officially supported) data dictionary views in APEX. Using this simple sql query I can find all interactive reports in all pages of my application (having the id 20120618):

select   workspace
       , application_id
       , application_name
       , page_id
       , region_name
       , download_formats
  from apex_application_page_ir
 where application_id=20120618
 order by page_id;

This will show all interactive reports which are used in my application:

We can even look for all interactive reports that need to be fixed by specifically looking for all reports where the download format email is enabled:

select   workspace
       , application_id
       , application_name
       , page_id
       , region_name
       , download_formats
  from apex_application_page_ir
 where application_id=20120618
   and instr(download_formats, 'EMAIL') > 0
 order by page_id;

These data dictionary views contain a wealth of information about your application. This is a real strength of Oracle Application Express, due to its nature being driven by a metadata repository.

Thus from a quality management perspective you could use the following query to make sure, all interactive reports are configured the same way, i.e. having flashback disabled, using the same display position for pagination (Top and Bottom-Left) and showing the dropdown list for selecting the rows per page:

select   workspace
       , application_id
       , application_name
       , page_id
       , region_name
       , pagination_display_position
       , show_rows_per_page
       , show_flashback
  from apex_application_page_ir
 where application_id=20120618
   and (   pagination_display_position <> 'Top and Bottom - Left'
        or show_rows_per_page = 'No'
        or show_flashback = 'Yes' )
 order by page_id;

Here is the result:

But how do we find the appropriate views? The APEX data dictionary contains information about all available data dictionary views and all of their columns.

Typicall I use the following query to find all information about interactive reports, all related views contain _IR_ in the view name:

select *
  from apex_dictionary
 where apex_view_name like '%IR%'

This will show us all view including their respective columns:

If we want to display the view only without their columns we can add the filter for the column_id 0, in there the APEX team added the overall description of the view itself:

select *
  from apex_dictionary
 where apex_view_name like '%IR%'
   and column_id=0; 

Now, we get only the view names including their description:

Regards,
~Dietmar.