UTL_SMTP v18

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
`AUTH(c IN OUT, username, password [, schemes ])`	Both	`Reply`	Send the `AUTH` command to authenticate with the SMTP server.
`CLOSE_DATA(c IN OUT)`	Both	`REPLY`	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)`	Both	`REPLY`	Specify the body of an email message.
`EHLO(c IN OUT, domain)`	Both	`REPLIES`	Perform initial handshaking with an SMTP server and return extended information.
`HELO(c IN OUT, domain)`	Both	`REPLY`	Perform initial handshaking with an SMTP server
`HELP(c IN OUT [, command ])`	Function	`REPLIES`	Send the `HELP` command.
`MAIL(c IN OUT, sender [, parameters ])`	Both	`REPLY`	Start a mail transaction.
`NOOP(c IN OUT)`	Both	`REPLY`	Send the null command.
`OPEN_CONNECTION(host, port, tx_timeout, wallet_path, wallet_password, secure_connection_before_smtp, secure_host, ssl_cert_path)`	Function	`CONNECTION`	Open a connection.
`OPEN_DATA(c IN OUT)`	Both	`REPLY`	Send the `DATA` command.
`QUIT(c IN OUT)`	Both	`REPLY`	Terminate the SMTP session and disconnect.
`RCPT(c IN OUT, recipient [, parameters ])`	Both	`REPLY`	Specify the recipient of an email message.
`RSET(c IN OUT)`	Both	`REPLY`	Terminate the current mail transaction.
`STARTTLS(c IN OUT connection [, secure_host ])`	Both	`REPLY`	Initiate the STARTTLS command to upgrade the connection to a secure TLS connection.
`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;

AUTH

These subprograms send the AUTH command to authenticate with the SMTP server. The UTL_SMTP package automatically attempts the user's chosen authentication schemes in order, selecting the first one supported by the server.

# Procedure
AUTH(
   c        IN OUT  connection,
   username VARCHAR2,
   password VARCHAR2,
   schemes  VARCHAR2 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES
);

# Function
AUTH(
   c        IN OUT connection,
   username VARCHAR2,
   password VARCHAR2,
   schemes  VARCHAR2 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES
) RETURN reply;

Parameters

c

The SMTP connection to authenticate.

username

The username for authentication.

password

The password for authentication.

schemes

The authentication schemes to attempt. The default is NON_CLEARTEXT_PASSWORD_SCHEMES. Allowed values are ALL_SCHEMES and NON_CLEARTEXT_PASSWORD_SCHEMES.

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.

Note

NON_CLEARTEXT_PASSWORD_SCHEMES supports the CRAM-MD5 authentication schemes which do not transmit passwords in clear text over SMTP. These schemes are available for use even when the connection is not secured by SSL/TLS. Although these schemes avoid cleartext transmission, their overall security may still be compromised when used over an unsecured SMTP connection.
ALL_SCHEMES supports PLAIN, LOGIN and CRAM-MD5 authentication schemes. The PLAIN and LOGIN schemes transmit passwords in clear text over SMTP and should only be used over a secure SSL/TLS connection.

CLOSE_DATA

The CLOSE_DATA subprogram terminates an email message by sending the following sequence:

<CR><LF>.<CR><LF>

This is a single period at the beginning of a line.

# Procedure
CLOSE_DATA(
   c IN OUT CONNECTION
);

# Function
CLOSE_DATA(
    c IN OUT CONNECTION
) RETURN reply;

Parameters

c

The SMTP connection to close.

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

The COMMAND subprograms execute an SMTP command. If you're expecting multiple reply lines, use COMMAND_REPLIES.

# Procedure
COMMAND( 
   c   IN OUT CONNECTION, 
   cmd VARCHAR2,
   arg VARCHAR2 DEFAULT NULL
); 

# Function
COMMAND( 
   c   IN OUT CONNECTION, 
   cmd VARCHAR2, 
   arg VARCHAR2 DEFAULT NULL
) RETURN reply;

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.

COMMAND_REPLIES(
   c   IN OUT CONNECTION, 
   cmd VARCHAR2,
   arg VARCHAR2 DEFAULT NULL
) RETURN replies;

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 subprograms specify the body of the email message. The message is terminated with a <CR><LF>.<CR><LF> sequence.

# Procedure
DATA(
   c    IN OUT CONNECTION,
   body VARCHAR2
);

# Function
DATA( 
    c    IN OUT CONNECTION,
    body VARCHAR2
) RETURN reply;

Parameters

c

The SMTP connection to which to send the command.

body

Body of the email message to send.

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.

EHLO

The EHLO subprograms perform 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.

# Procedure
EHLO(
   c      IN OUT CONNECTION, 
   domain VARCHAR2
);

# Function
EHLO(
   c      IN OUT CONNECTION, 
   domain VARCHAR2
) RETURN replies;

Parameters

c

The connection to the SMTP server over which to perform handshaking.

domain

Domain name of the sending host.

replies

SMTP reply lines to the command. See Reply/Replies for a description of REPLY and REPLIES.

HELO

The HELO subprograms perform 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.

# Procedure
HELO(
   c      IN OUT CONNECTION, 
   domain VARCHAR2
);

# Function
HELO(
   c      IN OUT CONNECTION, 
   domain VARCHAR2
) RETURN reply;

Parameters

c

