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.

Step 5: Send email using UTL_MAIL with attachments

To send attachment we are using UTL_MAIL package of Oracle.
you must set UTL_FILE_DIR to a directory, where the attachment files exists

SQL> show parameter UTL_FILE_DIR
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /u02/attachment_dir

You must set the UTL_FILE_DIR to send the attachment. Also need to create a directory using create directory command.

 
SQL> Alter System Set UTL_FILE_DIR = ‘/u02/attachment_dir’; 

create or replace
PROCEDURE send_mail_attach AS
BEGIN
  UTL_MAIL.SEND_ATTACH_VARCHAR2(
                      sender =>       'tamimdba@yahoo.com',
                      recipients =>   'tamimdba@gmail.com',
                      cc =>           'tamim@dnsgroup.net',
                      bcc =>          'tamimdba@hotmail.com',
                      subject =>      'Attachment Test Mail',
                      message =>      'This is just a test mail with attachment.',
                      attachment =>   'text',
                      att_filename => 'attach.txt');
  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
 RAISE_APPLICATION_ERROR(-20001,'The following error has occurred: ' || sqlerrm);
END;