How to sent email in Oracle PL/SQL package to multiple receivers?

Question

How to sent email in Oracle PL/SQL package to multiple receivers? I have below pl/sql procedure within an oracle package, it works only for one receiver. I need to improve it functional to let it can send email to multiple receivers at same time like "To: David Festool; Peter Makita; John Dewalt". Any body can help me out will be great appreciate! Please provide me modified code.

---

procedure email(p_recip   in varchar2,
                p_subject in varchar2,
                p_message in varchar2) is

  c   utl_smtp.connection;
  msg varchar2(4000);

  procedure send_header(name in varchar2, header in varchar2) as
  begin
    utl_smtp.write_data(c, name || ': ' || header || utl_tcp.crlf);
  end;
begin
  --Open SMTP connection
  c := utl_smtp.open_connection('ExchangeServerName');

  -- Write SMTP header
  utl_smtp.helo(c, 'ExchangeServerName');
  utl_smtp.mail(c, '[email protected]');
  utl_smtp.rcpt(c, p_recip);
  utl_smtp.open_data(c);
  send_header('From', '"Title" <[email protected]');
  send_header('To', p_recip);
  send_header('Subject', p_subject);
  send_header('Mime-Version', '1.0');
  send_header('Content-Type', 'multipart/mixed; boundary="DMW.Boundary.605592468"');

  -- Write MIME boundary line for the message body
  msg := utl_tcp.crlf || '--DMW.Boundary.605592468' || utl_tcp.crlf ||
         'Content-Type: text/plain' || utl_tcp.crlf ||
         'Content-Transfer-Encoding: 7bit' || utl_tcp.crlf ||
         utl_tcp.crlf;
  utl_smtp.write_data(c, msg);

  -- Write message body
  utl_smtp.write_data(c, p_message || utl_tcp.crlf);

  -- Clean up
  utl_smtp.close_data(c);
  utl_smtp.quit(c);
exception
  when utl_smtp.transient_error or utl_smtp.permanent_error then
    begin
      utl_smtp.quit(c);
    exception
      when utl_smtp.transient_error or utl_smtp.permanent_error then
        null;
        -- When the SMTP server is down or unavailable, we don't have
      -- a connection to the server. The QUIT call will raise an
      -- exception that we can ignore.
    end;

    raise_application_error(-20000, 'Failed to send mail due to the following error: ' ||
                             sqlerrm);
end;
--------------------------------------------------------------

Solution 1

You need to call utl_smtp.rcpt multiple times, once for each recipient; you can't give a list of values in one call.

From the UTL_SMTP.RCPT documentation:

To send a message to multiple recipients, call this routine multiple times. Each invocation schedules delivery to a single e-mail address.

That means you can't really pass a string of names, unless you're happy to parse the individual addresses out; it would be easier to pass an array of values, probably.

The TO header is a separate issue; if I recall correctly, that is really just for display, and having an address as a rcpt but not in the TO (or CC) header is how BCC is implemented. Citation needed though...

Here's an old AskTom article demonstrating this. jonearles suggestion to use UTL_MAIL should be investigated though.

Solution 2

The format is:

UTL_MAIL.SEND (sender, recipientlist, cc, bcc, subject, Message, mime_type, priority)

The recipientlist, cc, and bcc parameters are all comma-separated lists of recipient, copy to, and blind copy e-mail addresses.

The sender, subject, message, and mime_type parameters are all single item fields.

Solution 3

Just run below procedure with change code:

v_Mail_Host VARCHAR2(50) := 'uacemail.rxcorp.com'; -- your host ip or name

Execute:

begin
prc_email_send( '[email protected]', -- Mail From
'[email protected]',---Recipient
'[email protected];[email protected]',-- Cc List
'This is mail subject ',
'This is mail body' );
end;
/

Procedure Code:

Create or replace procedure prc_email_send(
v_From      VARCHAR2,
v_Recipient VARCHAR2,
v_cc_list varchar2,
v_Subject   VARCHAR2,   
v_Mail_body VARCHAR2
)

is
v_Mail_Host VARCHAR2(50) := 'uacemail.rxcorp.com';
v_Mail_Conn utl_smtp.Connection;

crlf        VARCHAR2(2)  := chr(13)||chr(10);
CC_parties varchar2(2000);

begin

v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);

utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

 for i in (SELECT LEVEL AS id, REGEXP_SUBSTR(v_cc_list, '[^;]+', 1, LEVEL) AS cc_email_name
           FROM dual
           CONNECT BY REGEXP_SUBSTR(v_cc_list, '[^;]+', 1, LEVEL) IS NOT NULL) loop
    CC_parties := CC_parties||';'|| i.cc_email_name;
  utl_smtp.Rcpt(v_Mail_Conn,i.cc_email_name);

end loop;


utl_smtp.Data(v_Mail_Conn,
                'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
                'From: '   || v_From || crlf ||
                'Subject: '|| v_Subject || crlf ||
                'To: '     || v_Recipient || crlf ||
                'Cc: '     || CC_parties|| crlf ||
                'Content-Type: text/html;' ||crlf ||
                v_Mail_body);
utl_smtp.Quit(v_mail_conn);

EXCEPTION
   WHEN OTHERS THEN
      BEGIN
     DBMS_OUTPUT.put_line (
        SUBSTR (
              'Unable to send mail to recipients. Error message: '
              || SQLERRM
              || CHR (10)
              || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE (),
                 1,255));
     UTL_SMTP.quit (v_Mail_Conn);
     UTL_TCP.close_all_connections;
  EXCEPTION
     WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error   THEN
        NULL;     
  END;
END;

This is working fine for myself