Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle utl_smtp to send e-mail


Oracle Tips by Burleson Consulting

 

Oracle utl_smtp utility to sending e-mail

The utl_smtp utility enables e-mail messages to be sent from the database (PL/SQL) to any valid e-mail address. This can be very useful in database monitoring since e-mails can be sent to production support personnel when certain events occur. These events could be anything ranging from space deficits to unauthorized database access. Anything that can be monitored can be sent in an e-mail.

sendmail.sql

CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle9.2';
mailhost VARCHAR2(30) := '127.0.0.1'; -- local database host

BEGIN
c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);

utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);

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;
/

According to OTN:

"This new package makes it possible for a PL/SQL programmer to send programmatically composed emails. It requires only the normal mental model of a user of a GUI email client rather than an understanding of the underlying protocol (SMTP) features.

This distinguishes it from Utl_Smtp which was introduced in Oracle8i Database. Utl_Smtp requires that the programmer understands the details of the SMTP protocol. Utl_Mail is much simpler to use because it supports just a limited, but very common, subset of the functionality that Utl_Smtp provides."

utl_smtp is a powerful e-mail utility that should be utilized in a monitoring environment.



UTL_SMTP

The UTL_SMTPpackage provides PL/SQL programs the capability to send emails over SMTP (Simple Mail Transfer Protocol). Note that this package requires that the Oracle XMLDB component was chosen during initial database creation or subsequent modification utilizing DBCA (Database Configuration Assistant ). Otherwise, the following series of error messages will occur:

 

SQL> @send_email

DECLARE

*

ERROR at line 1:

ORA-24248: XML DB extensible security not installed

ORA-06512: at "SYS.UTL_TCP", line 17

ORA-06512: at "SYS.UTL_TCP", line 246

ORA-06512: at "SYS.UTL_SMTP", line 115

ORA-06512: at "SYS.UTL_SMTP", line 138

ORA-06512: at line 7

 

The package will be detailed shortly, but this is one occasion where an example is worth more than the reference material. The email_myself_smtp_demo.sql PL/SQL code, shown below, is a simple snippet of PL/SQL to email a message.  Note that in this example, the author has an email server running on his PC, hence why 127.0.0.1 can be used for the mail host.

 

<  email_myself_smtp_demo.sql script

 

DECLARE

    mailhost   VARCHAR2(64) := '127.0.0.1';

    sender     VARCHAR2(64) := 'bert.scalzo@yahoo.com';

    recipient  VARCHAR2(64) := 'bert.scalzo@yahoo.com';

    mail_conn  utl_smtp.connection;

BEGIN

    mail_conn := utl_smtp.open_connection (mailhost, 25);

    utl_smtp.helo (mail_conn, mailhost);

    utl_smtp.mail (mail_conn, sender);

    utl_smtp.rcpt (mail_conn, recipient);

    utl_smtp.open_data (mail_conn);

    utl_smtp.write_data (mail_conn, 'This is a test of the emergency broadcast system, this is only a test' || chr(13));

    utl_smtp.write_data (mail_conn, 'In the case of an actual emergency, you would be informed where to tune' || chr(13));

    utl_smtp.close_data (mail_conn);

    utl_smtp.quit (mail_conn);

END;

/

 

That is really all there is to it. Now examine the procedures and functions provided by the UTL_SMTP  package. Note that key to the process is creating an email connection and then using that on subsequent UTL_SMTP calls. One also needs to work with the following data types:

 

TYPE reply IS RECORD (

  code    PLS_INTEGER,                  -- 3-digit reply code

  text    VARCHAR2(508)                 -- text message

);

 

TYPE replies IS TABLE OF reply

     INDEX BY BINARY_INTEGER;           -- multiple reply lines

 

close_data  is both a procedure and function that ends an email message by sending the proper termination sequence such as a single period at the beginning of a line. The function returns a record of type UTL_SMTP .reply.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

C

UTL_SMTP.CONNECTION

IN | OUT

 

Table 6.140:  Close_data Parameter

commandis both a procedure and function that performs a generic SMTP command. The function returns a record of type UTL_SMTP .reply..

  

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

C

UTL_SMTP.CONNECTION

IN | OUT

 

CMD

VARCHAR2

IN

 

ARG

VARCHAR2

IN

NULL

Table 6.141:  Command Parameters

command_replies  is a function that performs the initial handshaking with the SMTP server. It returns a data type of UTL_SMTP.replies, so there are multiple occurrences of a reply.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

C

UTL_SMTP.CONNECTION

IN | OUT

 

CMD

VARCHAR2

IN

 

ARG

VARCHAR2

IN

NULL

