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.
Jonathan
15 March 2019 - 2:34 am
Hello Jeffrey, do you know if there is any solution or work around for this issue?
Jeffrey Kemp
15 March 2019 - 8:12 am
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.
Valerie
26 May 2020 - 10:06 pm
Hi Jeremy,
How about query with :app_user, is there a way we can pass it to subscription?
Thanks
Jeffrey Kemp
27 May 2020 - 8:20 am
Hi Valerie,
Session information including :app_user, :app_id, and :app_page_id are automatically set when running the query for a subscription, so you don’t need to do anything except include them in your query. :app_session, however, will be null.
Jeff