53.127 SET_SECURITY_GROUP_ID Procedure
Use this procedure with apex_util.find_security_group_id
to ease the use of the mail package in batch mode. This procedure is especially useful when a schema is associated with more than one workspace. For example, you might want to create a procedure that is run by a nightly job to email all outstanding tasks.
Syntax
APEX_UTIL.SET_SECURITY_GROUP_ID (
p_security_group_id IN NUMBER);
Parameters
Table 53-96 SET_SECURITY_GROUP_ID Parameters
Parameter | Description |
---|---|
|
This is the security group id of the workspace you are working in. |
Example
The following example sends an alert to each user that has had a task assigned within the last day.
create or replace procedure new_tasks
is
l_workspace_id number;
l_subject varchar2(2000);
l_body clob;
l_body_html clob;
begin
l_workspace_id := apex_util.find_security_group_id (p_workspace => 'PROJECTS');
apex_util.set_security_group_id (p_security_group_id => l_workspace_id);
l_body := ' ';
l_subject := 'You have new tasks';
for c1 in (select distinct(p.email_address) email_address, p.user_id
from teamsp_user_profile p, teamsp_tasks t
where p.user_id = t.assigned_to_user_id
and t.created_on > sysdate - 1
and p.email_address is not null ) loop
l_body_html := '<p />The following tasks have been added.';
for c2 in (select task_name, due_date
from teamsp_tasks
where assigned_to_user_id = c1.user_id
and created_on > sysdate - 1 ) loop
l_body_html := l_body_html || '<p />Task: '||c2.task_name||', due '||c2.due_date;
end loop;
apex_mail.send (
p_to => c1.email_address,
p_from => c1.email_address,
p_body => l_body,
p_body_html => l_body_html,
p_subj => l_subject );
end loop;
end;
Parent topic: APEX_UTIL