Skip Headers
Oracle® Application Express API Reference
Release 3.1.2

Part Number E12855-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

2 APEX_MAIL

You can use the APEX_MAIL package to send an email from an Oracle Application Express 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 APEX_MAIL.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the UTL_SMTP package

APEX_MAIL contains three procedures. Use APEX_MAIL.SEND to send an outbound email message from your application. Use APEX_MAIL.PUSH_QUEUE to deliver mail messages stored in APEX_MAIL_QUEUE. Use APEX_MAIL.ADD_ATTACHMENT to send an outbound email message from your application as an attachment.

This section contains the following topics:

Note:

The most efficient approach to sending email 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 Email from an Application" in Oracle Application Express Application Builder User's Guide

SEND Procedure

This procedure sends an outbound email 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 APEX_MAIL.SEND, remember the following:

Syntax

APEX_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);
    p_replyto                   IN    VARCHAR2 DEFAULT);
RETURN NUMBER;

Parameters

Table 2-1 describes the parameters available in the SEND procedure.

Table 2-1 SEND Parameters

Parameter Description

p_to

Valid email address to which the email will be sent (required). For multiple email addresses, use a comma-separated list

p_from

Email address from which the email will be sent (required). This email address must be a valid address. Otherwise, the message will not be sent

p_body

Body of the email 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 email 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 email 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 email

p_cc

Valid email addresses to which the email is copied. For multiple email addresses, use a comma-separated list

p_bcc

Valid email addresses to which the email is blind copied. For multiple email addresses, use a comma-separated list

p_replyto

Address of the Reply-To mail header. You can use this parameter as follows:

  • If you omit the p_replyto parameter, the Reply-To mail header is set to the value specified in the p_from parameter

  • If you include the p_replyto parameter, but provide a NULL value, the Reply-To mail header is set to NULL. This results in the suppression of automatic email replies

  • If you include p_replyto parameter, but provide a non-null value (for example, a valid email address), you will send these messages, but the automatic replies will go to the value specified (for example, the email address)


Examples

The following example demonstrates how to use APEX_MAIL.SEND to send a plain text email message from an application.

-- Example One: Plain Text only message
DECLARE
    l_body      CLOB;
BEGIN
    l_body := 'Thank you for your interest in the APEX_MAIL 
package.'||utl_tcp.crlf||utl_tcp.crlf;
    l_body := l_body ||'  Sincerely,'||utl_tcp.crlf;
    l_body := l_body ||'  The APEX Dev Team'||utl_tcp.crlf;
    apex_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     => 'APEX_MAIL Package - Plain Text message');
END;
/

The following example demonstrates how to use APEX_MAIL.SEND to send an HTML email 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>APEX_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;
    apex_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      => 'APEX_MAIL Package - HTML formatted message');
END;
/

ADD_ATTACHMENT Procedure

This procedure sends an outbound email message from an application as an attachment. To add multiple attachments to a single email, APEX_MAIL.ADD_ATTACHMENT can be called repeatedly for a single email message.

Syntax

APEX_MAIL.ADD_ATTACHMENT(
    p_mail_id                   IN    NUMBER,
    p_attachment                IN    BLOB,
    p_filename                  IN    VARCHAR2,
    p_mime_type                 IN    VARCHAR2);

Parameters

Table 2-2 describes the parameters available in the ADD_ATTACHMENT procedure.

Table 2-2 ADD_ATTACHMENT Parameters

Parameter Description

p_mail_id

The numeric ID associated with the email. This is the numeric identifier returned from the call to APEX_MAIL.SEND to compose the e-mail body.

p_attachment

A BLOB variable containing the binary content to be attached to the e-mail message.

p_filename

The filename associated with the e-mail attachment.

p_mime_type

A valid MIME type (or Internet media type) to associate with the e-mail attachment.


Examples

The following example demonstrates how to access files stored in APEX_APPLICATION_FILES and add them an email message

DECLARE
    l_id number;
BEGIN
    l_id := APEX_MAIL.SEND( p_to        => 'fred@flintstone.com',
                            p_from      => 'barney@rubble.com',
                            p_subj      => 'APEX_MAIL with attachment',
                            p_body      => 'Please review the attachment.',
                            p_body_html => '<b>Please</b> review the attachment' );

    FOR c1 IN (SELECT filename, blob_content, mime_type 
                 FROM APEX_APPLICATION_FILES
                WHERE ID IN (123,456)) loop
        --
        APEX_MAIL.ADD_ATTACHMENT( p_mail_id    => l_id,
                WHERE ID IN (123,456)) loop
        --
        APEX_MAIL.ADD_ATTACHMENT( p_mail_id    => l_id,
                                  p_attachment => c1.blob_content,
                                  p_filename   => c1.filename,
                                  p_mime_type  => c1.mime_type);
    END LOOP;
    COMMIT;
END;
/

PUSH_QUEUE Procedure

Oracle Application Express stores unsent email messages in a table named APEX_MAIL_QUEUE. You can manually deliver mail messages stored in this queue to the specified SMTP gateway by invoking the APEX_MAIL.PUSH_QUEUE procedure.

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

Syntax

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

Parameters

Table 2-3 describes the parameters available in the PUSH_QUEUE procedure.

Table 2-3 PUSH_QUEUE Parameters

Parameters Description

p_smtp_hostname

SMTP gateway host name

p_smtp_portno

SMTP gateway port number


Note that these parameter values are provided for backward compatibility, but their respective values are ignored. The SMTP gateway hostname and SMTP gateway port number are exclusively derived from values entered on the Manage Environment Settings when sending e-mail.

Example

The following example demonstrates the use of the APEX_MAIL.PUSH_QUEUE procedure using a shell script. This example only applies to UNIX/LINUX installations.

SQLPLUS / <<EOF
APEX_MAIL.PUSH_QUEUE;
DISCONNECT
EXIT
EOF

APEX_MAIL_ATTACHMENTS View

You can use the APEX_MAIL_ATTACHMENTS view in conjunction with the existing APEX_MAIL_QUEUE to access email attachments associated with email messages in the Oracle Application Express mail queue.

Example

The following example demonstrates how to access files stored in APEX_APPLICATION_FILES and add to an e-mail message.

DECLARE
    l_id number;
BEGIN
   l_id := apex_mail.send( p_to        => 'fred@flintstone.com',
                           p_from      => 'barney@rubble.com',
                           p_subj      => 'APEX_MAIL with attachment',
                           p_body      => 'Please review the attachment.',
                           p_body_html => '<b>Please</b> review the attachment' );

    FOR c1 IN (SELECT filename, blob_content, mime_type
                 FROM apex_application_files
                WHERE ID IN (123,456)) LOOP
        --
        apex_mail.add_attachment( p_mail_id => l_id,
                               p_attachment => c1.blob_content,
                                 p_filename => c1.filename,
                                p_mime_type => c1.mime_type);
  END LOOP;
  COMMIT;
END;
/