Thursday, August 31, 2006

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 (
-- You found this code at

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


  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;
  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);
    '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);

    dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
    dbms_output.put_line(' Temporary e-mail issue - try again'); 
    dbms_output.put_line(' Permanent Error Encountered.'); 
    dbms_output.put_line(' Unknown Error Occured.'); 

How do you use this code? See some sample usage.
-- Send a plain vanilla mail with just 5 parameters
EXEC RR_sendmail ('', '', 'Subject', 
'Body text',  'Mail_host_name') 

-- Send a plain vanilla mail using a host running on Secure SMTP
EXEC RR_sendmail ('', '', 'Subject', 
'Body text',  '', 465) 

-- Send a text mail with a CC
EXEC RR_sendmail ('', '', 'Subject', 
'Body text',  '', 25, '') 

-- Send a HTML mail with a CC
EXEC RR_sendmail ('', '', 'Subject', 

'Hello World! from <a href="">Ranjeet</a>', 'Mail_host_name', 25, '', 'text/html') -- Send a high priority HTML mail with a CC EXEC RR_sendmail ('', '', 'Subject',
'Hello World! from <a href="">Ranjeet</a>', 'Mail_host_name', 25, '', '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!!!

1 comment:

Anonymous said...


I mostly visits this website[url=].[/url]You have really contiributed very good info here Frankly speaking we really do not pay attention towards our health. Let me show you one truth. Recent Scientific Research indicates that almost 60% of all USA adults are either obese or weighty[url=].[/url] So if you're one of these people, you're not alone. Its true that we all can't be like Brad Pitt, Angelina Jolie, Megan Fox, and have sexy and perfect six pack abs. Now the question is how you are planning to have quick weight loss? Quick weight loss can be achived with little effort. You need to improve some of you daily habbits to achive weight loss in short span of time.

About me: I am blogger of [url=]Quick weight loss tips[/url]. I am also health expert who can help you lose weight quickly. If you do not want to go under difficult training program than you may also try [url=]Acai Berry[/url] or [url=]Colon Cleansing[/url] for quick weight loss.