 |
|
Oracle Application Express (APEX):
sending emails from multiple select LOV
Oracle Tips by Burleson Consulting |
An APEX multiple select list of values (LOV) returns the
result in a colon-delimited string in to the item.
Here is a sample LOV definition:
select
manager_full_name fn,
manager_e_mail em
from
bc_manager
order by fn
SEE
CODE DEPOT FOR FULL SCRIPTS
And here is the APEX screen with the
multiple select list:
If
we select multiple e-mail address from a list, the selected the result will be:
manager@remote-mail.net:boss@company.cc:johnbob@remote-mail.net
To process this list, we can either write a
PL/SQL loop to do INSTRs and SUBSTRs to get the values or we can use
the APEX built-in functions to process the multiple select items.
APEX provides the string_to_table function
in the HTMLDB_UTIL package where it will take the string of values
and convert that into an array. Then you can process through the
array for the values. Here is the syntax for the APEX string to
table conversion function:
htmldb_util.string_to_table (
p_string in varchar2,
p_separator in varchar2 default ‘:’)
return
htmldb_application_global.vc_arr2;
So a call of
htmldb_util.string_to_table(‘one:two:three:four:five’)
will result in a array of
one
two
three
four
five
Here is a simple example of using APEX with
a multiple select list:
declare
list_arr2 htmldb_application_global.vc_arr2;
begin
list_arr2 := htmldb_util.string_to_table(‘One:Two:Three’);
for
i in 1..list_arr2.count
loop
htp.p(list_arr2(i));
end loop;
End;
SEE
CODE DEPOT FOR FULL SCRIPTS
For
the APEX email function (htmldb_mail), the solution is:
declare
email_recipients HTMLDB_APPLICATION_GLOBAL.VC_ARR2;
begin
email_recipients
:= HTMLDB_UTIL.STRING_TO_TABLE(:P1_E_MAIL_LOG);
FOR i
in 1..email_recipients.count
LOOP
HTMLDB_MAIL.SEND(
P_TO => email_recipients(i),
….);
END
LOOP;
End;
SEE
CODE DEPOT FOR FULL SCRIPTS
For more information on this topic, see the
book "Easy Oracle HTML-DB Application Express
", the best book anywhere on expert APEX
development:
APEX support:
 |
For APEX development support just call to gat an
Oracle Certified professional for all APEX development
projects. |
APEX book and code samples:
|
and include the URL for the page.