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

Topics:

About Configuring Oracle Application Express to Send Email
ADD_ATTACHMENT Procedure
GET_IMAGES_URL Function
GET_INSTANCE_URL Function
PUSH_QUEUE Procedure
SEND Procedure

Note:

The most efficient approach to sending email is to create a background job (using the DBMS_JOB or DBMS_SCHEDULER package) to periodically send all mail messages stored in the active mail queue. To call the APEX_MAIL package from outside the context of an Application Express application, you must call apex_util.set_security_group_id as in the following example:

for c1 in (
   select workspace_id
     from apex_applications
    where application_id = p_app_id )
loop
   apex_util.set_security_group_id(p_security_group_id =>
c1.workspace_id);
end loop;

About Configuring Oracle Application Express to Send Email

Before you can send email from an Application Builder application, you must:

Log in to Oracle Application Express Administration Services and configure the email settings on the Instance Settings page. See in Oracle Application Express Administration Guide.
If you are running Oracle Application Express with Oracle Database 11g release 1 (11.1), you must enable outbound mail. In Oracle Database 11g release 1 (11.1), the ability to interact with network services is disabled by default. See "Enabling Network Services in Oracle Database 11g" in Oracle Application Express Application Builder User's Guide.

Tip:

You can configure Oracle Application Express to automatically email users their login credentials when a new workspace request has been approved. To learn more, see "Specifying a Provisioning Mode"in Oracle Application Express Administration Guide.

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 16-1 describes the parameters available in the ADD_ATTACHMENT procedure.

Table 16-1 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 email body.
`p_attachment`	A `BLOB` variable containing the binary content to be attached to the email message.
`p_filename`	The filename associated with the email attachment.
`p_mime_type`	A valid MIME type (or Internet media type) to associate with the email attachment.

Examples

The following example demonstrates how to access files stored in APEX_APPLICATION_FILES and add them to an outbound 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,
            p_attachment => c1.blob_content,
            p_filename   => c1.filename,
            p_mime_type  => c1.mime_type);
        END LOOP;
    COMMIT;
END;
/

GET_IMAGES_URL Function

Use this function to get the image prefixed URL, if the email includes Application Express instance images.

Syntax

APEX_MAIL.GET_IMAGES_URL return VARCHAR2;

Parameters

None.

Example

The following example sends an Order Confirmation email which includes the Oracle Logo image.

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><body>' || utl_tcp.crlf ||
                   '<p>Please confirm your order on the <a href="' ||
                   apex_mail.get_instance_url || 'f?p=100:10">Order Confirmation</a> page.</p>' || utl_tcp.crlf ||
                   '<p>Sincerely,<br />' || utl_tcp.crlf ||
                   'The Application Express Dev Team<br />' || utl_tcp.crlf ||
                   '<img src="' || apex_mail.get_images_url || 'oracle.gif" alt="Oracle Logo"></p>' || utl_tcp.crlf ||
                   '</body></html>'; 
    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      => 'Order Confirmation' );
end;

GET_INSTANCE_URL Function

If an email includes a link to an Application Express instance, use this function to get the instance URL.

Note:

This function requires that the instance setting Application Express Instance URL for emails is set.

Syntax

APEX_MAIL.GET_IMAGES_URL return VARCHAR2;

Parameters

None.

Example

The following example sends an Order Confirmation email which includes an absolute URL to page 10 of application 100.

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><body>' || utl_tcp.crlf ||
                   '<p>Please confirm your order on the <a href="' ||
                   apex_mail.get_instance_url || 'f?p=100:10">Order Confirmation</a> page.</p>' || utl_tcp.crlf ||
                   '</body></html>'; 
    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      => 'Order Confirmation' );
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 NULL,
    p_smtp_portno               IN    NUMBER   DEFAULT NULL);

Parameters

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

Table 16-2 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 email.

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

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:

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 results in erroneous email messages, including partial messages or messages with extraneous exclamation points.
Plain text and HTML email 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 email clients can read an 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 email 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 email. 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 is not displayed. 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

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

Parameters

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

Table 16-3 SEND Parameters

Parameter	Description
`p_to`	Valid email address to which the email is sent (required). For multiple email addresses, use a comma-separated list
`p_from`	Email address from which the email is sent (required). This email address must be a valid address. Otherwise, the message is not 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 send these messages, but the automatic replies 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 Application Express 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 Application Express Dev Team</span><br />'||utl_tcp.crlf;
    l_body_html := l_body_html ||'</body></html>'; 
    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;
/