Tuesday, May 28, 2013

Sending Email using PL/SQL Procedure

Posted by at 3:34 AM Read our previous post

You can send mails using the following UTL_SMTP package

  v_From      VARCHAR2(80) := '';
  v_Recipient VARCHAR2(80) := '';
  v_Subject   VARCHAR2(80) := 'test subject';
  v_Mail_Host VARCHAR2(30) := '';
  v_Mail_Conn utl_smtp.Connection;
  crlf        VARCHAR2(2)  := chr(13)||chr(10);
 v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
 utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
 utl_smtp.Mail(v_Mail_Conn, v_From);
 utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
   'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
   'From: '   || v_From || crlf ||
   'Subject: '|| v_Subject || crlf ||
   'To: '     || v_Recipient || crlf ||
   crlf ||
   'some message text'|| crlf ||      -- Message body
   'more message text'|| crlf
 WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
   raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);

You may also like to read      Find Profile Option values using SQL queries
                                             Process to Debug a Concurrent Request
                                             Checking the File Versions with SQL Query

                                             Find Trace File location using SQL

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer
    LinkedIn profile -
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at | +91 - 9581017828.


© Oracle Apps CRM is powered by Blogger - Template designed by Stramaxon - Best SEO Template