Simple Procedure to send mails from Oracle

We can send emails using this program from oracle using any exchange server:-

 

 

 

Create or Replace Procedure Send_Mails (                       
                           sender     IN VARCHAR2,
                           recipients IN VARCHAR2,
                           subject    IN VARCHAR2,
                           message    IN VARCHAR2,
                           priority   IN NUMBER ) IS
 /*------------------------------------------------------------------------------------------------------------------------------------------
 Procedure to Send Mails with text attachments
 Parameters:-
 sender                               -    sender email id eg; maneesh.mohan@sos.sungard.com
 recipients                           -    recipient address, currently supports only one recipient
                                               eg; mh2000@gmail.com
 subject                              -    mail subject
 message                              -    message to display as body
 priority                             -    message priority valid range (1 .. 5)
 --------------------------------------------------------------------------------------------------------------------------------------------*/
 conn                  UTL_SMTP.CONNECTION;
 smtp_host             VARCHAR2(1000):='mailserver';
 smtp_port             NUMBER:=25;
 smtp_domain           VARCHAR2(1000):='smsi';
 boundary         CONSTANT VARCHAR2(256) := 'NextPart_000_0605_01C775EB.8BD624E0';
 first_boundary        CONSTANT VARCHAR2(256) := '--' || boundary || utl_tcp.CRLF;
 v_mes            VARCHAR2(1000);
 crlf                  VARCHAR2(5):=UTL_TCP.CRLF;
 -----------------------------------------------------------------------------------------------------------------------------------------
 BEGIN
 -- Open SMTP Connection
 conn := utl_smtp.open_connection(smtp_host, smtp_port);
 utl_smtp.helo(conn, smtp_domain);
 utl_smtp.mail(conn,sender);
 utl_smtp.rcpt(conn,recipients);
 --Start mail body
 utl_smtp.open_data(conn);
 v_mes:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi ss' ) || crlf ||
'From: ' || sender || crlf ||
'Subject: ' || subject || crlf ||
'To: ' || recipients || crlf ||
'Mime-Version: 1.0' || crlf;
--Add mail priority
IF priority IS NOT NULL THEN
    v_mes := v_mes || 'X-Priority: ' || to_char(priority) || '; ' || crlf;
END IF;

v_mes  :=   v_mes ||
          'Content-Type: multipart/mixed; boundary="' || boundary || '"' || crlf ||
          '' || crlf || '' || crlf ||
          first_boundary ||
          'Content-Type: text/plain; ' || crlf ||
          'Content-Disposition: inline;' || crlf ||
          'Content-Transfer-Encoding: 7bit' || crlf ||
          '' || crlf ||
          message || crlf ;

utl_smtp.write_data(conn,v_mes);
utl_smtp.close_data(conn);
utl_smtp.quit(conn);

EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
  BEGIN
    utl_smtp.close_data(conn);
  EXCEPTION
    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    NULL;
  END;
  RAISE_APPLICATION_ERROR(-20000,'Failed to send mail :' || SQLERRM);
 
End Send_Mails;

 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------