HTMLDB_MAIL

You can use the HTMLDB_MAIL package to send an e-mail from an Oracle HTML DB application. This package is built on top of the Oracle supplied UTL_SMTP package. Because of this dependence, the UTL_SMTP package must be installed and functioning in order to use HTMLDB_MAIL.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the UTL_SMTP package and "Sending E-mail from an Application"

HTMLDB_MAIL contains two procedures. Use HTMLDB_MAIL.SEND to send an outbound e-mail message from your application. Use HTMLDB_MAIL.PUSH_QUEUE to deliver mail messages stored in HTMLDB_MAIL_QUEUE.

Topics:

SEND Procedure
PUSH_QUEUE Procedure

Note:

The most efficient approach to sending e-mail is to create a background job (using a DBMS_JOB package) to periodically send all mail messages stored in the active mail queue.

See Also:

"Sending E-mail from an Application"

SEND Procedure

This procedure sends an outbound e-mail message from an application. Although you can use this procedure to pass in either a VARCHAR2 or a CLOB to p_body and p_body_html, the data types must be the same. In other words, you cannot pass a CLOB to P_BODY and a VARCHAR2 to p_body_html.

When using HTMLDB_MAIL.SEND, remember the following:

No single line may exceed 1000 characters. The SMTP/MIME specification dictates that no single line shall exceed 1000 characters. To comply with this restriction, you must add a carriage return or line feed characters to break up your p_body or p_body_html parameters into chunks of 1000 characters or less. Failing to do so will result in erroneous e-mail messages, including partial messages or messages with extraneous exclamation points.
Plain text and HTML e-mail content. Passing a value to p_body, but not p_body_html results in a plain text message. Passing a value to p_body and p_body_html yields a multi-part message that includes both plain text and HTML content. The settings and capabilities of the recipient's email client determine what displays. Although most modern e-mail clients can read a HTML formatted email, remember that some users disable this functionality to address security issues.
Avoid images. When referencing images in p_body_html using the <img /> tag, remember that the images must be accessible to the recipient's e-mail client in order for them to see the image.

For example, suppose you reference an image on your network called hello.gif as follows:
```
<img src="http://someserver.com/hello.gif" alt="Hello" />]
```
In this example, the image is not attached to the email, but is referenced by the e-mail. For the recipient to see it, they must be able to access the image using a Web browser. If the image is inside a firewall and the recipient is outside of the firewall, the image will not display. For this reason, avoid using images. If you must include images, be sure to include the ALT attribute to provide a textual description in the event the image is not accessible.

Syntax

HTMLDB_MAIL.SEND(
    p_to                        IN    VARCHAR2,
    p_from                      IN    VARCHAR2,
    p_body                      IN  [ VARCHAR2 | CLOB ],
    p_body_html                 IN  [ VARCHAR2 | CLOB ] DEFAULT,
    p_subj                      IN    VARCHAR2 DEFAULT)
    p_cc                        IN    VARCHAR2 DEFAULT)
    p_bcc                       IN    VARCHAR2 DEFAULT);

Parameters

Table 16-27 describes the parameters available in the SEND procedure.

Table 16-27 Send Parameters

Parameter	Description
`p_to`	Valid e-mail address to which the e-mail will be sent (required). For multiple e-mail addresses, use a comma separated list.
`p_from`	E-mail address from which the e-mail will be sent (required). This e-mail address must be a valid address. Otherwise, the message will not be sent.
`p_body`	Body of the e-mail in plain text, not HTML (required). If a value is passed to `p_body_html`, then this is the only text the recipient sees. If a value is not passed to `p_body_html`, then this text only displays for e-mail clients that do not support HTML or have HTML disabled. A carriage return or line feed (CRLF) must be included every 1000 characters.
`p_body_html`	Body of the e-mail in HTML format. This must be a full HTML document including the `<html>` and `<body>` tags. A single line cannot exceed 1000 characters without a carriage return or line feed (CRLF).
`p_subj`	Subject of the e-mail.
`p_cc`	Valid e-mail addresses to which the e-mail is copied. For multiple e-mail addresses, use a comma separated list.
`p_bcc`	Valid e-mail addresses to which the e-mail is blind copied. For multiple e-mail addresses, use a comma separated list.

