Email Process in Oracle APEX.
1) We need to configure the email setting at the administration level of apex .
- Log in to Oracle Application Express Administration Services. Application Express Administration Services".
- Click Manage Instance.
- Under Instance Settings, click Instance Settings.
- Under Email, enter the following:
- SMTP Host Address - Defines the server address of the SMTP server. By default on installation, this is set to local host. If you are using another server as an SMTP relay, change this parameter to that server's address.
- SMTP Host Port - Defines the port the SMTP server listens to for mail requests. The default setting is 25.
- Administration Email Address - Defines the from address for administrative tasks that generate email, such as approving a provision request or resetting a password.
- Notification Email Address - Enter the email address to receive notification email messages for new workspace requests and change workspace requests. If not specified, no workspace request notification email messages are sent.
- Click Apply Changes.
2) Use the Email API provided by apex for sending the mails in the dev environment .
- APEX_MAIL is the Apex API which can be used for sending the email notification in apex once the instance is configured.
- This package is built on the UTL_SMTP package; hence UTL_SMTP must be installed and functioning in order to use APEX_MAIL.
- There are three sub programs which are used in the package.
- ADD_ATTACHMENT Procedure
- PUSH_QUEUE Procedure
- SEND 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.
APEX_MAIL.ADD_ATTACHMENT(p_mail_id IN NUMBER,
p_attachment IN BLOB,
p_filename IN VARCHAR2,
p_mime_type IN VARCHAR2);
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.
A BLOB variable containing the binary content to be attached to the email message.
The filename associated with the email attachment.
A valid MIME type (or Internet media type) to associate with the email attachment.
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
Valid email address to which the email will be sent (required). For multiple email addresses, use a comma-separated list
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
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.
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)
Subject of the email
Valid email addresses to which the email is copied. For multiple email addresses, use a comma-separated list
Valid email addresses to which the email is blind copied. For multiple email addresses, use a comma-separated list
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).
Scheduling the Email.
In order to schedule the email outside of the application express for example using the DMBS_SCHEDULER pakcgae or the DBMS_JOB package .
We need to access in the following format .
for c1 in (
where application_id = p_app_id )
Sample example which we used in oracle1 env to test the email functionality is :-
l_id := APEX_MAIL.SEND(
p_to => 'firstname.lastname@example.org',
p_from => ‘email@example.com',
p_subj => 'APEX_MAIL with attachment',
p_body => 'Please review the attachment.',
p_body_html => '<b>Please</b> review the attachment');