UTL_SMTP v14

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.

MAIL

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');

← Prev

UTL_RAW

↑ Up

Built-in packages

UTL_URL

UTL_SMTP v14

Note

CONNECTION

REPLY/REPLIES

CLOSE_DATA

Parameters

COMMAND

Parameters

COMMAND_REPLIES

Parameters

DATA

Parameters

EHLO

Parameters

HELO

Parameters

HELP

Parameters

MAIL

Parameters

NOOP

Parameters

OPEN_CONNECTION

Parameters

OPEN_DATA

Parameters

QUIT

Parameters

RCPT

Parameters

RSET

Parameters

VRFY

Parameters

WRITE_DATA

Parameters

Comprehensive example

← Prev

↑ Up

Next →