The connection to the SMTP server over which to perform handshaking.

domain

Domain name of the sending host.

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.

HELP

The HELP function sends the HELP command to the SMTP server.

HELP(
   c       IN OUT CONNECTION, 
   command VARCHAR2 DEFAULT NULL
) RETURN replies;

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.

# Procedure
MAIL(
   c          IN OUT CONNECTION, 
   sender     VARCHAR2, 
   parameters VARCHAR2 DEFAULT NULL
);

# Function
MAIL(
   c          IN OUT CONNECTION, 
   sender     VARCHAR2, 
   parameters VARCHAR2 DEFAULT NULL
) RETURN reply;

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.

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.

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.

# Procedure
NOOP(
   c IN OUT CONNECTION
);

# Function
NOOP(
   c IN OUT CONNECTION
) RETURN reply;

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.

OPEN_CONNECTION( 
    host                          VARCHAR2(255),
    port                          INTEGER DEFAULT 25,
    tx_timeout                    INTEGER DEFAULT NULL,
    wallet_path                   VARCHAR2(255) DEFAULT NULL,
    wallet_password               VARCHAR2(255) DEFAULT NULL,
    secure_connection_before_smtp BOOLEAN DEFAULT FALSE,
    secure_host                   VARCHAR2(255) DEFAULT NULL,
    ssl_cert_path                 VARCHAR2(255) DEFAULT NULL
) RETURN connection;

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.

wallet_path

Added for Oracle compatibility. The default value is NULL and should be NULL always.

wallet_password

Added for Oracle compatibility. The default value is NULL and should be NULL always.

secure_connection_before_smtp

If TRUE, establishes a secure SSL/TLS connection before any SMTP communication. If FALSE, the connection starts as a standard SMTP connection. The default is FALSE.

secure_host

The host name to matched against the common name of the SMTP server’s SSL certificate. It can also be a domain name like "*.example.com". If NULL, the SMTP hostname is used to connect.

ssl_cert_path

Path to the SSL certificate.

connection

Returns the newly established SMTP connection.

OPEN_DATA

The OPEN_DATA subprograms send the DATA command to the SMTP server.

# Procedure
OPEN_DATA(
   c IN OUT CONNECTION
);

# Function
OPEN_DATA(
   c IN OUT connection
) RETURN reply;

Parameters

c

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.

QUIT

The QUIT subprograms close the session with an SMTP server.

# Procedure
QUIT(
   c IN OUT CONNECTION
);

# Function
QUIT(
   c IN OUT CONNECTION
) RETURN reply;

Parameters

c

SMTP connection to terminate.

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.

RCPT

The RCPT subprograms provide the email address of the recipient. To schedule multiple recipients, invoke RCPT multiple times.

# Procedure
RCPT(
   c          IN OUT CONNECTION, 
   recipient  VARCHAR2 
   parameters VARCHAR2 DEFAULT NULL
);

# Function
RCPT(
   c          IN OUT CONNECTION, 
   recipient  VARCHAR2, 
   parameters VARCHAR2 DEFAULT NULL
) RETURN reply;

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.

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.

RSET

The RSET subprograms terminate the current mail transaction.

# Procedure
RSET(
   c IN OUT CONNECTION
);

# Function
RSET(
   c IN OUT CONNECTION
) RETURN reply;

Parameters

c

SMTP connection on which to cancel the mail transaction.

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.

STARTTLS

These subprograms ensure a secure SMTP connection by sending the STARTTLS command to establish SSL/TLS encryption.

# Procedure
STARTTLS(
    c           IN OUT connection,
    secure_host VARCHAR2 DEFAULT NULL
);

# Function
STARTTLS(
   c            IN OUT connection,
   secure_host  VARCHAR2 DEFAULT NULL
) RETURN reply;

Parameters

c

SMTP connection on which to send the command.

secure_host

The host name to be matched against the common name of the SMTP server’s SSL certificate. It can also be a domain name like "*.example.com". If NULL, the SMTP host name is used to connect.

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.

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.

VRFY(
   c         IN OUT CONNECTION, 
   recipient VARCHAR2
) RETURN reply;

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. Also, it uses AUTH to authenticate to the server and STARTTLS to make the connection secure.

CREATE OR REPLACE PROCEDURE send_mail_2 (
    p_sender        VARCHAR2,
    p_recipient     VARCHAR2,
    p_subj          VARCHAR2,
    p_msg           VARCHAR2,
    p_mailhost      VARCHAR2,
    p_ssl_cert_path	VARCHAR2,
    p_username		VARCHAR2,
    p_password		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,ssl_cert_path => p_ssl_cert_path);
    UTL_SMTP.STARTTLS(v_conn);
    UTL_SMTP.HELO(v_conn,p_mailhost);
    UTL_SMTP.AUTH(v_conn,p_username,p_password,UTL_SMTP.NON_CLEARTEXT_PASSWORD_SCHEMES);
    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','/etc/ssl/certs/sendmail.pem','edb','edb');

← Prev

UTL_RAW

↑ Up

Built-in packages

UTL_URL

UTL_SMTP v18

Note

CONNECTION

REPLY/REPLIES

AUTH

Parameters

Note

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

STARTTLS

Parameters

VRFY

Parameters

WRITE_DATA

Parameters

Comprehensive example

← Prev

↑ Up

Next →