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

 
 Home
 E-mail Us
 Oracle Articles
New 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 Database Tips by Donald Burleson

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.

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

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.

 

ORACLE_HOME/RDBMS/ADMIN/utlsmtp.sql

ORACLE_HOME/RDBMS/ADMIN/utltcp.sql

 

% Note: This package is capable of only sending emails and cannot receive any!


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,

 

TYPE connection IS RECORD (

    host              VARCHAR2(255),     

    port              PLS_INTEGER,       

    tx_timeout        PLS_INTEGER,       

    private_tcp_con   utl_tcp.connection,

    private_state     PLS_INTEGER        

);

 

         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.

 

% Note: Modifying the parameters other than the internally used ones has no effect on the connection.

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,

 

TYPE reply IS RECORD (

  code    PLS_INTEGER,     

  text    VARCHAR2(508)    

);

 

TYPE replies IS TABLE OF reply INDEX BY BINARY_INTEGER;

 

         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,

 

UTL_SMTP.OPEN_CONNECTION (

   host        IN VARCHAR2,

   port        IN PLS_INTEGER DEFAULT 25,

   c           OUT connection,

   tx_timeout IN PLS_INTEGER DEFAULT NULL)

RETURN reply;

 

UTL_SMTP.OPEN_CONNECTION (

   host       IN VARCHAR2,

   port       IN PLS_INTEGER DEFAULT 25,

   tx_timeout IN PLS_INTEGER DEFAULT NULL)

RETURN connection;

 

         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,

      

UTL_SMTP.COMMAND (

   c   IN OUT NOCOPY connection,

   cmd IN            VARCHAR2,

   arg IN            VARCHAR2 DEFAULT NULL)

RETURN reply;

 

UTL_SMTP.COMMAND (

   c   IN OUT NOCOPY connection,

   cmd IN            VARCHAR2,

   arg IN            VARCHAR2 DEFAULT NULL);

 

UTL_SMTP.COMMAND_REPLIES (

   c     IN OUT NOCOPY    connection,

   cmd   IN               VARCHAR2,

   arg   IN               VARCHAR2 DEFAULT NULL)

RETURN replies;

 

         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,

 

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

 

UTL_SMTP.NOOP (c IN OUT NOCOPY connection);

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,

 

UTL_SMTP.HELO (

   c      IN OUT NOCOPY connection,

   domain IN VARCHAR2)

RETURN reply;

 

UTL_SMTP.HELO (

   c      IN OUT NOCOPY connection,

   domain IN VARCHAR2);

 

UTL_SMTP.EHLO (

   c      IN OUT NOCOPY connection,

   domain IN VARCHAR2)

RETURN replies;

 

UTL_SMTP.EHLO (

   c      IN OUT NOCOPY connection,

   domain IN VARCHAR2);

 

         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,

 

UTL_SMTP.VRFY (

   c         IN OUT NOCOPY connection

   recipient IN VARCHAR2)

RETURN reply;

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,

 

UTL_SMTP.HELP (

   c         IN OUT NOCOPY connection,

   command   IN VARCHAR2 DEFAULT NULL)

RETURN replies;

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,

 

UTL_SMTP.RCPT (

   c          IN OUT NOCOPY connection,

   recipient  IN VARCHAR2,

   parameters IN VARCHAR2 DEFAULT NULL)

RETURN reply;

 

UTL_SMTP.RCPT (

   c          IN OUT NOCOPY connection,

   recipient  IN VARCHAR2,

   parameters IN VARCHAR2 DEFAULT NULL);

 

         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,

 

UTL_SMTP.MAIL (

   c          IN OUT NOCOPY connection,

   sender     IN VARCHAR2,

   parameters IN VARCHAR2 DEFAULT NULL)

RETURN reply;

 

UTL_SMTP.MAIL (

   c          IN OUT NOCOPY connection,

   sender     IN VARCHAR2,

   parameters IN VARCHAR2 DEFAULT NULL);

 

         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,

                                                                                 

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

 

UTL_SMTP.RSET (c IN OUT NOCOPY connection);

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,

 

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

 

UTL_SMTP.OPEN_DATA (c IN OUT NOCOPY connection);

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,

 

UTL_SMTP.WRITE_DATA (

   c     IN OUT NOCOPY connection,

   data IN VARCHAR2 CHARACTER SET ANY_CS);

 

UTL_SMTP.WRITE_RAW_DATA (

   c     IN OUT NOCOPY connection

   data IN RAW);

 

         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,

 

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

 

UTL_SMTP.CLOSE_DATA (c IN OUT NOCOPY connection);

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,

 

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

 

UTL_SMTP.QUIT (c IN OUT NOCOPY connection);

 

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.

 

DECLARE

  l_smtp_conn UTL_SMTP.CONNECTION;

  l_cl_var1 CLOB   :='Clob attachment';

  l_n_offset NUMBER:=15000;

BEGIN

  l_smtp_conn := UTL_SMTP.OPEN_CONNECTION('mail.daredevil.com','25');

  UTL_SMTP.HELO(l_smtp_conn, 'mail.daredevil.com');

  UTL_SMTP.MAIL(l_smtp_conn, '"Kick Buttowsky" kick.buttowsky@daredevil.com');

  UTL_SMTP.RCPT(l_smtp_conn, '"Gunther Magnuson" gunther.magnuson@daredevil.com');

  UTL_SMTP.OPEN_DATA(l_smtp_conn);

  UTL_SMTP.WRITE_DATA(l_smtp_conn, 'From : "Kick Buttowsky" kick.buttowsky@daredevil.com'||UTL_TCP.CRLF);

  UTL_SMTP.WRITE_DATA(l_smtp_conn, 'To : "Gunther Magnuson" gunther.magnuson@daredevil.com'||UTL_TCP.CRLF);

  UTL_SMTP.WRITE_DATA(l_smtp_conn, 'Subject : Its time to kick!'||UTL_TCP.CRLF);

  UTL_SMTP.WRITE_DATA(l_smtp_conn, UTL_TCP.CRLF || 'Big Stunt - tomorrow morning at the dead man''s drop!');

  FOR loop_att IN 1 .. TRUNC(DBMS_LOB.getlength(l_cl_var1)/l_n_offset)

  LOOP

    UTL_SMTP.write_data(l_smtp_conn, DBMS_LOB.substr(l_cl_var1, l_n_offset, loop_att * l_n_offset + 1));

  END LOOP;

  UTL_SMTP.write_data(l_smtp_conn, UTL_TCP.crlf);

  UTL_SMTP.CLOSE_DATA(l_smtp_conn);

  UTL_SMTP.QUIT(l_smtp_conn);

EXCEPTION

WHEN OTHERS THEN

    UTL_SMTP.QUIT(l_smtp_conn);

END;

/

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
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 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.