Home > UTL_SMTP & UTL_MAIL > Oracle UTL_SMTP And UTIL_MAIL Service

Oracle UTL_SMTP And UTIL_MAIL Service

The UTL_MAIL or UTL_SMPT package is introduced in Oracle 10g and it is easier to use when compared to UTL_SMTP.  In order to use Oracle UTL_MAIL package we have to set a new init.ora parameter or spinit.ora if database running in spfile “SMTP_OUT_SERVER” set to outgoing mail server. Make sure that there is a getaway for the outgoing mail.

Step 1: Send Mail in Linux Server

[root@vasdbsrv mail]# rpm -qa |grep sendmail
sendmail-8.13.1-3.RHEL4.5
sendmail-cf-8.13.1-3.RHEL4.5

To verify the mail service running in the local host

[oracle@vasdbsrv ~]$ telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.
220 vasdbsrv.ring.com.bd ESMTP Sendmail 8.13.1/8.13.1; Thu, 10 Dec 2009 14:54:51 +0600

If sendmail not installed then install it by using following command.

$ rpm –ivh sendmail

Step 2: Make sure the send mail service run on kick start.

[root@vasdbsrv mail]# chkconfig –list |grep sendmail

sendmail        0:off   1:off   2:on    3:on    4:on    5:on    6:off

Add service in checkcofig:

$ chkconfig  --add sendmail
$ chkconfig  --level 345 sendmail on

Step 3: Configure the /etc/mail/sendmail.cf

If you are running mail server in private IP

[oracle@vasdbsrv ~]$ vi /etc/mail/sendmail.cf
# "Smart" relay host (may be null)
DSmail.dnsgroup.net

Step 4: Configure /etc/resolv.conf

[root@vasdbsrv mail]# vi /etc/resolv.conf
search ring.com.bd
nameserver 203.188.191.5

Follow the simple steps to send an email using UTL_MAIL package

Step 5: Install UTL_MAIL or UTL_SMTP package

To install the UTL_MAIL and UTL_SMTP package, run the below files as user “SYS”

Source:

$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/utlsmtp.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb

Step 6: Grant permissions

Grants the execute permission on UTL_MAIL privilege to PUBLIC or the user which will use the package.

Run the beow command as user “SYS”

 SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
 -or-
 SQL> GRANT EXECUTE ON utl_smtp TO <USER NAME>;

Step 7: Set SMTP_OUT_SERVER parameter

 SQL> ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=both;

Step 8: Create procedure to send email

 create or replace PROCEDURE send_mail_smtp
 (   sender     IN VARCHAR2,
 recipient  IN VARCHAR2,
 subject    IN VARCHAR2,
 message    IN LONG
 )
IS
   mailhost     VARCHAR2(30) := 'localhost';  -- -- host mail address
   mail_conn    utl_smtp.connection ;
   crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
   mesg long;
BEGIN
   mail_conn := utl_smtp.open_connection(mailhost, 25);

   mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
          'From:  <'||sender||'>' || crlf ||
          'Subject: '||subject || crlf ||
          'To: '||recipient || crlf ||
          '' || crlf || message;
   utl_smtp.helo(mail_conn, mailhost);
   utl_smtp.mail(mail_conn, sender);
   utl_smtp.rcpt(mail_conn, recipient);
   utl_smtp.data(mail_conn, mesg);
   utl_smtp.quit(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.'); 
END;

To Run the SEND_MAIL_SMTP run the following code.

BEGIN
SEND_MAIL_SMTP(
'tamimdba@yahoo.com',           --Sender
'tamimdba@gmail.com',           --Recipient
'Test Mail',                    --Subject
'Send From Oracle10g Database'  --Message
);
END;
PL/SQL procedure successfully completed.
Advertisements
  1. Anshuman Rudra
    December 29, 2009 at 1:23 pm

    Hi Tamim,
    Nice blog!
    I have an issue with the attachments send thru’ utl_mail package. I followed the steps you’ve given in the blog, but recipient receives the mail with attachment with no data.
    Any idea; why is this occurring.

    Thanks in advance.

    Regards,
    -Anshuman

    • Tamim Khan
      December 29, 2009 at 9:28 pm

      Hello dear,
      I think there might be some problem to read file from the directory. can you pls check the file following way. ie read/write something from oracle.
      declare
      f utl_file.file_type;
      begin
      f := utl_file.fopen(‘ATTACHMENT_DIR’, ‘something.txt’, ‘w’);
      utl_file.put_line(f, ‘line one: some text’);
      utl_file.put_line(f, ‘line two: more text’);
      utl_file.fclose(f);
      end;

      /

  2. abhijit satam
    February 20, 2011 at 4:35 pm

    hi tamim,

    i want such trigger or procedure to send a mail of ORA- error. when alert_sid.log giving any ORA -ERROR after that mail has been fire. please help me on this issue

  3. January 24, 2017 at 9:52 pm

    Does this still work on ORacle 11g and ORacle 12c

    • January 25, 2017 at 10:22 am

      No. In oracle 11g or 12c it should not be work..

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: