Loading

Saturday, March 17, 2012

Script to Send Email Using Oracle PLSQL (UTL_SMTP)


This post gives you an example script to send an E-Mail from Oracle PL/SQL using the UTL_SMTP.

SCRIPT:



SET SERVEROUTPUT ON; 
DECLARE
   l_err_msg            VARCHAR2 (2000)     := NULL;
   l_email_text         VARCHAR2 (32000)    DEFAULT NULL;
   p_host_id_address    VARCHAR2 (100);
   p_remittance_email   VARCHAR2 (200)      := 'xyz@shareoracleapps.com';
   l_new_line           VARCHAR2 (2)        := UTL_TCP.crlf;
   l_connection         UTL_SMTP.connection;
BEGIN
   BEGIN
      SELECT UTL_INADDR.get_host_address ('mailhost.shareoracleapps.com')
        INTO p_host_id_address
        FROM DUAL;
   END;

   l_connection := UTL_SMTP.open_connection (p_host_id_address, 25);
   UTL_SMTP.helo (l_connection, p_host_id_address);
   UTL_SMTP.mail (l_connection, 'Teamsearch@shareoracleapps.com');
   UTL_SMTP.rcpt (l_connection, p_remittance_email);
   l_email_text :=
         l_email_text
      || 'From: '
      || 'teamsearch@shareoracleapps.com'
      || l_new_line
      || 'Subject: Test Email'
      || l_new_line
      || 'To: '
      || p_remittance_email
      || l_new_line
      || 'Content-Type: text/html'
      || l_new_line
      || l_new_line
      || '<HTML><BODY><P><PRE>'
      || l_new_line
      || 'This is Blog contains the details about Oracle apps Technical and Functional'
      || '</PRE></P></BODY></HTML>';
   fnd_file.put_line (fnd_file.LOG, 'Calling UTL_SMTP.DATA');
   UTL_SMTP.DATA (l_connection, l_email_text);
   DBMS_OUTPUT.put_line ('Calling UTL_SMTP.quit');
   UTL_SMTP.quit (l_connection);
   DBMS_OUTPUT.put_line ('Email Sent Successfull.');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'Unexpected Error Occured  = '
                            || l_err_msg
                            || SUBSTR (SQLERRM, 1, 200)
                           );
END send_email_detail;


1 Responses to “Script to Send Email Using Oracle PLSQL (UTL_SMTP)”

Mohamed Taha said...
January 10, 2013 at 3:32 AM

Thanks a looooooooooooooooooooot


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.