Home » SQL & PL/SQL » SQL & PL/SQL » multiple recipients using UTL_SMTP package
multiple recipients using UTL_SMTP package [message #45157] Tue, 17 February 2004 11:40 Go to next message
Scion78
Messages: 15
Registered: February 2004
Junior Member
Can anyone point me out how to send an email to multiple recipients. Is there a delimeter that i have to use to separate the email addresses, or do i have to do the utl_smtp.rcpt(c,email) mulitple number of times.

thanks,
Re: multiple recipients using UTL_SMTP package [message #45160 is a reply to message #45157] Tue, 17 February 2004 12:11 Go to previous messageGo to next message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
See these useful links:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:255615160805
http://otn.oracle.com/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html
Re: multiple recipients using UTL_SMTP package [message #45161 is a reply to message #45157] Tue, 17 February 2004 13:33 Go to previous messageGo to next message
Dan White
Messages: 48
Registered: September 2003
Member
PACKAGE pkg_mail AUTHID CURRENT_USER IS
/*************************************************************************/
/* Purpose: Send Mail from PL/SQL */
/* See Package Body for modification history */
/* Author Dan White */
/*************************************************************************/
-- Public Global Declarations

Type varchar_tab IS TABLE OF VARCHAR2(48)
INDEX BY BINARY_INTEGER;

addr_list varchar_tab;

PROCEDURE send_mail(to_addr varchar2,
from_addr VARCHAR2,
subject VARCHAR2,
text_msg VARCHAR2,
sql_query VARCHAR2);
PROCEDURE send_mail(to_addr varchar_tab,
from_addr VARCHAR2,
subject VARCHAR2,
text_msg VARCHAR2,
sql_query VARCHAR2);
PROCEDURE open_mail_server;
PROCEDURE to_who (P_EMAIL_ADDRESS VARCHAR2);
PROCEDURE to_who (P_EMAIL_LIST VARCHAR_TAB);
PROCEDURE from_who (P_EMAIL_ADDRESS VARCHAR2);
PROCEDURE close_Mail_Server;
PROCEDURE Build_Message (message_body VARCHAR2);
PROCEDURE Build_Message (message_body VARCHAR2,
sql_query VARCHAR2);

END;
/

PACKAGE BODY pkg_mail IS
/*************************************************************************/
/* Purpose: Send Mail from PL/SQL */
/* Author: Dan White 7/22/2002 */
/* */
/*************************************************************************/
/* MODIFICATION HISTORY */
/* Person Date Comments */
/* --------- ----------- ------------------------------------------ */
/* Dwhite 7/22/2002 Created */
/* Dwhite 8/15/2002 Added to_Many for multiple recipient support */
/* and SQL query support */
/*************************************************************************/
-- Private Global Variables
v_mailhost_c varchar2(500);
v_mail_connection_c utl_smtp.connection;
v_message_c varchar2(32767);
v_recipient_c varchar2(500);
v_sender_c varchar2(500);
v_subject_c varchar2(500);
v_many varchar2(1) := 'N';

/*************************************************************************/
/* Send_Mail: Send mail using single address construct */
/*************************************************************************/
/* Parameters: */
/* to_addr: single recipient email address */
/* from_addr: sender */
/* subject: subject_line */
/* text_msg: text for message body */
/* sql_query: formatted query for message body. Note: the query should */
/* concatenate all columns and padding into 1 column */
/*************************************************************************/
procedure send_mail(to_addr varchar2,
from_addr varchar2,
subject varchar2,
text_msg varchar2,
sql_query varchar2)
is

v_email_list varchar_tab;

Begin
-- Initialize variables
v_sender_c := from_addr;
v_recipient_c := to_addr;
v_subject_c := subject;
v_message_c := text_msg;
--
-- Call Mail Procedures
--
open_mail_server;
From_Who(v_sender_c);
To_Who(v_recipient_c);
--
-- If no query is passed in , just send text message
--
if sql_query is null then
build_message(v_message_c);
else
build_message(v_message_c,sql_query);
end if;
close_mail_server;

EXCEPTION
WHEN others THEN
raise_application_error(-20900, 'Error in PKG_MAIL.SEND_MAIL(SINGLE)' ||
' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);
end;
/*************************************************************************/
/* Send_Mail: Send mail using multiple address construct */
/*************************************************************************/
/* Parameters: */
/* to_addr: PL/SQL table of recipients */
/* from_addr: sender */
/* subject: subject_line */
/* text_msg: text for message body */
/* sql_query: formatted query for message body. Note: the query should */
/* concatenate all columns and padding into 1 column */
/*************************************************************************/
procedure send_mail(to_addr varchar_tab,
from_addr varchar2,
subject varchar2,
text_msg varchar2,
sql_query varchar2)
is

Begin
-- Initialize variables
v_sender_c := from_addr;
addr_list := to_addr;
v_subject_c := subject;
v_message_c := text_msg;
--
-- Call Mail Procedures
--
open_mail_server;
From_Who(v_sender_c);
To_Who(addr_list);
--
-- If no query is passed in , just send text message
--
if sql_query is null then
build_message(v_message_c);
else
build_message(v_message_c,sql_query);
end if;
close_mail_server;

EXCEPTION
WHEN others THEN
raise_application_error(-20900, 'Error in PKG_MAIL.SEND_MAIL(MANY)' ||
' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);
end;
/*************************************************************************/
/* Open_Mail_Server: Establish connection to mail server */
/*************************************************************************/
PROCEDURE open_mail_server
is
-- Open Mail Server Connection
Begin
-- Set mail server conenction and perform initial handshake
v_mailhost_c := 'mymailserver.mydomain.com';
v_mail_connection_c := utl_smtp.open_connection(v_mailhost_c);
utl_smtp.helo(v_mail_connection_c,v_mailhost_c);

EXCEPTION
WHEN others THEN
raise_application_error(-20900, 'Error in PKG_MAIL.OPEN_MAIL_SERVER ' ||
' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);
end;

/*************************************************************************/
/* To_Who: Set single recipient */
/*************************************************************************/
PROCEDURE To_Who (P_EMAIL_ADDRESS VARCHAR2)
is
-- Set Single Recipient
Begin
utl_smtp.rcpt(v_mail_connection_c,P_EMAIL_ADDRESS);
EXCEPTION
WHEN others THEN
raise_application_error(-20900, 'Error in PKG_MAIL.TO_WHO(SINGLE)' ||
' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);
end;

/*************************************************************************/
/* To_Who: Set multiple recipients */
/*************************************************************************/
PROCEDURE To_Who (P_EMAIL_LIST VARCHAR_tab)
is
-- Set Multiple Recipients
Begin
v_many := 'Y';
for indx in p_email_list.first .. p_email_list.last
loop
utl_smtp.rcpt(v_mail_connection_c,P_EMAIL_LIST(indx));
end loop;
EXCEPTION
WHEN others THEN
raise_application_error(-20900, 'Error in PKG_MAIL.TO_WHO(MANY)' ||
' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);
end;

/*************************************************************************/
/* From_Who: Set the sender */
/*************************************************************************/
PROCEDURE From_Who (P_EMAIL_ADDRESS VARCHAR2)
is
-- Set Sender
Begin
utl_smtp.mail(v_mail_connection_c,P_EMAIL_ADDRESS);
EXCEPTION
WHEN others THEN
raise_application_error(-20900, 'Error in PKG_MAIL.FROM_WHO' ||
' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);
end;

/*************************************************************************/
/* Close_Mail_Server: Close mail server connection */
/*************************************************************************/
PROCEDURE close_mail_server
is
-- Close Mail Connection
Begin
utl_smtp.quit(v_mail_connection_c);
EXCEPTION
WHEN others THEN
raise_application_error(-20900, 'Error in PKG_MAIL.CLOSE_MAIL_SERVER' ||
' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);
end;

/*************************************************************************/
/* Build_Message: If no query is passed in , send a text message */
/*************************************************************************/
PROCEDURE Build_Message (message_body varchar2)
is
-- Send a Text Message
Begin
utl_smtp.open_data(v_mail_connection_c);
utl_smtp.write_data(v_mail_connection_c,'From: "'||v_sender_c||'" <'||v_sender_c||'>'|| utl_tcp.CRLF);
if v_many = 'Y' then
utl_smtp.write_data(v_mail_connection_c,'To: "Distribution" <Distribution>'|| utl_tcp.CRLF);
else
utl_smtp.write_data(v_mail_connection_c,'To: "'||v_recipient_c||'" <'||v_recipient_c||'>'|| utl_tcp.CRLF);
end if;
utl_smtp.write_data(v_mail_connection_c,'Subject: "'||v_subject_c||'"'|| utl_tcp.CRLF);
utl_smtp.write_data(v_mail_connection_c,utl_tcp.CRLF||message_body);
utl_smtp.close_data(v_mail_connection_c);
v_many := 'N';

EXCEPTION
WHEN others THEN
raise_application_error(-20900, 'Error in PKG_MAIL.BUILD_MESSAGE(TEXT)' ||
' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);

end;

/*************************************************************************/
/* Build_Message: If query is passed in, send a text message and results */
/*************************************************************************/
PROCEDURE BUild_Message (message_body varchar2,
sql_query varchar2)
is
-- Send a Text Message and Data

type refcursor is REF CURSOR;
report_cur refcursor;
report_rec varchar2(4000);

BEGIN

OPEN report_cur FOR sql_query;

utl_smtp.open_data(v_mail_connection_c);
utl_smtp.write_data(v_mail_connection_c,'From: "'||v_sender_c||'" <'||v_sender_c||'>'|| utl_tcp.CRLF);
--
-- If multiple recipients, set header 'to' info to 'Distribution'
--
if v_many = 'Y' then
utl_smtp.write_data(v_mail_connection_c,'To: "Distribution" <Distribution>'|| utl_tcp.CRLF);
else
utl_smtp.write_data(v_mail_connection_c,'To: "'||v_recipient_c||'" <'||v_recipient_c||'>'|| utl_tcp.CRLF);
end if;
utl_smtp.write_data(v_mail_connection_c,'Subject: "'||v_subject_c||'"'|| utl_tcp.CRLF);
utl_smtp.write_data(v_mail_connection_c,utl_tcp.CRLF||message_body);
utl_smtp.write_data(v_mail_connection_c,utl_tcp.CRLF||'==========================');
--
-- Fetch cursor and add to email text
--
LOOP
FETCH report_cur INTO report_rec;
EXIT WHEN report_cur%NOTFOUND;
v_message_c := report_rec;
utl_smtp.write_data(v_mail_connection_c,utl_tcp.CRLF||v_message_c);
END LOOP;

utl_smtp.close_data(v_mail_connection_c);
v_many := 'N';

EXCEPTION
WHEN others THEN
raise_application_error(-20900, 'Error in PKG_MAIL.BUILD_MESSAGE(QUERY)' ||
' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);

END;

END;
/
Re: multiple recipients using UTL_SMTP package [message #551652 is a reply to message #45161] Fri, 20 April 2012 00:32 Go to previous messageGo to next message
ramnath1989
Messages: 48
Registered: November 2011
Location: india
Member
Working fine ,
Thanks

will u pls send the code to call the procedure to send mail For multiple recipients .,


Thnaks .
Re: multiple recipients using UTL_SMTP package [message #551656 is a reply to message #551652] Fri, 20 April 2012 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you REALLY expect someone that have posted this 8 years ago will answer you?
Why don't you use UTL_MAIL instead?
And why don't you follow what Thomas gave you yesterday?
http://www.orafaq.com/forum/mv/msg/180612/551595/102589/#msg_551595

Regards
Michel
Re: multiple recipients using UTL_SMTP package [message #551678 is a reply to message #551656] Fri, 20 April 2012 01:46 Go to previous messageGo to next message
ramnath1989
Messages: 48
Registered: November 2011
Location: india
Member
okay..,

Do u have code [sample] to call utl.smtp multiple times,
by loop ,some functions etc.,???
Re: multiple recipients using UTL_SMTP package [message #551683 is a reply to message #551678] Fri, 20 April 2012 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you use UTL_MAIL instead?

Regards
Michel
Re: multiple recipients using UTL_SMTP package [message #563458 is a reply to message #45161] Mon, 13 August 2012 22:55 Go to previous messageGo to next message
babu2k
Messages: 1
Registered: August 2012
Location: India
Junior Member
Hi Everybody,

can anybody share the code for sending mail to multiple recipient,

I am in bad shape for delivering a component.

please help me.

my project requirement is to send mail to multiple recipient in "to" and one recipient in "cc" with attachment.

Regards,
Prasanta.
Re: multiple recipients using UTL_SMTP package [message #563459 is a reply to message #563458] Mon, 13 August 2012 22:59 Go to previous messageGo to next message
BlackSwan
Messages: 23150
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+UTL_MAIL+example

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: multiple recipients using UTL_SMTP package [message #563467 is a reply to message #563458] Tue, 14 August 2012 01:46 Go to previous message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Send mail from PL/SQL
SQL> desc utl_mail
PROCEDURE SEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SENDER                         VARCHAR2                IN
 RECIPIENTS                     VARCHAR2                IN
 CC                             VARCHAR2                IN     DEFAULT
 BCC                            VARCHAR2                IN     DEFAULT
 SUBJECT                        VARCHAR2                IN     DEFAULT
 MESSAGE                        VARCHAR2                IN     DEFAULT
 MIME_TYPE                      VARCHAR2                IN     DEFAULT
 PRIORITY                       BINARY_INTEGER          IN     DEFAULT
PROCEDURE SEND_ATTACH_RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SENDER                         VARCHAR2                IN
 RECIPIENTS                     VARCHAR2                IN
 CC                             VARCHAR2                IN     DEFAULT
 BCC                            VARCHAR2                IN     DEFAULT
 SUBJECT                        VARCHAR2                IN     DEFAULT
 MESSAGE                        VARCHAR2                IN     DEFAULT
 MIME_TYPE                      VARCHAR2                IN     DEFAULT
 PRIORITY                       BINARY_INTEGER          IN     DEFAULT
 ATTACHMENT                     RAW                     IN
 ATT_INLINE                     BOOLEAN                 IN     DEFAULT
 ATT_MIME_TYPE                  VARCHAR2                IN     DEFAULT
 ATT_FILENAME                   VARCHAR2                IN     DEFAULT
PROCEDURE SEND_ATTACH_VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SENDER                         VARCHAR2                IN
 RECIPIENTS                     VARCHAR2                IN
 CC                             VARCHAR2                IN     DEFAULT
 BCC                            VARCHAR2                IN     DEFAULT
 SUBJECT                        VARCHAR2                IN     DEFAULT
 MESSAGE                        VARCHAR2                IN     DEFAULT
 MIME_TYPE                      VARCHAR2                IN     DEFAULT
 PRIORITY                       BINARY_INTEGER          IN     DEFAULT
 ATTACHMENT                     VARCHAR2                IN
 ATT_INLINE                     BOOLEAN                 IN     DEFAULT
 ATT_MIME_TYPE                  VARCHAR2                IN     DEFAULT
 ATT_FILENAME                   VARCHAR2                IN     DEFAULT

Regards
Michel
Previous Topic: Oracle Date / Time Zone Conversion
Next Topic: Roles dependency
Goto Forum:
  


Current Time: Fri Dec 19 15:11:32 CST 2014

Total time taken to generate the page: 0.09417 seconds