Find application that triggers mail

Mencis Joe

Hi all. Is it possible to find which application is sending out the email to the user with just his email address? One of the application is sending out mail to user which he no longer needs. I want to remove his mail address from the recipients list. All I have is his email address. How do I find out which application sends out the mail and then stop sending the mail to the user?

Thanks

fac586

Mencis Joe wrote:

Hi. I tried your suggestion. It was of great help. I figured out where the mail is coming from. The unsubscribe link in the mail leads to a URL. When I click on that URL it shows error as in the following image.

The URL is http://xxxxxxxxxxxxxx:8080/apex/f?p=4155:200:0:::200:P200_ID:5282728150784751&p_lang=en

When I checked for the application 4155 in internal workspace the result is as the below image. It shows the INTERNAL workspace.

How should I stop this mail from being sent? Thanks.

If the application has been upgraded from a previous version of APEX there may be a zombie subscription persisting in the old instance:

Otherwise, if you now know which application is responsible, can't the user go and unsubscribe from the original report?

fac586

Mencis Joe wrote:

Hi all. Is it possible to find which application is sending out the email to the user with just his email address? One of the application is sending out mail to user which he no longer needs. I want to remove his mail address from the recipients list. All I have is his email address. How do I find out which application sends out the mail and then stop sending the mail to the user? 

Why do you only have the email address? What about the subject and content of the mail received? APEX can send mail from various sources: interactive report subscriptions; Send E-Mail processes; custom PL/SQL processes etc. Having some idea of the context in which the mail is sent would enable you to at least narrow down which particular haystack to comb for the needle using search/utilities/APEX views.

There's no application-level information recorded in the public APEX_MAIL_QUEUE and APEX_MAIL_LOG views, but the latter could be used to establish a pattern of when the email is being sent.

Mencis Joe

Hi thanks for the reply. I also have the subject of the mail now. The mail is being sent from interactive report subscription. But still not able to find out the application. Is there a way to do it?

fac586

Mencis Joe wrote:

Hi thanks for the reply. I also have the subject of the mail now. The mail is being sent from interactive report subscription. But still not able to find out the application. Is there a way to do it?

Go to Workspace Utilities > Application Express Views, select the APEX_APPLICATION_PAGE_IR_SUB view, and run a query on the view using the subject information you now have to filter on the EMAIL_SUBJECT column.

Mencis Joe

Hi. I tried your suggestion. It was of great help. I figured out where the mail is coming from. The unsubscribe link in the mail leads to a URL. When I click on that URL it shows error as in the following image.

The URL is http://xxxxxxxxxxxxxx:8080/apex/f?p=4155:200:0:::200:P200_ID:5282728150784751&p_lang=en

When I checked for the application 4155 in internal workspace the result is as the below image. It shows the INTERNAL workspace.

How should I stop this mail from being sent? Thanks.

fac586

Mencis Joe wrote:

Hi. I tried your suggestion. It was of great help. I figured out where the mail is coming from. The unsubscribe link in the mail leads to a URL. When I click on that URL it shows error as in the following image.

The URL is http://xxxxxxxxxxxxxx:8080/apex/f?p=4155:200:0:::200:P200_ID:5282728150784751&p_lang=en

When I checked for the application 4155 in internal workspace the result is as the below image. It shows the INTERNAL workspace.

How should I stop this mail from being sent? Thanks.

If the application has been upgraded from a previous version of APEX there may be a zombie subscription persisting in the old instance:

Otherwise, if you now know which application is responsible, can't the user go and unsubscribe from the original report?

Mencis Joe

That is the problem. I do not know from which application the mail is getting triggered. I have gone through the link. Where do I try the suggested queries? Is it through APEX (If so, what workspace? Internal??)  Or through SQLPLUS (directly connected to the database)?

Mencis Joe

Please ignore my previous message. I have found the suspicious data. Should I delete it to stop the mail? The data is in WWV_FLOW_WORKSHEET_NOTIFY table. Thanks.

fac586

Mencis Joe wrote:

Please ignore my previous message. I have found the suspicious data. Should I delete it to stop the mail? The data is in  WWV_FLOW_WORKSHEET_NOTIFY table. Thanks.

That is only relevant if your circumstances are the same as that described in the other thread, with the email originating from an application in a superseded APEX instance. If that scenario does not apply, do not run DML directly against the APEX base tables.

I do not know from which application the mail is getting triggered. 

But previously you said:

I figured out where the mail is coming from.

Which is it?

Mencis Joe

My case is same as in the thread. I am getting the same issue as mentioned by Tony Miller.