Thursday, 11 January 2018

ORACLE UTL_SMTP MAIL

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;
/

No comments:

Post a Comment