Table 6.142:  Command_replies Parameters

data  is both a procedure and function that specifies the email message body. The function returns a record of type UTL_SMTP.reply.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

C

UTL_SMTP.CONNECTION

IN | OUT

 

BODY

VARCHAR2

IN

 

Table 6.143:  Data Parameters

EHLO  is both a procedure and a function that performs the initial handshaking with the SMTP server with extended information returned. It returns a data type of UTL_SMTP .replies, so there are multiple occurrences of a reply.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

C

UTL_SMTP.CONNECTION

IN | OUT

 

DOMAIN

VARCHAR2

IN

 

Table 6.144:  EHLO Parameters

HELO is both a procedure and a function that performs the initial handshaking with the SMTP server without extended information returned. It returns a record of type UTL_SMTP .reply.

 

ARGUMENT

TYPE

IN / OUT

DEFAULT VALUE

C

UTL_SMTP.CONNECTION

IN | OUT

 

DOMAIN

VARCHAR2

IN

 

Table 6.145:  HELO Parameters

 

Help is a function that transmits the help command. It returns a record of type utl_smtp.reply.

 

Argument

Type

In / Out

Default Value

C

UTL_SMTP.CONNECTION

IN | OUT

 

DOMAIN

VARCHAR2

IN

NULL

Table 6.145:  Help Parameters

Mail is both a procedure and function that initiates a mail transaction. The function returns a record of type utl_smtp.reply.

 

Argument

Type

In / Out

Default Value

C

UTL_SMTP.CONNECTION

IN | OUT

 

SENDER

VARCHAR2

IN

 

PARAMETERS

VARCHAR2

IN

NULL

Table 6.146:  Mail Parameters

NOOP is both a procedure and function that performs a NULL command. It does not actually send the mail, just prepares to send it and requires subsequent calls to RCPT and DATA to complete the transaction. The function returns a record of type utl_smtp.reply.

 

Argument

Type

In / Out

Default Value

C

UTL_SMTP.CONNECTION

IN | OUT

 

Table 6.147:  NOOP Parameters

Open_connection is an overloaded function that takes two fairly different forms. However, each opens a connection to the SMTP server; they simply differ in their return styles. The first version returns the connection handle via and out parameter and returns utl_smtp.reply, shown here.

 

Argument

Type

In / Out

Default Value

HOST

VARCHAR2

IN

 

PORT

PLS_INETGER

IN

25

C

UTL_SMTP.CONNECTION

OUT

 

TX_TIMEOUT

PLS_INETGER

IN

NULL

Table 6.148:  Open_connection Parameters

The second version of open_connection simply returns the connect handle, which is type utl_smtp.connection.

 

Argument

Type

In / Out

Default Value

HOST

VARCHAR2

IN

 

PORT

PLS_INETGER

IN

25

TX_TIMEOUT

PLS_INETGER

IN

NULL

Table 6.149:  Open_connections Parameters, 2nd Version

Open_data is both a procedure and a function that transmits the DATA command to the SMTP server, after which one can then call write_data and write_raw_data. The function returns a record of type utl_smtp.reply.

 

Argument

Type

In / Out

Default Value

C

UTL_SMTP.CONNECTION

IN | OUT

 

Table 6.150:  Open_data Parameter

Quit is both a procedure and function that terminates the SMTP session. The function returns a record of type utl_smtp.reply.

 

Argument

Type

In / Out

Default Value

C

UTL_SMTP.CONNECTION

IN | OUT

 

Table 6.151:  Quit Parameter

RCPT is both a procedure and function that specifies the recipient of the email. The function returns a record of type utl_smtp.reply.

 

Argument

Type

In / Out

Default Value

C

UTL_SMTP.CONNECTION

IN | OUT

 

RECIPIENT

VARCHAR2

IN

 

PARAMETERS

VARCHAR2

IN

NULL

Table 6.152:  RCPT Parameters

RSET is both a procedure and function that simply terminates the current mail transaction. The function returns a record of type utl_smtp.reply.

 

Argument

Type

In / Out

Default Value

C

UTL_SMTP.CONNECTION

IN | OUT

 

Table 6.153:  RSET Parameter

VRFY is a function that verifies or validates the destination email address. It returns a record of type utl_smtp.reply.

 

Argument

Type

In / Out

Default Value

C

UTL_SMTP.CONNECTION

IN | OUT

 

DOMAIN

VARCHAR2

IN

 

Table 6.154:  VRFY Parameters

Write_data is a procedure that writes a portion of the email message and where repeat calls simply append data to the message.

 

Argument

Type

In / Out

Default Value

C

UTL_SMTP.CONNECTION

IN | OUT

 

DATA

