Sending Email From Oracle PL/SQL (UTL_SMTP)

Sending Email From Oracle PL/SQL (UTL_SMTP)

Simple (Single Recipient)

Now we are going to learn about how to send the mail using oracle PL/SQL (UTL_SMTP).

First we will see the simple mailing it contains only sender and one receiver.

Procedure for sending mail.

CREATE OR REPLACE 

PROCEDURE send_mail(    p_from         varchar2,

                                              p_to             varchar2,

                                              p_body        varchar2

                                              errbuf    out  varchar2 )

AS

         l_smtp_host     varchar2(100)   := '<give ur mailing host name>';

         l_smtp_port      number              := <give the port number>;

         l_mail_conn     utl_smtp.connection;

         l_error              varchar2(1000);

Begin 

         l_mail_conn :=utl_smtp.open_connection(  l_smtp_host , l_smtp_port );

         utl_smtp.helo(l_mail_conn, l_smtp_host);                                                                                 
        utl_smtp.mail(l_mail_conn, p_from);

         utl_smtp.rcpt (l_mail_conn, p_to);

         utl_smtp.open_data(l_mail_conn);

         utl_smtp.write_data(l_mail_conn, p_body);

         utl_smtp.close_data(l_mail_conn);

         utl_smtp.quit(l_mail_conn);

Exception

         WHEN OTHERS THEN

         l_error := 'Error while sending mail :   ' || SQLERRM; 

         errbuf := l_error;

END;

/                                                      

 Now the procedure is created successfully. 

 We will execute the procedure to send the mail 

 Declare

 v_error varchar2(100);

 Begin       

         send_mail(p_from => '<sender mail id>',

                           p_to     => '<recevier mail id>',

                           p_body=> ' This is a test mail.',

                           v_error)

End

/ 

Sending email to multiple recipients

Now we are going to learn about how to send the mail to multiple recipients.

 create or replace package test_mail_pkg
  as
      type array is table of varchar2(255);
  
            procedure send( p_sender_email in varchar2,
                   p_from         in varchar2 default NULL,
                   p_to           in array default array(),
                   p_cc           in array default array(),
                   p_subject      in varchar2 default NULL,
                   p_body         in long  default NULL );
   end;
   /
   
   
   
   create or replace package body test_mail_pkg
    as
  
    g_crlf        char(2) default chr(13)||chr(10);
    g_mail_conn   utl_smtp.connection;
    g_mailhost    varchar2(255) := '<give ur mailing host name>';
    
    function address_email( p_string in varchar2,
                            p_recipients in array ) return varchar2
   is
       l_recipients long;
   begin
      for i in 1 .. p_recipients.count
      loop
         utl_smtp.rcpt(g_mail_conn, p_recipients(i) );
         if ( l_recipients is null )
         then
             l_recipients := p_string || p_recipients(i) ;
         else
             l_recipients := l_recipients || ', ' || p_recipients(i);
         end if;
      end loop;
      return l_recipients;
   end;
   
   procedure send( p_sender_email in varchar2,
                   p_from         in varchar2 default NULL,
                   p_to           in array default array(),
                   p_cc           in array default array(),
                   p_subject      in varchar2 default NULL,
                   p_body         in long  default NULL )
   is
       l_to_list   long;
       l_cc_list   long;
        
       l_date      varchar2(255) default
                   to_char( SYSDATE, 'dd Mon yy hh24:mi:ss' );

       procedure writeData( p_text in varchar2 )
       as
       begin
           if ( p_text is not null )
           then
               utl_smtp.write_data( g_mail_conn, p_text || g_crlf );
           end if;
       end;
       
       begin
       g_mail_conn := utl_smtp.open_connection('<give ur mailing host name>',<give the port number>);

       utl_smtp.helo(g_mail_conn, g_mailhost);
       utl_smtp.mail(g_mail_conn, p_sender_email);
       
         l_to_list  := address_email( 'To: ', p_to );
       l_cc_list  := address_email( 'Cc: ', p_cc );
        
       
       utl_smtp.open_data(g_mail_conn );

       writeData( 'Date: ' || l_date );
       writeData( 'From: ' || nvl( p_from, p_sender_email ) );
       writeData( 'Subject: ' || nvl( p_subject, '(no subject)' ) );

       writeData( l_to_list );
       writeData( l_cc_list );

       utl_smtp.write_data( g_mail_conn, '' || g_crlf );
       utl_smtp.write_data(g_mail_conn, p_body );
       utl_smtp.close_data(g_mail_conn );
       utl_smtp.quit(g_mail_conn);
   end;


  end;
  
  

 

The package for sending mail to sending multiple recipient is completed.
Now execute the procedure to send mail to multiple recipient.

Begin
        multi_res_mail_pkg( p_sender_email => '<sender mail id>',
                                         p_from => '<sender mail id>',
                                         p_to => test_mail_pkg.array( '<recevier mail id>','<recevier mail id>' ),
                                         p_cc => test_mail_pkg.array('<recevier mail id>','<recevier mail id>'  ),
                                         p_subject => 'Test Mail',
                                         p_body => 'This is a test mail with multiple recipients.'
                                         )
End;
/