Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 7.12 UTL_SMTP

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

7.12 UTL_SMTP

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.

Table 7-20 UTL_SMTP Functions/Procedures

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.

The following table lists the public variables available in the UTL_SMTP package.

Table 7-21 UTL_SMTP Public Variables

Public Variables

Data Type

Value

Description

connection

RECORD

 

Description of an SMTP connection.

reply

RECORD

 

SMTP reply line.

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

7.12.2 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;

7.12.3 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.

7.12.4 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 Section 7.12.2 for a description of REPLY and REPLIES.

7.12.5 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 Section 7.12.2 for a description of REPLY and REPLIES.

7.12.6 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.

7.12.7 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.

7.12.8 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.

7.12.9 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 Section 7.12.2 for a description of REPLY and REPLIES.

7.12.10 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 e-mail address.

parameters

Mail command parameters in the format, key=value as defined in RFC 1869, Section 6.

7.12.11 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 Section 7.12.2 for a description of REPLY and REPLIES.

7.12.12 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.

7.12.13 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.

7.12.14 QUIT

The QUIT procedure closes the session with an SMTP server.

QUIT(c IN OUT CONNECTION)

Parameters

c

SMTP connection to be terminated.

7.12.15 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, Section 6.

7.12.16 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.

7.12.17 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 Section 7.12.2 for a description of REPLY and REPLIES.

7.12.18 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.

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

Previous PageTable Of ContentsNext Page