Oracle utl_smtp to send 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.

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.

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

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.

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

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.

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.

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

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.

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.

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.

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.

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.

===================================================
 

This package was the first available email utility in Oracle which was introduced in the version 8i. This package requires that the programmer understands the details of the underlying SMTP protocol features. This package by default comes installed with the UTL_TCP package, with our database installation. If not, we can find the scripts in the below location and install them in the SYS schema and provide public grants for the other users in the database.

The UTL_SMTP package provides interfaces to the protocols consisting of certain commands for sending emails to an SMTP server. These protocols have both procedural and functional forms of interface. The functional form returns the reply from the SMTP server for each command sent in the form of success or failure. The procedural form raises an exception when the command sent is failed.

During the mail process, the body of the DATA command is transferred in 8-bits, whereas, the other SMTP commands are converted to US7ASCII (7-bit ASCII) and then transmitted.

Most of the APIs is overloaded as procedures and functions in the UTL_SMTP package because the procedural API fails with an exception if the reply from the server falls into an exception, but the function API returns the REPLY message for users to analyze during an exceptional scenery.

The objects and commands available in this package are described below,

CONNECTION Record Type

This record type is used for representing an SMTP connection.

The prototype of this record type is shown below,

·         HOST parameter holds the name of the target host when a connection is established. Holds null in case of no connection.

·         PORT parameter holds the port of the connected target host. Holds null in case of no connection.

·         TX_TIMEOUT parameter holds the time in seconds that this package waits before giving up on the read/write operation. 0 indicates no wait and null indicates to wait forever.

·         PRIVATE_TCP_CON and PRIVATE_STATE parameters are for internal use and must not be modified.

REPLY Record Type and REPLIES Index-by-table

These types are used to represent SMTP reply line(s). When an SMTP command responds with a single-line reply, the REPLY record type is used and when it responds with a multi-line reply, an associative array (REPLIES) of REPLY record type is used. In the case of the REPLY type, If the SMTP server returns more than one line of reply, the last line of the reply is only returned.

The prototypes of these types are shown below,

·         CODE parameter holds a 3-digit reply code.

·         TEXT parameter holds the text message for the corresponding reply code.

OPEN_CONNECTION Functions

There are two overloaded OPEN_CONNECTION functions to open up an SMTP server connection. The function with CONNECTION record type as its RETURN type raises an exception when there is an error in the connection. The function with REPLY record type as its RETURN type does not result in any exception as the error message is returned through its RETURN type for the user to analyze.

The prototypes of these functions are shown below,

·         C parameter returns the SMTP connection.

COMMAND Function/ Procedure and COMMAND_REPLIES Function

These subprograms are used to invoke a generic SMTP command. The COMMAND subprograms are to be used only when a single line reply is expected. If the SMTP server returns more than one line of reply, the last line of the reply is only returned. The COMMAND_REPLIES function is to be used when multiple reply lines are expected.

The prototypes of these subprograms are shown below,

·         CMD parameter accepts the SMTP command which is to be sent to the server.

·         ARG parameter accepts an optional argument for the SMTP command. Space will be sent between the CMD and the ARG parameter values.

NOOP Function and Procedure

These APIs does not have any impact except for returning a successful reply from the SMTP mail server. This is often used to check if the server is still connected or not. This command will always reply with a single line message with the message code: 250.

The prototypes of these subprograms are shown below,

HELO and EHLO Function/ Procedure

These subprograms perform the initial handshaking with the server after connecting with reference to the RFC 821 note, which states that the client must identify itself to the server after they get connected.

The HELO function returns the single-line reply from the SMTP server using the REPLY record type, whereas the EHLO function returns the multi-line extended information about its configuration using the REPLIES index-by-table type.

The prototypes of these subprograms are shown below,

·         DOMAIN parameter accepts the domain name of the client host for identification.

VRFY Function

