How to Send SMTP MAIL to Multiple Recipents using ORACLE UTL_SMTP package
------------------------------------------------------------------------------------------
Below procedure shows how to send email to mutliple recipents
CREATE OR REPLACE PROCEDURE XXCUST.XXX_SMTP_MAIL_NOTIF_PRC(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_smtp_server IN VARCHAR2 default 'mail.yourcompany.com', -- ask your dba for this server name and port
p_smtp_port IN VARCHAR2 default '25',
p_mail_sender IN VARCHAR2 default 'donotreply@doyensys.com' )
IS
p_subject varchar2(1000);
l_conn utl_smtp.connection;
l_row_found boolean := false ;
l_mesg varchar2(32767);
l_db_name varchar2(10) := substr(sys.database_name,1,7); -- database name
c_mail_boundary constant varchar2(255) default '--ABCDEFGHIJKL12345--';
c_mail_footer constant varchar2(1000) := '<p><b>Note:</b> For any Help please raise IT ticket to Support team.<br> ************* This is auto generated email from the system. Please do not reply *************</p></body></html>';
crlf constant varchar2(2):= chr(13) || chr(10);
TYPE email_list IS TABLE OF VARCHAR2(240); -- collection type definition
rec_list email_list; -- collection variable to hold multiple recipients
BEGIN
p_subject :='Email Subject Here';
select description bulk collect into rec_list from ar_lookups where lookup_type='NOTIF_EMAIL_LIST' and ENABLED_FLAG='Y' ; -- for getting recipients email id s from database table
FOR i in (
select col1,col2,col3,col4,col5 -- if you have email body text from database table
FROM table1,table2
WHERE table1.col6=table2.col5 --joing conditions here
)
LOOP
if not l_row_found then -- this will execute one time for each procedure call
l_conn := utl_smtp.open_connection (host => p_smtp_server, port => p_smtp_port ) ;
utl_smtp.helo(l_conn, p_smtp_server);
utl_smtp.mail(l_conn, p_mail_sender );
--utl_smtp.rcpt(l_conn, p_recip);
l_mesg := l_mesg ||'Date: '|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') ||crlf;
l_mesg := l_mesg ||'To: ';
for i in rec_list.FIRST .. rec_list.last loop -- if you have mutliple email recipients then you can use this for loop
utl_smtp.Rcpt(l_conn,rec_list(i));
if i < rec_list.last then
l_mesg := l_mesg ||rec_list(i)||',';
else
l_mesg := l_mesg ||rec_list(i)||crlf;
end if;
end loop;
l_mesg := l_mesg ||'From: '|| p_mail_sender||crlf;
l_mesg := l_mesg ||'Subject: '||p_subject||'-'||l_db_name ||crlf;
-- l_mesg := l_mesg || 'Reply-To: ' || c_mail_from || crlf;
l_mesg := l_mesg ||'MIME-Version: 1.0'|| crlf;
l_mesg := l_mesg || 'Content-Type: multipart/alternative; boundary=' ||chr(34) || c_mail_boundary || chr(34) || crlf;
l_mesg := l_mesg || 'content-type: text/html;' || crlf || crlf;
l_mesg := l_mesg || '<html><head><title>'||p_subject||'</title></head><body>';
utl_smtp.open_data(l_conn);
utl_smtp.write_data(l_conn,l_mesg);
utl_smtp.write_data(l_conn,'<p> This is to notify that the Dearchive program is completed and below is the summary.<br></p>');
utl_smtp.write_data(l_conn,'<table cellspacing="0" cellpadding="0" border="1">');
utl_smtp.write_data(l_conn,'<tr bgcolor="BLUE">'||
'<td width="200"><font face="Calibri">'||'<b>Col_HDR1</b>'||
'<td width="100" align="center"><font face="Calibri">'||'<b>Col_HDR2</b>'||
'<td width="180" align="center"><font face="Calibri">'||'<b>Col_HDR3</b>'||
'<td width="300" align="center"><font face="Calibri">'||'<b>Col_HDR4</b>'||
'<td width="400"><font face="Calibri">'||'<b>Col_HDR5</b>'||
'</tr>'||crlf
);
l_row_found := true;
end if;
utl_smtp.write_data(l_conn,'<tr>'||
'<td width="200"><font face="Calibri">'||i.col1||
'<td width="100" align="center"><font face="Calibri">'||i.col2||
'<td width="180" align="center"><font face="Calibri">'||i.col3||
'<td width="300" align="center"><font face="Calibri">'||i.col4||
'<td width="400"><font face="Calibri">'||i.col5||
'</tr>'||crlf
);
end loop;
if l_row_found then
utl_smtp.write_data(l_conn,'</table> ');
if p_mail_footer is null then
utl_smtp.write_data(l_conn,c_mail_footer);
else
utl_smtp.write_data(l_conn,p_mail_footer);
end if;
utl_smtp.close_data(l_conn);
utl_smtp.quit(l_conn);
end if;
--DBMS_OUTPUT.PUT_LINE('At END');
exception
when others then
retcode:=2;
errbuf:='error in procedure';
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error occured while running this program, please check LOG file for Error Details');
FND_FILE.PUT_LINE(FND_FILE.LOG,' Exception Occured , SQL ERROR MESSAGE :'||SQLERRM);
begin utl_smtp.quit(l_conn);
exception when others then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception Occured While utl_smtp.quit() , SQL ERROR MESSAGE :'||SQLERRM);
end;
end XXX_SMTP_MAIL_NOTIF_PRC;
/
------------------------------------------------------------------------------------------
Below procedure shows how to send email to mutliple recipents
CREATE OR REPLACE PROCEDURE XXCUST.XXX_SMTP_MAIL_NOTIF_PRC(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_smtp_server IN VARCHAR2 default 'mail.yourcompany.com', -- ask your dba for this server name and port
p_smtp_port IN VARCHAR2 default '25',
p_mail_sender IN VARCHAR2 default 'donotreply@doyensys.com' )
IS
p_subject varchar2(1000);
l_conn utl_smtp.connection;
l_row_found boolean := false ;
l_mesg varchar2(32767);
l_db_name varchar2(10) := substr(sys.database_name,1,7); -- database name
c_mail_boundary constant varchar2(255) default '--ABCDEFGHIJKL12345--';
c_mail_footer constant varchar2(1000) := '<p><b>Note:</b> For any Help please raise IT ticket to Support team.<br> ************* This is auto generated email from the system. Please do not reply *************</p></body></html>';
crlf constant varchar2(2):= chr(13) || chr(10);
TYPE email_list IS TABLE OF VARCHAR2(240); -- collection type definition
rec_list email_list; -- collection variable to hold multiple recipients
BEGIN
p_subject :='Email Subject Here';
select description bulk collect into rec_list from ar_lookups where lookup_type='NOTIF_EMAIL_LIST' and ENABLED_FLAG='Y' ; -- for getting recipients email id s from database table
FOR i in (
select col1,col2,col3,col4,col5 -- if you have email body text from database table
FROM table1,table2
WHERE table1.col6=table2.col5 --joing conditions here
)
LOOP
if not l_row_found then -- this will execute one time for each procedure call
l_conn := utl_smtp.open_connection (host => p_smtp_server, port => p_smtp_port ) ;
utl_smtp.helo(l_conn, p_smtp_server);
utl_smtp.mail(l_conn, p_mail_sender );
--utl_smtp.rcpt(l_conn, p_recip);
l_mesg := l_mesg ||'Date: '|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') ||crlf;
l_mesg := l_mesg ||'To: ';
for i in rec_list.FIRST .. rec_list.last loop -- if you have mutliple email recipients then you can use this for loop
utl_smtp.Rcpt(l_conn,rec_list(i));
if i < rec_list.last then
l_mesg := l_mesg ||rec_list(i)||',';
else
l_mesg := l_mesg ||rec_list(i)||crlf;
end if;
end loop;
l_mesg := l_mesg ||'From: '|| p_mail_sender||crlf;
l_mesg := l_mesg ||'Subject: '||p_subject||'-'||l_db_name ||crlf;
-- l_mesg := l_mesg || 'Reply-To: ' || c_mail_from || crlf;
l_mesg := l_mesg ||'MIME-Version: 1.0'|| crlf;
l_mesg := l_mesg || 'Content-Type: multipart/alternative; boundary=' ||chr(34) || c_mail_boundary || chr(34) || crlf;
l_mesg := l_mesg || 'content-type: text/html;' || crlf || crlf;
l_mesg := l_mesg || '<html><head><title>'||p_subject||'</title></head><body>';
utl_smtp.open_data(l_conn);
utl_smtp.write_data(l_conn,l_mesg);
utl_smtp.write_data(l_conn,'<p> This is to notify that the Dearchive program is completed and below is the summary.<br></p>');
utl_smtp.write_data(l_conn,'<table cellspacing="0" cellpadding="0" border="1">');
utl_smtp.write_data(l_conn,'<tr bgcolor="BLUE">'||
'<td width="200"><font face="Calibri">'||'<b>Col_HDR1</b>'||
'<td width="100" align="center"><font face="Calibri">'||'<b>Col_HDR2</b>'||
'<td width="180" align="center"><font face="Calibri">'||'<b>Col_HDR3</b>'||
'<td width="300" align="center"><font face="Calibri">'||'<b>Col_HDR4</b>'||
'<td width="400"><font face="Calibri">'||'<b>Col_HDR5</b>'||
'</tr>'||crlf
);
l_row_found := true;
end if;
utl_smtp.write_data(l_conn,'<tr>'||
'<td width="200"><font face="Calibri">'||i.col1||
'<td width="100" align="center"><font face="Calibri">'||i.col2||
'<td width="180" align="center"><font face="Calibri">'||i.col3||
'<td width="300" align="center"><font face="Calibri">'||i.col4||
'<td width="400"><font face="Calibri">'||i.col5||
'</tr>'||crlf
);
end loop;
if l_row_found then
utl_smtp.write_data(l_conn,'</table> ');
if p_mail_footer is null then
utl_smtp.write_data(l_conn,c_mail_footer);
else
utl_smtp.write_data(l_conn,p_mail_footer);
end if;
utl_smtp.close_data(l_conn);
utl_smtp.quit(l_conn);
end if;
--DBMS_OUTPUT.PUT_LINE('At END');
exception
when others then
retcode:=2;
errbuf:='error in procedure';
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error occured while running this program, please check LOG file for Error Details');
FND_FILE.PUT_LINE(FND_FILE.LOG,' Exception Occured , SQL ERROR MESSAGE :'||SQLERRM);
begin utl_smtp.quit(l_conn);
exception when others then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception Occured While utl_smtp.quit() , SQL ERROR MESSAGE :'||SQLERRM);
end;
end XXX_SMTP_MAIL_NOTIF_PRC;
/
Such a nice blog, I really like what you write in this blog, I also have some relevant Information about Best HR Training In Hyderabad | Hr training institute in Hyderabad! if you want more information.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
Hello Everyone,
ReplyDeleteI want to Send email using oracle apex Kindly provide the steps..its urgent
I am very thankful to you