6.1.07

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

1 comment:

Rupesh said...

Good Information.Thanks Jornica.

Regds
Rupesh
crony.chum@gmail.com