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.
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
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
Brian Penrose
19 March 2022 - 1:45 am
Sorry, I know this is a bit old, but just to clarify…even if I have default values set for page items, the subscription will still not use these? For example, I have transaction from/to dates, and these default to SYSDATE-1 and SYSDATE. If I subscribe to the report today and schedule it to run once a day, it will always use today’s date (not pick up SYSDATE for each subsequent day)?
Jeffrey Kemp
19 March 2022 - 9:20 am
Hi Brian,
That’s an excellent question, thanks. I’ve updated the post accordingly after I did a bit of research.
If you have default values for page items, you may need to repeat those values (or expressions) in your query so that it will use those same values when the query is run for a subscription.
Jeff
Anonymous
19 March 2022 - 10:55 pm
Thanks for the quick reply, Jeff (and for researching)…very much appreciated! I am in he process of adding NVL functions to my report queries now. Excellent idea to implement the new pre-rendering point, I hope to see that in a future release!
Thanks again,
Brian
Brian Penrose
21 March 2022 - 6:38 am
After investigating further, it appears the report subscription does use the current session state at the time in which the subscription is initiated. However, it appears to store/maintain these values (via SESSION_ID) for each subsequent report execution (via that subscription). So, for my previous example, if I use transaction from/to dates of 3/1/2022 and 3/2/2022, it will maintain and use these same dates each time it generates the report (rather than defaulting to NULL dates). So, unfortunately, incorporating NVL or COALESCE function into my report queries will have no effect.
Jeffrey Kemp
23 March 2022 - 10:01 am
Hi Brian,
Thank you for testing that and finding that my knowledge was out of date! Unfortunately my testing was done on an old 18.1 instance and I wasn’t aware of some changes that had since been made to APEX.
I’ve updated the article accordingly.
Thanks again,
Jeff