IRs with Subscriptions that might not work

If you have an Interactive Report with the Subscription feature enabled, users can “subscribe” to the report, getting a daily email with the results of the report. Unfortunately, however, this feature doesn’t work as expected if it relies on session state – e.g. if the query uses bind variables based on page items to filter the records. In this case, the subscription will run the query with a default (null) session state – APEX doesn’t remember what the page item values were when the user subscribed to the report.

The job that runs subscriptions, first sets the APEX globals (e.g. app ID, page ID) and evaluates any relevant authorizations (to check that the user still has access to the app, page and region); apart from that, only the query for the region is actually executed. Page processes are not executed, and page items with default values or expressions are not set and will be considered null if referred to by the query. In some cases you may be able to workaround this by using NVL or COALESCE in your query to handle null page items.

This is a query I used to quickly pick out all the Interactive Reports that have the Subscription feature enabled but which might rely on session state to work – i.e. it relies on items submitted from the page, refers to a bind variable or to a system context:

select workspace, application_id, application_name,
page_id, region_name, page_items_to_submit
from apex_application_page_ir
where show_notify = 'Yes'
and (page_items_to_submit is not null
     or regexp_like(sql_query,':[A-Z]','i')
     or regexp_like(sql_query,'SYS_CONTEXT','i')
);

For these reports, I reviewed them and where appropriate, turned off the Subscription feature. Note that this query is not perfect and might give some false positives and negatives.

Related idea for consideration: https://apex.oracle.com/ideas/FR-2393 – “Set up session for email subscription”

UPDATE (Mar 2022)

As of APEX 20.1, when a subscription is saved, the session is cloned, and the clone is used for all subsequent daily emails of the report. What this effectively means it that a snapshot of the value of each item in the application is taken (i.e. the value as stored in session state on the database) and this snapshot will provide values for any items used by the report.

In addition, the Database Session Initialization PL/SQL Code (under Security Attributes) is also executed, which is how you can initialise any application contexts that the report might need (e.g. for VPD).

Note that if the report relied on any APEX collections, these are not included in the clone; but reports that are based on collections should not generally be enabled for email subscriptions anyway.