VARCHAR2

IN

 

Table 6.155:  Write_data Parameters

Write_raw_data is also a procedure that writes a portion of the email message and where repeat calls append data to the message. The main difference is that the DATA parameter is now RAW.

 

Argument

Type

In / Out

Default Value

C

UTL_SMTP.CONNECTION

IN | OUT

 

DATA

RAW

IN

 

Table 6.156:  Write_raw_data parameters

UTL_SMTP is covered in more detail from the developerís perspective in Chapter 9.



Sending Email Notifications of Job Errors

The mechanism for sending email notifications can vary depending on the version of Oracle being used.  Oracle allows the use of the simpler utl_mail  package rather than the utl_smtp package available in previous versions.  Note that utl_mail is covered in more detail concerning its application for developers in Chapter 9 and utl_email and utl_smtp are both covered in more detail for DBAs in Chapter 6.

Using UTL_SMTP

The utl_smtp package was introduced in Oracle8i to give access to the SMTP protocol from PL/SQL.  The package is dependent on the JServer option, which can be loaded using the Database Configuration Assistant (DBCA) or by running the following scripts as the SYS user if it is not already present.

 

CONN sys/password AS SYSDBA

@$ORACLE_HOME/javavm/install/initjvm.sql

@$ORACLE_HOME/rdbms/admin/initplsj.sql

 

Using the package to send an email requires some knowledge of the SMTP protocol, but for the purpose of this text, a simple send_mail procedure has been written that should be suitable for most error reporting.

 

send_mail.sql

 

-- Parameters:
--    1) SMTP mail gateway.
--    2) From email address.
--    3) To email address.
--    4) Subject of email.
--    5) Text body of email.
-- *****************************************************************
 
CREATE OR REPLACE PROCEDURE send_mail (
  p_mail_host  IN  VARCHAR2,
  p_from       IN  VARCHAR2,
  p_to         IN  VARCHAR2,
  p_subject    IN  VARCHAR2,
  p_message    IN  VARCHAR2)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_mail_host, 25);
  UTL_SMTP.helo(l_mail_conn, p_mail_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);
 
  UTL_SMTP.open_data(l_mail_conn);
 
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, p_message || Chr(13));
 
  UTL_SMTP.close_data(l_mail_conn);
  UTL_SMTP.quit(l_mail_conn);
END send_mail;
/
SHOW ERRORS

 

The following code shows how the send_mail procedure can be used to send an email.  Obviously, one will need to substitute the appropriate parameter values.

 

BEGIN
  send_mail(p_mail_host => 'smtp.mycompany.com',
            p_from      => 'me@mycompany.com',
            p_to        => 'you@mycompany.com',
            p_subject   => 'Test SEND_MAIL Procedure',
            p_message   => 'If you are reading this it worked!');
END;
/

 

The p_mail_host parameter specifies the SMTP gateway that actually sends the message.

 

Now that the email mechanism has been presented, how to capture errors and produce email notifications will be explained.

 

The simplest way to achieve this is to place all the code related to the job into a database procedure or, preferably, a packaged procedure.  This allows the capture of errors using an exception handler and the generation of an appropriate email.  As an example, assume there is a need for a procedure to gather database statistics for an Oracle 8i or 9i instance.  A procedure like the one below might be defined.

 

automated_email_alert.sql

 

CREATE OR REPLACE PROCEDURE automated_email_alert AS
  l_mail_host  VARCHAR2(50) := 'smtp.mycompany.com';
  l_from       VARCHAR2(50) := 'jobs@mycompany.com';
  l_to         VARCHAR2(50) := 'tim@mycompany.com';
BEGIN
  DBMS_STATS.gather_database_stats(cascade => TRUE,
                                   options => 'GATHER AUTO');
  send_mail(p_mail_host => l_mail_host,
            p_from      => l_from,
            p_to        => l_to,
            p_subject   => 'AUTOMATED_EMAIL_ALERT (MYSID): Success',
            p_message   => 'AUTOMATED_EMAIL_ALERT (MYSID) completed successfully!');
 
EXCEPTION
  WHEN OTHERS THEN
    send_mail(p_mail_host => l_mail_host,
              p_from      => l_from,
              p_to        => l_to,
              p_subject   => 'AUTOMATED_EMAIL_ALERT (MYSID): Error',
              p_message   => 'AUTOMATED_EMAIL_ALERT (MYSID) failed with the following error:' || SQLERRM);
END automated_email_alert;
/
SHOW ERRORS

 

If this procedure were run as part of a scheduled job, an email notification would be generated whether the job completed successfully or not.  In the event of an error, the associated Oracle error would be reported.


 


 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.