Oracle Apex - (Security Group|Workspace Id) (Apex Context)

About

The security group id is the workspace id. Setting the security group id specify the workspace. It is especially useful when:

• you want to use an APEX function outside Apex (ie in the database) (Thanks to in/basdeklerk)
• a schema is associated with more than one workspace

Articles Related

API

APEX_UTIL.SET_SECURITY_GROUP_ID

Example

for c1 in (
   select workspace_id
     from apex_applications
    where application_id = p_app_id )
loop
   apex_util.set_security_group_id(p_security_group_id => c1.workspace_id);
end loop;

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;

Documentation / Reference

• APEX_UTIL.SET_SECURITY_GROUP_ID