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) := 'mandrillusername@yourdomain.com';
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 ;
To Test This Procedure:
EXEC .p_send_mail(‘Haneef Puttur’,’yourname@youremail.com’,’toaddress@test.com’,’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’);