Examples

The following example demonstrates how to use HTMLDB_MAIL.SEND to send a plain text e-mail message from an application.

-- Example One: Plain Text only message
DECLARE
    l_body      CLOB;
BEGIN
    l_body := 'Thank you for your interest in the HTMLDB_MAIL 
package.'||utl_tcp.crlf||utl_tcp.crlf;
    l_body := l_body ||'  Sincerely,'||utl_tcp.crlf;
    l_body := l_body ||'  The HTMLDB Dev Team'||utl_tcp.crlf;
    htmldb_mail.send(
        p_to       => 'some_user@somewhere.com',   -- change to your email address
        p_from     => 'some_sender@somewhere.com', -- change to a real senders email address
        p_body     => l_body,
        p_subj     => 'HTMLDB_MAIL Package - Plain Text message');
END;
/

The following example demonstrates how to use HTMLDB_MAIL.SEND to send a HTML e-mail message from an application. Remember, you must include a carriage return or line feed (CRLF) every 1000 characters. The example that follows uses utl_tcp.crlf.

-- Example Two: Plain Text / HTML message
DECLARE
    l_body      CLOB;
    l_body_html CLOB;
BEGIN
    l_body := 'To view the content of this message, please use an HTML enabled mail client.'||utl_tcp.crlf;

    l_body_html := '<html>
                      <head>
                        <style type="text/css">
                          body{font-family: Arial, Helvetica, sans-serif;
                               font-size:10pt;
                               margin:30px;
                               background-color:#ffffff;}

                          span.sig{font-style:italic;
                                   font-weight:bold;
                                   color:#811919;}
                        </style>
                      </head>
                      <body>'||utl_tcp.crlf;
    l_body_html := l_body_html ||'<p>Thank you for your interest in the <strong>HTMLDB_MAIL</strong> package.</p>'||utl_tcp.crlf;
    l_body_html := l_body_html ||'  Sincerely,<br />'||utl_tcp.crlf;
    l_body_html := l_body_html ||'  <span class="sig">The HTMLDB Dev Team</span><br />'||utl_tcp.crlf;
    htmldb_mail.send(
     p_to        => 'some_user@somewhere.com',   -- change to your email address
     p_from      => 'some_sender@somewhere.com', -- change to a real senders email address
     p_body      => l_body,
     p_body_html => l_body_html,
     p_subj      => 'HTMLDB_MAIL Package - HTML formatted message');
END;
/

PUSH_QUEUE Procedure

Oracle HTML DB stores unsent e-mail messages in a table named HTMLDB_MAIL_QUEUE. You can manually deliver mail messages stored in this queue to the specified SMTP gateway by invoking the HTMLDB_MAIL.PUSH_QUEUE procedure. This procedure requires two input parameters:

p_smtp_hostname defines the host name of your SMTP gateway
p_smtp_portno defines port number of your SMTP gateway (for example, 25)

Oracle HTML DB logs successfully submitted message in the table HTMLDB_MAIL_LOG with the timestamp reflecting your server's local time. Keep in mind, the most efficient approach to sending e-mail is to create a background job (using a DBMS_JOB package) to periodically send all mail messages stored in the active mail queue.

See Also:

"Sending E-mail Using a Background Job"

Syntax

HTMLDB_MAIL.PUSH_QUEUE(
    p_smtp_hostname             IN    VARCHAR2 DEFAULT,
    p_smtp_portno               IN    NUMBER   DEFAULT;

Parameters

Table 16-28 describes the parameters available in the HTMLDB_MAIL procedure.

Table 16-28 PUSH_QUEUE Parameters

Parameters	Description
p_smtp_hostname	SMTP gateway host name.
p_smtp_portno	SMTP gateway port number.

Example

The following example demonstrates the use of the HTMLDB_MAIL.PUSH_QUEUE procedure using a shell script. This example only applies to UNIX/LINUX installations. In this example, the SMTP gateway host name is defined as smtp01.oracle.com and the SMTP gateway port number is 25.

SQLPLUS / <<EOF
FLOWS_020000.HTMLDB_MAIL.PUSH_QUEUE('smtp01.oracle.com','25');
DISCONNECT
EXIT
EOF

See Also:

"Sending E-mail from an Application"