UTL_SMTP v11
The UTL_SMTP package provides the capability to send e-mails over the Simple Mail Transfer Protocol (SMTP).
Note
An administrator must grant execute privileges to each user or group before they can use this package.
| Function/Procedure | Function or Procedure | Return Type | Description |
|---|---|---|---|
CLOSE_DATA(c IN OUT) | Procedure | n/a | Ends an e-mail message. |
COMMAND(c IN OUT, cmd [, arg ]) | Both | REPLY | Execute an SMTP command. |
COMMAND_REPLIES(c IN OUT, cmd [, arg ]) | Function | REPLIES | Execute an SMTP command where multiple reply lines are expected. |
DATA(c IN OUT, body VARCHAR2) | Procedure | n/a | Specify the body of an e-mail message. |
EHLO(c IN OUT, domain) | Procedure | n/a | Perform initial handshaking with an SMTP server and return extended information. |
HELO(c IN OUT, domain) | Procedure | n/a | Perform initial handshaking with an SMTP server |
HELP(c IN OUT [, command ]) | Function | REPLIES | Send the HELP command. |
MAIL(c IN OUT, sender [, parameters ]) | Procedure | n/a | Start a mail transaction. |
NOOP(c IN OUT) | Both | REPLY | Send the null command. |
OPEN_CONNECTION(host [, port [, tx_timeout ]]) | Function | CONNECTION | Open a connection. |
OPEN_DATA(c IN OUT) | Both | REPLY | Send the DATA command. |
QUIT(c IN OUT) | Procedure | n/a | Terminate the SMTP session and disconnect. |
RCPT(c IN OUT, recipient [, parameters ]) | Procedure | n/a | Specify the recipient of an e-mail message. |
RSET(c IN OUT) | Procedure | n/a | Terminate the current mail transaction. |
VRFY(c IN OUT, recipient) | Function | REPLY | Validate an e-mail address. |
WRITE_DATA(c IN OUT, data) | Procedure | n/a | Write a portion of the e-mail message. |
Advanced Server's implementation of UTL_SMTP is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above are supported.
The following table lists the public variables available in the UTL_SMTP package.
| Public Variables | Data Type | Value | Description |
|---|---|---|---|
connection | RECORD | Description of an SMTP connection. | |
reply | RECORD | SMTP reply line. |
CONNECTION
The CONNECTION record type provides a description of an SMTP connection.
TYPE connection IS RECORD (
host VARCHAR2(255),
port PLS_INTEGER,
tx_timeout PLS_INTEGER
);REPLY/REPLIES
The REPLY record type provides a description of an SMTP reply line. REPLIES is a table of multiple SMTP reply lines.
TYPE reply IS RECORD (
code INTEGER,
text VARCHAR2(508)
);
TYPE replies IS TABLE OF reply INDEX BY BINARY_INTEGER;CLOSE_DATA
The CLOSE_DATA procedure terminates an e-mail message by sending the following sequence:
<CR><LF>.<CR><LF>
This is a single period at the beginning of a line.
CLOSE_DATA(<c> IN OUT CONNECTION)
Parameters
c
The SMTP connection to be closed.
COMMAND
The COMMAND procedure provides the capability to execute an SMTP command. If you are expecting multiple reply lines, use COMMAND_REPLIES.
<reply> REPLY COMMAND(<c> IN OUT CONNECTION, <cmd> VARCHAR2 [, <arg> VARCHAR2 ]) COMMAND(<c> IN OUT CONNECTION, <cmd> VARCHAR2 [, <arg> VARCHAR2 ])
Parameters
c
The SMTP connection to which the command is to be sent.
cmd
The SMTP command to be processed.
arg
An argument to the SMTP command. The default is null.
reply
SMTP reply to the command. If SMTP returns multiple replies, only the last one is returned in reply.
See Reply/Replies for a description of REPLY and REPLIES.
COMMAND_REPLIES
The COMMAND_REPLIES function processes an SMTP command that returns multiple reply lines. Use COMMAND if only a single reply line is expected.
<replies> REPLIES COMMAND(<c> IN OUT CONNECTION, <cmd> VARCHAR2 [, <arg> VARCHAR2 ])
Parameters
c
The SMTP connection to which the command is to be sent.
cmd
The SMTP command to be processed.
arg
An argument to the SMTP command. The default is null.
replies
SMTP reply lines to the command. See Reply/Replies for a description of REPLY and REPLIES.
DATA
The DATA procedure provides the capability to specify the body of the e-mail message. The message is terminated with a <CR><LF>.<CR><LF> sequence.
DATA(<c> IN OUT CONNECTION, <body> VARCHAR2)
Parameters
c
The SMTP connection to which the command is to be sent.
body
Body of the e-mail message to be sent.
EHLO
The EHLO procedure performs initial handshaking with the SMTP server after establishing the connection. The EHLO procedure allows the client to identify itself to the SMTP server according to RFC 821. RFC 1869 specifies the format of the information returned in the server’s reply. The HELO procedure performs the equivalent functionality, but returns less information about the server.
EHLO(<c> IN OUT CONNECTION, <domain> VARCHAR2)
Parameters
c
The connection to the SMTP server over which to perform handshaking.
domain
Domain name of the sending host.
HELO
The HELO procedure performs initial handshaking with the SMTP server after establishing the connection. The HELO procedure allows the client to identify itself to the SMTP server according to RFC 821. The EHLO procedure performs the equivalent functionality, but returns more information about the server.
HELO(<c> IN OUT, <domain> VARCHAR2)
Parameters
c
The connection to the SMTP server over which to perform handshaking.
domain
Domain name of the sending host.
HELP
The HELP function provides the capability to send the HELP command to the SMTP server.
<replies> REPLIES HELP(<c> IN OUT CONNECTION [, <command> VARCHAR2 ])
Parameters
c
The SMTP connection to which the command is to be sent.
command
Command on which help is requested.
replies
SMTP reply lines to the command. See Reply/Replies for a description of REPLY and REPLIES.
The MAIL procedure initiates a mail transaction.
MAIL(<c> IN OUT CONNECTION, <sender> VARCHAR2 [, <parameters> VARCHAR2 ])
Parameters
c
Connection to SMTP server on which to start a mail transaction.
sender
The sender’s e-mail address.
parameters
Mail command parameters in the format, key=value as defined in RFC 1869.
NOOP
The NOOP function/procedure sends the null command to the SMTP server. The NOOP has no effect upon the server except to obtain a successful response.
<reply> REPLY NOOP(<c> IN OUT CONNECTION) NOOP(<c> IN OUT CONNECTION)
Parameters
c
The SMTP connection on which to send the command.
reply
SMTP reply to the command. If SMTP returns multiple replies, only the last one is returned in reply. See Reply/Replies for a description of REPLY and REPLIES.
OPEN_CONNECTION
The OPEN_CONNECTION functions open a connection to an SMTP server.
<c> CONNECTION OPEN_CONNECTION(<host> VARCHAR2 [, <port> PLS_INTEGER [, <tx_timeout> PLS_INTEGER DEFAULT NULL]])
Parameters
host
Name of the SMTP server.
port
Port number on which the SMTP server is listening. The default is 25.
tx_timeout
Time out value in seconds. Do not wait is indicated by specifying 0. Wait indefinitely is indicated by setting timeout to null. The default is null.
c
Connection handle returned by the SMTP server.
OPEN_DATA
The OPEN_DATA procedure sends the DATA command to the SMTP server.
OPEN_DATA(<c> IN OUT CONNECTION)
Parameters
c
SMTP connection on which to send the command.
QUIT
The QUIT procedure closes the session with an SMTP server.
QUIT(<c> IN OUT CONNECTION)
Parameters
c
SMTP connection to be terminated.
RCPT
The RCPT procedure provides the e-mail address of the recipient. To schedule multiple recipients, invoke RCPT multiple times.
RCPT(<c> IN OUT CONNECTION, <recipient> VARCHAR2 [, <parameters> VARCHAR2 ])
Parameters
c
Connection to SMTP server on which to add a recipient.
recipient
The recipient’s e-mail address.
parameters
Mail command parameters in the format, key=value as defined in RFC 1869.
RSET
The RSET procedure provides the capability to terminate the current mail transaction.
RSET(<c> IN OUT CONNECTION)
Parameters
c
SMTP connection on which to cancel the mail transaction.
VRFY
The VRFY function provides the capability to validate and verify the recipient’s e-mail address. If valid, the recipient’s full name and fully qualified mailbox is returned.
<reply> REPLY VRFY(<c> IN OUT CONNECTION, <recipient> VARCHAR2)
Parameters
c
The SMTP connection on which to verify the e-mail address.
recipient
The recipient’s e-mail address to be verified.
reply
SMTP reply to the command. If SMTP returns multiple replies, only the last one is returned in reply. See Reply/Replies for a description of REPLY and REPLIES.
WRITE_DATA
The WRITE_DATA procedure provides the capability to add VARCHAR2 data to an e-mail message. The WRITE_DATA procedure may be repetitively called to add data.
WRITE_DATA(<c> IN OUT CONNECTION, <data> VARCHAR2)
Parameters
c
The SMTP connection on which to add data.
data
Data to be added to the e-mail message. The data must conform to the RFC 822 specification.
Comprehensive Example
The following procedure constructs and sends a text e-mail message using the UTL_SMTP package.
CREATE OR REPLACE PROCEDURE send_mail (
p_sender VARCHAR2,
p_recipient VARCHAR2,
p_subj VARCHAR2,
p_msg VARCHAR2,
p_mailhost VARCHAR2
)
IS
v_conn UTL_SMTP.CONNECTION;
v_crlf CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
v_port CONSTANT PLS_INTEGER := 25;
BEGIN
v_conn := UTL_SMTP.OPEN_CONNECTION(p_mailhost,v_port);
UTL_SMTP.HELO(v_conn,p_mailhost);
UTL_SMTP.MAIL(v_conn,p_sender);
UTL_SMTP.RCPT(v_conn,p_recipient);
UTL_SMTP.DATA(v_conn, SUBSTR(
'Date: ' || TO_CHAR(SYSDATE,
'Dy, DD Mon YYYY HH24:MI:SS') || v_crlf
|| 'From: ' || p_sender || v_crlf
|| 'To: ' || p_recipient || v_crlf
|| 'Subject: ' || p_subj || v_crlf
|| p_msg
, 1, 32767));
UTL_SMTP.QUIT(v_conn);
END;
EXEC send_mail('asmith@enterprisedb.com','pjones@enterprisedb.com','Holiday
Party','Are you planning to attend?','smtp.enterprisedb.com');The following example uses the OPEN_DATA, WRITE_DATA, and CLOSE_DATA procedures instead of the DATA procedure.
CREATE OR REPLACE PROCEDURE send_mail_2 (
p_sender VARCHAR2,
p_recipient VARCHAR2,
p_subj VARCHAR2,
p_msg VARCHAR2,
p_mailhost VARCHAR2
)
IS
v_conn UTL_SMTP.CONNECTION;
v_crlf CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
v_port CONSTANT PLS_INTEGER := 25;
BEGIN
v_conn := UTL_SMTP.OPEN_CONNECTION(p_mailhost,v_port);
UTL_SMTP.HELO(v_conn,p_mailhost);
UTL_SMTP.MAIL(v_conn,p_sender);
UTL_SMTP.RCPT(v_conn,p_recipient);
UTL_SMTP.OPEN_DATA(v_conn);
UTL_SMTP.WRITE_DATA(v_conn,'From: ' || p_sender || v_crlf);
UTL_SMTP.WRITE_DATA(v_conn,'To: ' || p_recipient || v_crlf);
UTL_SMTP.WRITE_DATA(v_conn,'Subject: ' || p_subj || v_crlf);
UTL_SMTP.WRITE_DATA(v_conn,v_crlf || p_msg);
UTL_SMTP.CLOSE_DATA(v_conn);
UTL_SMTP.QUIT(v_conn);
END;
EXEC send_mail_2('asmith@enterprisedb.com','pjones@enterprisedb.com','Holiday
Party','Are you planning to attend?','smtp.enterprisedb.com');