How to delete an Interactive Report subscription

Recently one of our testers asked me to remove an Interactive Report subscription. He still received an email every week from an Interactive Report in one of our applications. But he didn’t know if it was the Development, Test or Acceptance environment. In the first deployment of the application the option to subscribe to the Interactive Report was enabled, but later in the development process this option was disabled. So he couldn’t remove the subscription himself.

To help him out to stop receiving the emails, my first task was to identify the environment. I could use the APEX_APPLICATION_PAGE_IR_SUB view to this end. Querying it in consecutively Development and Test, I quickly established it was the Test environment, because there it returned a row containing the email address of our tester.

So now the next step: how do I remove the subscription? I know there is an APEX_IR package, so looking at the documentation I quickly found the DELETE_SUBSCRIPTION procedure. It has one input parameter, p_subscription_id.

But how can I find the subscription ID? In the APEX_APPLICATION_PAGE_IR_SUB view there are several IDs, an INTERACTIVE_REPORT_ID, a REPORT_ID and a NOTIFY_ID. From the comments on the view we can see it is the NOTIFY_ID (“ID of this subscription”). So now we can determine the parameter value:

select notify_id from apex_application_page_ir_sub;

NOTIFY_ID
----------
69955829892038312

And then execute the APEX_IR.DELETE_SUBSCRIPTION procedure under the workspace schema:

begin
  apex_ir.delete_subscription(p_subscription_id => 69955829892038312);
end;

But to my surprise this did not result in removal of the subscription.

After some thought and further exploring the internet I concluded that I had to do this in an APEX session. First I thought that it had to be a session of the subscribed user, so that I had to use this code:

apex_session.attach (p_app_id       => 120,            -- Application ID
                     p_page_id      => 10,             -- Page ID
                     p_session_id   => 114821746917893 -- Session ID
                    );

with the Session ID being a valid session id of the subscribed user. But later I found out that any APEX session will do, so also the following code can be used:

apex_session.create_session (p_app_id   => 120,    -- Application ID
                             p_page_id  => 10,     -- Page ID
                             p_username => 'DUMMY' -- Any username can be put in here, does not have to be a real user
                            );

After running this we can execute the delete procedure.

Because of my false assumption that it had to be an APEX session of the subscribed user, and because unfortunately our tester was not available at that time to start an APEX session, I started looking for another way to remove the subscription.

After some further searching the internet, I found out that there is also another procedure, APEX_INSTANCE_ADMIN.REMOVE_SUBSCRIPTION. This needs ADMIN rights to execute, but it doesn’t need attaching to an APEX session. So under the ADMIN user, I executed

begin
  apex_instance_admin.remove_subscription(p_subscription_id => 69955829892038312);
end;

This finally resulted in removal of the subscription.

One thing I’ve learned from this whole exercise: it’s a good idea to remove all active subscriptions when disabling the subscription function of an interactive report. This can be done using the following code:

begin
  for r_sub in ( select notify_id
                 from   apex_application_page_ir_sub
                 where  application_id = 120 -- application_id of the IR
                 and    page_id = 10         -- page_id of the IR, assuming one IR on the page
               )
  loop
    apex_instance_admin.remove_subscription(p_subscription_id => r_sub.notify_id);
  end loop;
end;