Mail from Application Express

On the OTN forum Application Express (APEX) somebody asked: how to send mail from a report. Assume you have a report with all employees, you can select one or more rows , push a button and your mail is sent. It takes just a few steps!

The first step is to create a page with a report region, a submit button, an after submit process and an after processing branch. In the after submit process we call a database package p_send_mail. Make sure the package specification exists before creating the process otherwise you will not be able to save your process.

The package specification is:

-----------------------------------------------------------
CREATE OR REPLACE PACKAGE p_send_mail IS
 PROCEDURE submit(p_cb_arr wwv_flow_global.vc_arr2);
END;
/
-----------------------------------------------------------

We will come back on this package later.

Building the report

We build a simply report based on the query.

SELECT ename                         ename_ro
,      htmldb_item.checkbox(1,ename) ename_cb
FROM emp

The first column ename_ro displays the ename as a normal report field. The second column ename_cb displays a checkbox. The second column uses the function htmldb_item.checkbox. The first argument 1 is the form element name. The second argument is the value returned by the form element when checked. When the page is submitted APEX composes an array of employee names. The name of this array is htmldb_application.g_f01 (g_f01 corresponds with the form element name 1) and it only contains the employee names of the checked rows.

Create a submit button and an 'On submit - after Computations and Validations' process. Set the value of the when button pressed attribute to the name of the submit button. Don't forget to set the messages as well. Set the process source to:

BEGIN
 p_send_mail.submit(p_cb_arr =>htmldb_application.g_f01);
END;

The procedure submit has an array of employee names as input. In the next paragraphs we will explain the package body.

Implementing the package body

The code of the procedure submit:

 PROCEDURE submit(p_cb_arr wwv_flow_global.vc_arr2)
 IS
 BEGIN
   IF p_cb_arr.COUNT > 0 THEN
     FOR i IN p_cb_arr.FIRST .. p_cb_arr.LAST
     LOOP
       send_mail(p_to => con_email_to,  
                 p_body => 'Employee ' || p_cb_arr(i) || ' selected.',  
                 p_subj => 'Mail from Application Express');
     END LOOP;
   ELSE
     -- In the case there are no checkboxes checked inform the user
     RAISE e_all_cb_unchecked;
   END IF;
 END submit;

In the procedure submit we check first If there are 0 elements in the array this means that none of the check boxes is checked. In this case raise an error to inform the user that there is no mail sent. Otherwise call for every employee the procedure send_mail.

The code for the procedure send_mail is:

 PROCEDURE send_mail(p_to IN VARCHAR2,       -- receiver
                     p_body IN VARCHAR2,     -- email body (text)
                     p_subj IN VARCHAR2)     -- subject
 IS
 BEGIN
   -- Place your email in the outbox.
   htmldb_mail.send(p_to => p_to,  
                    p_from => con_email_from,  
                    p_body => p_body,  
                    p_subj => p_subj);
   -- Send you email(s) now
   htmldb_mail.push_queue(p_smtp_hostname => con_smtp_hostname,  
                          p_smtp_portno => con_smtp_portno);
 END send_mail;

APEX provides the HTMLDB_MAIL package. We use two procedures send and push_queue. The procedure send adds an email message to the queue. And procedure push_queue sends all messages in the queue immediately.

Besides the procedures the package body contains some constants. You will have to set these constants:

• email_to is the receiver of the emails i.e scott@acme.com.


• smtp_hostname is the SMTP hostname


• smtp_portno is the portnumber of the SMTP host, default 25

The complete code of the package body is below and after compiling the package body it time to email!

Update: after reinstalling my Oracle XE database I discovered that Oracle changed the behaviour a little bit. Please read Mail from application express again as well.

-----------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY p_send_mail IS
 -- Change these settings
 -- receiver
 con_email_to        constant VARCHAR2(2000) := 'aaa@bbb.cc';
 -- sender
 con_email_from      constant VARCHAR2(2000) := 'XE';

 -- You find the SMTP values in the account settings of your email client.
 con_smtp_hostname   constant VARCHAR2(2000) := 'smtp.xxx.yy';
 con_smtp_portno     constant VARCHAR2(4)    := '25';

 e_all_cb_unchecked  EXCEPTION;

 -- A simple procedure to create and send an email message.
 PROCEDURE send_mail(p_to IN VARCHAR2,       -- receiver
                     p_body IN VARCHAR2,     -- email body (text)
                     p_subj IN VARCHAR2)     -- subject
 IS
 BEGIN
   -- Place your email in the outbox.
   htmldb_mail.send(p_to => p_to,  
                    p_from => con_email_from,  
                    p_body => p_body,  
                    p_subj => p_subj);
   -- Send you email(s) now
   htmldb_mail.push_queue(p_smtp_hostname => con_smtp_hostname,  
                          p_smtp_portno => con_smtp_portno);
 END send_mail;

 -- This procedure is called from the page process.
 -- It takes as argument an array with employee names
 -- For each checked employee an email is send.
 PROCEDURE submit(p_cb_arr wwv_flow_global.vc_arr2)
 IS
 BEGIN
   IF p_cb_arr.COUNT > 0 THEN
     FOR i IN p_cb_arr.FIRST .. p_cb_arr.LAST
     LOOP
       send_mail(p_to => con_email_to,  
                 p_body => 'Employee ' || p_cb_arr(i) || ' selected.',  
                 p_subj => 'Mail from Application Express');
     END LOOP;
   ELSE
     -- In the case there are no checkboxes checked inform the user
     RAISE e_all_cb_unchecked;
   END IF;
 END submit;
END p_send_mail;
/
-----------------------------------------------------------