This content has been marked as final. Show 6 replies
I can report the same problem. We're on 4.0.2.
Sanjay / Toby,
Does your Interactive report query use item value in the where clause by any chance? The reason I ask is because if Interactive report result depends on a APEX item value for the session, the subscription will not send any result as it runs in the background and not within APEX environment.
As a matter of fact, it does. Thanks, Christina. I got clued into this yesterday by one of my coworkers. So is there a way around this, or does the report need to be redesigned in order to take advantage of the subscription mechanism? Is there any way to "set state" for a subscribed report?
To make subscription return results, the Interactive report query needs to be re-written not to use item. There is no way to set the item session state for subscription to pick up the value.
i have a smilar problem with Email subscriptions, i don't use items, but i use collections in the where clause!
select * from PUMA_MMRDB_GSM_LB a
when (select C001 from APEX_COLLECTIONS where COLLECTION_NAME = 'TMP_SELECTION') = 'BRD' then 1
when (select C001 from APEX_COLLECTIONS where COLLECTION_NAME = 'TMP_SELECTION') = 'REGION' and (a.Region in (select C001 FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'REGION_M')) then 1
when (select C001 from APEX_COLLECTIONS where COLLECTION_NAME = 'TMP_SELECTION') = 'NL' and (a.NL in (select C001 FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'NLK')) then 1
end = 1
Sometimes i see the Errror ORA-01722 in the 'error message' field of the apex_application_page_ir_sub view. I don't know if this related to the above.
Can you point me in the right direction?
The collections are session based as well. Interactive report subscription does not work on session based values as it runs in the background outside of APEX. The query needs reference table or view with where clause that does not depend on collection for subscription to work.