This function verifies the validity of the destination email address and if it is successful, the recipient’s full name and mailbox path are returned back.            This API call must be made after OPEN_CONNECTION, and HELO or EHLO calls.

The prototype of this function is shown below,

HELP Function

This function sends the help command to the SMTP server. The return message from the server is in multi-line using the REPLIES index-by-table type.

The prototype of this function is shown below,

RCPT Function and Procedure

These subprograms specify the recipient of an email message. To send an email to multiple recipients, this API must be called multiple times, once for each recipient. This API must be called prior call to OPEN_CONNECTION, HELO or EHLO, and MAIL.

The prototypes of these subprograms are shown below,

·         RECIPIENT parameter accepts the email address of the recipient user.

·         PARAMETERS parameter accepts the additional parameter to the RCPT command.

MAIL Function and Procedure

These subprograms initiate the mail transaction with the destination mailbox. They do not send the email message, but only prepares them followed by RCPT and DATA to complete the transaction. The initial handshake must have been performed using HELO or EHLO APIs before this process.

The prototypes of these subprograms are shown below,

·         SENDER parameter accepts the email address of the user sending the message.

·         PARAMETERS parameter accepts the additional parameter to the MAIL command.

RSET Function and Procedure

This API terminates the current email transaction by abandoning the email that was being composed. This API must be called only after OPEN_CONNECTION and before DATA or OPEN_DATA is called. This call must be made before the email is sent, as it would be too late to terminate the transaction.

The prototypes of these subprograms are shown below,

OPEN_DATA Function and Procedure

These subprograms send the DATA command to the SMTP server so that the server is ready to accept the actual email message using the WRITE_DATA or WRITE_RAW_DATA subprograms. After the email message is sent, the data transfer can be terminated using the CLOSE_DATA subprogram. Using any other API other than the above list after the DATA command will raise the INVALID_OPERATION exception.

The OPEN_DATA subprogram can be called only after OPEN_CONNECTION, HELO or EHLO, MAIL, and RCPT has been called.

The prototypes of these subprograms are shown below,

WRITE_DATA and WRITE_RAW_DATA Procedures

These procedures are used to write the body of the email message. We must repeat calls to these procedures to append the text data to the email message. There is no function version of these APIs as the server does not respond back before the CLOSE_DATA call.

These procedures must be called only after OPEN_CONNECTION, HELO or EHLO, MAIL and RCPT have been called and must end with CLOSE_DATA call.

The single-byte VARCHAR2 data can be sent using the WRITE_DATA call where it is converted to 7-bit ASCII data before it is sent. The multi-byte VARCHAR2 data can be sent by first converting them to RAW type and then sending them using the WRITE_RAW_DATA call.

The prototypes of these procedures are shown below,

·         DATA parameter accepts the body of the email message to be sent.

CLOSE_DATA Function and Procedure

These subprograms end the call to the email message by sending the <CR><LF>.<CR><LF> (Carriage Return and Line Feed). These subprograms must be called only after OPEN_CONNECTION, HELO or EHLO, MAIL, and RCPT has been called.

The prototypes of these subprograms are shown below,

QUIT Function and Procedures

These APIs terminates the SMTP connection and disconnects it which was established in the first place using the OPEN_CONNECTION call. If there is an ongoing mail transaction, it is terminated similarly to the RSET API. After the SMTP connection is closed, the HOST and the PORT parameters of the CONNECTION record type are reset.

The prototypes of these subprograms are shown below,

In the below example, an email message with CLOB attachment is sent using the UTL_SMTP package. Unlike its successor, the UTL_MAIL package, this package does not need to set up an initialization parameter to indicate the mail server as it is accepted as a parameter during the SMTP connection. After the initial handshake process using the HELO API, the sender, and the receiver information is accepted using the MAIL and RCPT APIs respectively. The mail subject and the body are written using the WRITE_DATA procedure along with the CLOB attachment. The CLOB variable is looped with an offset until the complete attachment is sent. After the email message is sent, the SMTP connection is terminated using the QUIT API.