Link to home
Start Free TrialLog in
Avatar of scpig
scpig

asked on

How to send_mail to multiple recipients

I have a scheduled job to run (I created a job chain).  I'd like to have the result automatically sent to multiple recipients after the job is finished .  It works with just send to one person, but I would like the email sent to multiple persons.  How do I do that?
These are the codes:

CREATE OR REPLACE PROCEDURE send_mail (
  p_mail_host  IN  VARCHAR2,
  p_from       IN  VARCHAR2,
  p_to         IN  VARCHAR2,
  p_subject    IN  VARCHAR2,
  p_message    IN  VARCHAR2)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_mail_host, 25);
  UTL_SMTP.helo(l_mail_conn, p_mail_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);

  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, p_message || Chr(13));

  UTL_SMTP.close_data(l_mail_conn);
  UTL_SMTP.quit(l_mail_conn);
END send_mail;
/

CREATE OR REPLACE PROCEDURE email_alert AS
  l_mail_host  VARCHAR2(50) := 'ipaddress';
  l_from       VARCHAR2(50) := userid.com';
  l_to           VARCHAR2(50) := userid.com';  
  v_status     VARCHAR2(50);
BEGIN
 
     
  SELECT status INTO v_status FROM user_scheduler_job_log
  WHERE log_date=(SELECT MAX(log_date) FROM user_scheduler_job_log
                  WHERE JOB_NAME='TEST' AND  owner='MY');
 
IF v_status = 'SUCCEEDED'
THEN
  send_mail(p_mail_host => l_mail_host,
            p_from      => l_from,
            p_to        => l_to,
            p_subject   => 'EMAIL_ALERT: Successful.',
            p_message   => 'EMAIL_ALERT: completed successfully!');
ELSE
  send_mail(p_mail_host => l_mail_host,
              p_from      => l_from,
              p_to        => l_to,
              p_subject   => 'EMAIL_ALERT: Error',
              p_message   => 'EMAIL_ALERT: Failed With The Following
  Error:' || SQLERRM);
END IF;
END email_alert;
/
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of scpig
scpig

ASKER

Where do I define the recipients for p_reipients?
since they are just strings you could declare them as dbms_sql.varchar2s in your email alert procedure and corresponding parameter in your send_mail (instead of the "to")


then simply assign them like you do a normal string

v_recipient(1) := 'somebody@someaddress.com';
v_recipient(2) := 'somebodyelse@someaddress.com';
v_recipient(3) := 'yetanotherperson@someaddress.com';

where those addresses come from, I don't know, it's not particularly important.

in your example code it looks like you currently have it hardcoded, you could have them in a table, or a file, or derived from a webserice, or any other place that would be appropriate for your system