UTL_SMTP v16
The UTL_SMTP package sends emails 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 | End an email 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 email 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 email message. |
RSET(c IN OUT) | Procedure | n/a | Terminate the current mail transaction. |
VRFY(c IN OUT, recipient) | Function | REPLY | Validate an email address. |
WRITE_DATA(c IN OUT, data) | Procedure | n/a | Write a portion of the email message. |
EDB Postgres Advanced Server's implementation of UTL_SMTP is a partial implementation when compared to Oracle's version. Only the functions and procedures listed in the table 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 email 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 close.
COMMAND
The COMMAND procedure executes an SMTP command. If you're 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 to send the command.
cmd
The SMTP command to process.
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 you expect only a single reply line.
<replies> REPLIES COMMAND(<c> IN OUT CONNECTION, <cmd> VARCHAR2 [, <arg> VARCHAR2 ])
Parameters
c
The SMTP connection to which to send the command.
cmd
The SMTP command to process.
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 specifies the body of the email 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 to send the command.
body
Body of the email message to send.
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 sends the HELP command to the SMTP server.
<replies> REPLIES HELP(<c> IN OUT CONNECTION [, <command> VARCHAR2 ])
Parameters
c
The SMTP connection to which to send the command.
command
Command for which you want help.
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 email 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 on 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
Timeout value in seconds. Specify 0 to indicate not to wait. Set this value to null to wait indefinitely. 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 terminate.
RCPT
The RCPT procedure provides the email 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 email address.
parameters
Mail command parameters in the format key=value as defined in RFC 1869.
RSET
The RSET procedure terminates the current mail transaction.
RSET(<c> IN OUT CONNECTION)
Parameters
c
SMTP connection on which to cancel the mail transaction.
VRFY
The VRFY function validates and verifies the recipient’s email address. If valid, the recipient’s full name and fully qualified mailbox are returned.
<reply> REPLY VRFY(<c> IN OUT CONNECTION, <recipient> VARCHAR2)
Parameters
c
The SMTP connection on which to verify the email address.
recipient
The recipient’s email address to verify.
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 adds VARCHAR2 data to an email message. You can call the WRITE_DATA procedure repeatedly to add data.
WRITE_DATA(<c> IN OUT CONNECTION, <data> VARCHAR2)
Parameters
c
The SMTP connection on which to add data.
data
Data to add to the email message. The data must conform to the RFC 822 specification.
Comprehensive example
This procedure constructs and sends a text email 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');
This 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');