How to sent email in Oracle PL/SQL package to multiple receivers?
oracle, oracle11g, plsql, plsqldeveloper
Solution
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.
Problem
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@MyCompany.on.ca'); utl_smtp.rcpt(c, p_recip); utl_smtp.open_data(c); send_header('From', '"Title" <Email@MyCompany.on.ca'); 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; -------------------------------------------------------------- ```