PL/SQL for Sending Email with Mandrill API :: Oracle UTL_SMTP with Authentication

By | January 11, 2016
CREATE OR REPLACE PR
OCEDURE p_send_mail (
p_sender_name IN VARCHAR2,
p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_subject IN VARCHAR2, 
--p_cc  IN VARCHAR2, 
p_message IN VARCHAR2) 
IS
conn          UTL_SMTP.CONNECTION;
crlf          VARCHAR2(2)    := CHR(13) || CHR(10);

-- Mandrill Login Details
uname         VARCHAR2(200)  := '[email protected]';
EmailServer   VARCHAR2(60)   := 'smtp.mandrillapp.com';
pwd           VARCHAR2(200)  := 'mandrillappkey';
p_port   VARCHAR2(10)   := '25'; -- if you have issue use port 587


pq_message    VARCHAR2(5000);

-- Do Not Edit Below This Line
-- This PL/SQL procedure is created and tested by Haneef Puttur , for Mandrill

BEGIN
  conn := utl_smtp.open_connection(EmailServer, p_port);
  
  utl_smtp.ehlo(conn, EmailServer); 
  utl_smtp.command(conn, 'AUTH LOGIN');
  utl_smtp.command(conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode( utl_raw.cast_to_raw(uname))));
  utl_smtp.command(conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode( utl_raw.cast_to_raw(pwd))));
  utl_smtp.mail(conn, p_sender);
  utl_smtp.rcpt(conn, p_recipient);

  pq_message:= 'Date: '|| TO_CHAR(SYSDATE, 'DD MON RR HH24:MI:SS' )|| crlf ||
         'From: "' || p_sender_name || '" ' || p_sender || crlf ||
         'Subject: ' || p_subject || crlf ||
        -- 'Cc: '     || p_cc || crlf ||   -- uncomment this line for sending cc
         'Content-Type: text/html;' ||crlf ||  -- Comment this line if you want only plain text email
         'To: '|| p_recipient || crlf|| p_message || crlf || crlf;

  utl_smtp.data(conn, pq_message);
  utl_smtp.quit(conn);
  EXCEPTION
  WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END ;

plsql

To Test This Procedure:

EXEC .p_send_mail(‘Haneef Puttur’,’[email protected]’,’[email protected]’,’Message Subject’,’Haneef Puttur, This is test messgae.<br> I am sure that various <br>clients of <strong>Mandrill </strong>will like this.This is html test message to go’);

 

 .

  • Ben

    Hi Haneef