Send mail from PL/SQL code

Have you ever needed to send mail from within an Oracle PL/SQL procedure or funtion? Some colleagues of mine happened to be in the need of performing this task. With some googling I was able to find some bare bones code that allowed me send plain vanilla mails. But there were many limitations to what could be achieved with them. Having some working code was great, but what lacked there was sophistication. I wanted to have some features that would allow sending a well-formatted professional looking mail. Here is what I came up with. I have two implementations of the code; in form of a procedure and a function. Both implementations can send plain vanilla mail with a minimum number of arguments, but can perform advanced functions when passed more arguments. Here is the code for the procedure.

Create or Replace Procedure RR_SendMail

-- Written by Ranjeet Rain (ranjeet.rain@gmail.com)
-- You found this code at http://expertnotes.blogspot.com/

  (Sender IN VARCHAR2, 
  SendTo IN VARCHAR2, 
  Subject IN VARCHAR2, 
  BodyText IN VARCHAR2, 
  MailHost IN VARCHAR2, 
  SmtpPort IN NUMBER := 25, 
  CCTo IN VARCHAR2 := NULL, 
  MailFormat IN VARCHAR2 := 'text', -- text or text/html
  Priority IN NUMBER := 3 -- 3 = Medium, 5 = Low, 1 = High
  ) 

IS

  v_mail_conn UTL_SMTP.connection;
  v_mailhost VARCHAR2(64) := MailHost;
  n_smtpport VARCHAR2(4) := SmtpPort;
  v_subject VARCHAR2(64) := Subject;
  v_from VARCHAR2(64) := Sender;
  v_to VARCHAR2(64) := SendTo;
  v_CC VARCHAR2(64) := CCTo;
  v_bodytext VARCHAR2(1240) := BodyText;
BEGIN
  v_mail_conn := UTL_SMTP.open_connection(v_mailhost, n_smtpport);
  UTL_SMTP.helo(v_mail_conn, v_mailhost);
  UTL_SMTP.mail(v_mail_conn, v_from);
  UTL_SMTP.rcpt(v_mail_conn, v_to);
  UTL_SMTP.open_data(v_mail_conn);
  utl_smtp.write_data(v_mail_conn,
    'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || utl_tcp.crlf ||
    'From: ' || v_from || utl_tcp.crlf ||
    'To: ' || v_to || utl_tcp.crlf ||
    'CC: ' || v_CC || utl_tcp.crlf ||
    'Subject: ' || v_subject || utl_tcp.crlf ||
    'X-Priority: ' || Priority || utl_tcp.crlf ||
    'Content-Type: ' || MailFormat || utl_tcp.crlf
  );
  utl_smtp.write_data (v_mail_conn, v_bodytext);
  UTL_SMTP.close_data(v_mail_conn);
  UTL_SMTP.quit(v_mail_conn);

EXCEPTION
  WHEN UTL_SMTP.INVALID_OPERATION THEN
    dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
  WHEN UTL_SMTP.TRANSIENT_ERROR THEN
    dbms_output.put_line(' Temporary e-mail issue - try again'); 
  WHEN UTL_SMTP.PERMANENT_ERROR THEN
    dbms_output.put_line(' Permanent Error Encountered.'); 
  WHEN OTHERS THEN
    dbms_output.put_line(' Unknown Error Occured.'); 
END;

How do you use this code? See some sample usage.

-- Send a plain vanilla mail with just 5 parameters
EXEC RR_sendmail ('ranjeet.rain@gmail.com', 'ranjeet.rain@gmail.com', 'Subject', 
'Body text',  'Mail_host_name') 


-- Send a plain vanilla mail using a host running on Secure SMTP
EXEC RR_sendmail ('ranjeet.rain@gmail.com', 'ranjeet.rain@gmail.com', 'Subject', 
'Body text',  'smtp.gmail.com', 465) 


-- Send a text mail with a CC
EXEC RR_sendmail ('ranjeet.rain@gmail.com', 'ranjeet.rain@gmail.com', 'Subject', 
'Body text',  '130.1.3.1', 25, 'ranjeet.rain@gmail.com') 


-- Send a HTML mail with a CC
EXEC RR_sendmail ('ranjeet.rain@gmail.com', 'ranjeet.rain@gmail.com', 'Subject', 

 'Hello World! from <a href="mailto:ranjeet.rain@gmail.com">Ranjeet</a>',  
'Mail_host_name', 25, 'ranjeet.rain@gmail.com', 'text/html') 


-- Send a high priority HTML mail with a CC
EXEC RR_sendmail ('ranjeet.rain@gmail.com', 'ranjeet.rain@gmail.com', 'Subject', 
 'Hello World! from <a href="mailto:ranjeet.rain@gmail.com">Ranjeet</a>',  
'Mail_host_name', 25, 'ranjeet.rain@gmail.com', 'text/html', 1) 

So, as can be seen, the procedure is rather versatile and capable of sending all kind of text-based mails, very useful for sending small notificationmails. Only thing this procdure does not handle is multimedia mails (mail with MIME content). May be in future, if I write anything like that I'll update the page. Till then, happy mailing!!!

Dedicated IT Governance blog

Some time ago I embarked upon my little stint with IT Governance Tool from Mercury Interactive. I'd like to tell you that this is an interesting tool and I am working with it full-time now. I add a thought or two dedicated to the tool here -> http://itgplus.blogspot.com/ Get onto the next channel if you are interested!