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!
-----------------------------------------------------------
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;
/
-----------------------------------------------------------
