Sending emails from Oracle using UTL_SMTP.

In this post I want to show a simple way to send emails from an oracle database using the PL/SQL package UTL_SMTP.
Let’s start with a simple example:

CREATE OR REPLACE PROCEDURE SENDMAIL (dest in varchar2,msg in varchar2) is
    mailhost    VARCHAR2(10) := 'localhost';
    sender      VARCHAR2(20) := 'massimo@massimo.it';
    mail_conn   utl_smtp.connection;
    r           utl_smtp.replies;
BEGIN
    mail_conn := utl_smtp.open_connection(mailhost, 25);
    r:=utl_smtp.ehlo(mail_conn, mailhost);
    for i in r.first..r.last loop
       dbms_output.put_line('helo code='||r(i).code||' text='||r(i).text);
    end loop;
    utl_smtp.mail(mail_conn, sender);
    utl_smtp.rcpt(mail_conn, dest);
    utl_smtp.open_data(mail_conn);
    utl_smtp.write_data(mail_conn, 'From: '||sender||chr(13)|| CHR(10));
    utl_smtp.write_data(mail_conn, 'Subject: '||'messaggio di test'||chr(13)|| CHR(10));
    utl_smtp.write_data(mail_conn, 'To: '||dest||chr(13)|| CHR(10));
    utl_smtp.write_data(mail_conn, chr(13)|| CHR(10));
    utl_smtp.write_data(mail_conn, msg||chr(13)|| CHR(10));
    utl_smtp.close_data(mail_conn);
    utl_smtp.quit(mail_conn);
END;
/ 

The procedure gets as inputs the recipient’s address and the message to send.
Let’s analyze it step by step:

First of all, an smtp server to use is declared.
The sender’s address is hard-coded.
The first call opens the connection to the smtp server. To do this I’ve used the open_connection function that returns an handler to the connection. This handler will be always used for the whole program.
The next call is performed to initialize the connection starting the handshake phase.
There are two possible ehlo commands. The one I used in the most complete as it returns a PL/SQL table containing all the server’s abilities.
After receiving them I print them on the screen using DBMS_OUTPUT. The other option was the following procedure:

utl_smtp.helo(mail_conn, mailhost);

That’s identical to the previous one but has no output.

Once met the server we use the MAIL procedure to start the email sending process.
RCPT procedure selects the recipient’s mailbox. This procedure can be used more than once if we have more than one recipient.
The OPEN_DATA procedure opens the email’s body writing session.
After OPEN_DATA we can call as many WRITE_DATA as we need closed from a single call to COLSE_DATA.
In our example we use WRITE_DATA to write the following fields of the email: From, Subject, To.
The empty row closed the email’s header and starts the body.
In the body we could, once again, call WRITE_DATA as many times as we need. We get the text as an input parameter.
The send procedure is completed by the call to CLOSE_DATA, then we leave the server using QUIT.

Let’s run the procedure:

SQL> exec SENDMAIL('massimo.ruocchio@mymail.it','Dear friend, I''m writing...')
BEGIN SENDMAIL('massimo.ruocchio@mymail.it','Dear friend, I''m writing...'); END;

*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "SCOTT.SENDMAIL", line 7
ORA-06512: at line 1

This error will be thrown only in Oracle11g, due to security configuration. Access Control Lists are lists that check whether a PL/SQL program can access critical external resources or not.
We must grant to the program the right to use resource.
Let’s create a specific ACL for the SMTP resource and let’s associate it to our server:


BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl          => 'SMTP_ACL.xml', 
  description  => 'ACL for SMTP',
  principal    => 'MAXR',
  is_grant     => TRUE, 
  privilege    => 'connect',
  start_date   => null,
  end_date     => null); 
END;
/

BEGIN
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl         => 'SMTP_ACL.xml',
  host        => 'localhost', 
  lower_port  => 25,
  upper_port  => 25); 
END;
/

With the first statement I created the ACL that is stored in an xml document(SMTP_ACL.xml) then I associated it to the schema MAXR granting to him the right to connect to the resource.
With the second statement I assign the ACL to the resource specifying the range of ports.
Once made this security configuration I try again:

SQL> exec  SENDMAIL('massimo.ruocchio@mymail.it','Dear friend, I''m writing...')
helo code=250 text=Hello localhost
helo code=250 text=AUTH LOGIN
helo code=250 text=ENHANCEDSTATUSCODES
helo code=250 text=PIPELINING

The email has successful reached the destination.

SSL connection are supported since Oracle11gR2 using STARTTLS:

(for Oracle’s docs)
STARTTLS Function and Procedure
This subprogram sends the STARTTLS command to secure the SMTP connection using SSL/TLS. SSL/TLS requires an Oracle wallet which must be specified when the connection was opened by the OPEN_CONNECTION Functions.

Syntax

UTL_SMTP.STARTTLS (c IN OUT NOCOPY connection) RETURN reply;

UTL_SMTP.STARTTLS (c IN OUT NOCOPY connection);

Parameters
c SMTP connection

Return Values
reply SMTP reply

Usage Notes

The STARTTLS command must only be issued on an unencrypted connection and when the SMTP server indicates the support of the command in the reply of the EHLO command. The wallet to be used for encryption must have been specified when the initial SMTP connection was opened by the OPEN_CONNECTION function.

Examples

DECLARE
c utl_smtp.connection;
BEGIN
c := utl_smtp.open_connection(
host => ‘smtp.example.com’,
port => 25,
wallet_path => ‘file:/oracle/wallets/smtp_wallet’,
wallet_password => ‘password’,
secure_connection_before_smtp => FALSE);
utl_smtp.starttls(c);
END

Massimo

About these ads

Tags: , , ,

2 Responses to “Sending emails from Oracle using UTL_SMTP.”

  1. Inviare email dal DB utilizzando UTL_SMTP « Oracle Italia by Massimo Ruocchio Says:

    […] This article is also available in English here. […]

  2. sreeram Says:

    I used the smpt code for sending the emails, some are getting triggered successfully but some are triggering with out body….can any one let me know where the issue is?

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


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: