Forum Stats

  • 3,853,446 Users
  • 2,264,223 Discussions
  • 7,905,362 Comments

Discussions

Is there a way to find which part of my code is sending the email.

Hawk333
Hawk333 Member Posts: 126
edited Jan 11, 2016 12:42PM in SQL & PL/SQL

I have a problem as follows: Some email is being sent as a daily alert to recipient list. The sender is our development team email group. One of the recipients emails is no longer valid. Hence, every day, we receive some email back "Your message did not reach some or all of the intended recipients." because of the invalid recipient. Nobody knows where is the recipients list to remove that invalid email. The code was written years ago by some developer. But we need to remove that invalid email to stop receiving this daily email by everybody.

I tried to search throughout the code base, but the code is massive scattered over several schema and several servers.

My question, is there a way to detect where is this email being sent from (which part of the code, package, function, or procedure?)

Tagged:
Hawk333

Best Answer

  • Unknown
    edited Jan 10, 2016 9:03PM Answer ✓
    Hawk333 wrote:
    
    I have a problem as follows: Some email is being sent as a daily alert to recipient list. The sender is our development team email group. One of the recipients emails is no longer valid. Hence, every day, we receive some email back "Your message did not reach some or all of the intended recipients." because of the invalid recipient. Nobody knows where is the recipients list to remove that invalid email. The code was written years ago by some developer. But we need to remove that invalid email to stop receiving this daily email by everybody.
    
    I tried to search throughout the code base, but the code is massive scattered over several schema and several servers.
    My question, is there a way to detect where is this email being sent from (which part of the code, package, function, or procedure?)
    
    

    code sending email may not reside in the DB or part of basic application code.

    AFAIK, the best place to start sleuthing is by associating when the email is sent & correlating to what code is/was running at that time.

    AUDIT EXECUTE UTL_MAIL;

    ADUIT EXECUTE UTL_SMTP;

    Hawk333

Answers

  • Unknown
    edited Jan 10, 2016 9:03PM Answer ✓
    Hawk333 wrote:
    
    I have a problem as follows: Some email is being sent as a daily alert to recipient list. The sender is our development team email group. One of the recipients emails is no longer valid. Hence, every day, we receive some email back "Your message did not reach some or all of the intended recipients." because of the invalid recipient. Nobody knows where is the recipients list to remove that invalid email. The code was written years ago by some developer. But we need to remove that invalid email to stop receiving this daily email by everybody.
    
    I tried to search throughout the code base, but the code is massive scattered over several schema and several servers.
    My question, is there a way to detect where is this email being sent from (which part of the code, package, function, or procedure?)
    
    

    code sending email may not reside in the DB or part of basic application code.

    AFAIK, the best place to start sleuthing is by associating when the email is sent & correlating to what code is/was running at that time.

    AUDIT EXECUTE UTL_MAIL;

    ADUIT EXECUTE UTL_SMTP;

    Hawk333
  • Unknown
    edited Jan 10, 2016 9:30PM
    I have a problem as follows: Some email is being sent as a daily alert to recipient list. The sender is our development team email group. One of the recipients emails is no longer valid. Hence, every day, we receive some email back "Your message did not reach some or all of the intended recipients." because of the invalid recipient. Nobody knows where is the recipients list to remove that invalid email.
    

    Well you need to find that 'recipient list'.

    We don't have your tables or access to your system.

    We don't even know that it is YOUR system/systems that are sending that email. There may be a troll in a basement office whose sole job is to send that email from their twitter account.

    Have you checked the basement for trolls?

  • Hawk333
    Hawk333 Member Posts: 126
    edited Jan 10, 2016 9:37PM

    Well, few signs say it is from my code. The content, the title of the email being sent, the format is even similar to many other alerts. Nothing certain, but I think I need to make sure it not from my code first, before I head to the basement looking for trolls

  • Unknown
    edited Jan 10, 2016 9:53PM

    Consider in the future to make the recipient list resident in a Oracle table.

    Then as folks change no coding change would be required to properly maintain the list.

  • Unknown
    edited Jan 10, 2016 10:18PM
    Well, few signs say it is from my code. The content, the title of the email being sent, the format is even similar to many other alerts. Nothing certain, but I think I need to make sure it not from my code first, before I head to the basement looking for trolls 
    

    If those email address are REALLY hard-coded in your code, and not in a table where they belong, then just search the DBA_SOURCE view for one of those emails. That will tell you what code the email address is in and who owns that code.

    It seems more likely that the email addresses are coming from a table.

    Hawk333
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Jan 10, 2016 11:22PM

    It could be (I'd say likely) that the app is just sending to a a distribution list, like Outlook. If I had the OPs problem, I'd be searching for uses of utl_mail or utl_SMTP and work out from those occurances.

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy
    edited Jan 10, 2016 11:28PM

    You may run following query to find the code which is using UTL_MAIL or UTL_SMTP, which are used to send emails from database

    select * from all_source where upper(text) like '%UTL_MAIL%'
    union all
    select * from all_source where upper(text) like '%UTL_SMTP%'
    
    Hawk333
  • 3150692
    3150692 Member Posts: 54
    edited Jan 11, 2016 12:09AM

    Hi

    just also think about other areas the email can be sent

    It might be possible that email is not sent from PL/SQL code but might be sent from other languages like Shell Scripting/Perl/Java Code

    In Unix it will be mailx or sendmail

    Hence you might have to check the same

    garani

  • Unknown
    edited Jan 11, 2016 12:42PM

    Perhaps you missed this first statement:

    If those email address are REALLY hard-coded in your code,
    

    My hypothesis is still to blame it on the troll.

This discussion has been closed.