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 session state – APEX doesn’t remember what the page item values were when the user subscribed to the report.

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.

Quick Pick in APEX Report
A nice Descending Index Range Scan

Comments

  1. Hello Jeffrey, do you know if there is any solution or work around for this issue?

    • Hi Jonathan, since APEX doesn’t store and reload the session state when running the query for the subscription there is no workaround for this particular issue. One approach might be to eschew all references to bind variables and session context variables, and instead add IR filters – the definitions of these filters should be stored with the subscription I think, although I haven’t tested this myself.

Leave a Reply

Your email address will not be published / Required fields are marked *