D-I-Y apex_mail_log maintenance and an Apex feature request

A couple of days ago I submitted a feature request via the Apex Feature Request application because I believe that workspace administrators should be given the possibility of purging the apex mail log if they want to (see feature request AG3A). If you agree with my request, you're kindly invited to vote for it on that site.

Currently only the Apex Instance Administrator (the super administrator of the INTERNAL workspace) is able to purge the log from the apex mail log maintenance page without any options to selectively delete the data for a specific workspace or by date.

According to my experience and needs I would like to be able to purge the log for a certain workspace more frequently than others and/or remove failed emails earlier than successful ones.

That is why, while I wait for my dreams to come true, I set out and wrote the following procedure that enables me to schedule this housekeeping task and let the computer do the dirty work in my place.
In the end this is why computers were invented in the first place, isn't it?

-- the DBA is supposed to create this procedure in the APEX_TOOLS repository schema.
create or replace 
procedure apex_tools.purge_apex_mail_log (
  p_wks               in varchar2,              -- workspace name
  p_older             in date default null,     -- optional date limit
  p_error_contains    in varchar2 default null) -- optional error match string
  authid current_user                           -- invoker rights
as
  l_sgid                 number      := apex_custom_auth.get_security_group_id;
  l_dummy                char(2);
begin
  /*                    ** USE AT YOUR OWN RISK **
     Create by FC 25/7/2013
     Requirements:
     Version 1.01
      1) an APEX_TOOLS repository user must have been created:
         CREATE USER APEX_TOOLS IDENTIFIED BY "APEX_TOOLS" 
         DEFAULT TABLESPACE <tablespace> ACCOUNT LOCK;
      2) SGID must be set in advance:
         APEX_UTIL.SET_SECURITY_GROUP_ID(APEX_UTIL.FIND_SECURITY_GROUP_ID('<wks>'));
      3) caller must have GRANT DELETE rights on APEX_MAIL_LOG

      4) caller must have GRANT EXECUTE on APEX_TOOLS.PURGE_APEX_MAIL_LOG

     
     This procedure purges the portion of apex_mail_log belonging to 
     the workspace that the executing user (oracle schema) is associated with.
    
     NB: the SGID (Security Group ID) must be set BEFORE calling this
     procedure and must match with the ID associated with the symbolic name of 
     the workspace name you pass as parameter.
    
     The checks performed on the privileges held by the user running the 
     procedure are meant to avoid confusion and report errors in case of a
     misconfiguration of the parameters. 
    
     The procedure does NOT attempt to set the security ID basing on the user
     executing the procedure because that practice is forbidden by 
     the API setting the SGID when run with invoker rights.
    
*/ 
    
  if l_sgid = 0 then
    raise_application_error(-20001, 'Security Group ID is not set');
  else
    begin
      /*
       verify that the invoker is associated with the workspace by name and
       by SGID
      */
      select 'ok'
        into l_dummy
        from apex_workspace_schemas
       where schema = user
         and workspace_id = l_sgid
         and workspace_name = p_wks;
    exception
    when no_data_found then
      /*
       the query returned no rows, so you don't hold any rights on the 
       workspace passed as parameter or you entered a wrong workspace name
       that doesn't match with the SGID currently set 
      */
      raise_application_error(-20002, 'You are not authorized to access this workspace');
    end;     
  end if;
  
  /*
   delete the entries associated with the SGID currently set
   NB: this will work only if the invoker holds GRANT DELETE on APEX_MAIL_LOG
   otherwise a run time ORA-01031 error will be raised.
  */
  execute immediate 
  'delete from apex_mail_log
  where (:p_older is null or last_updated_on < :p_older)
    and (:p_error_contains is null or instr(mail_send_error, :p_error_contains)>0)'
  using p_older, p_older, p_error_contains, p_error_contains;
  /*
   reports the number of deleted rows when called interactively
  */
  dbms_output.put_line('log entries deleted: ' || to_char(SQL%ROWCOUNT));
end;
/
 
-- the DBA must give execute rights for executing this procedure to public 
-- or to specific users
GRANT EXECUTE ON apex_tools.purge_apex_mail_log TO PUBLIC;
 
-- allow selected users to perform the purging of log 
GRANT DELETE ON apex_mail_log to <user1>, <user2>...;
 

Now you are finally ready to schedule the distincts jobs for each of the relevant workspaces.
Log in as the primary schema user associated to the desired workspace and run this script (the user must have CREATE JOB rights). The script will create a job scheduled at the specified hour starting from the next day.

set verify off
accept WKS PROMPT "workspace name:"
accept DAYS NUMBER PROMPT "delete entries older than (number of days):"
accept HOUR NUMBER PROMPT "enter time of job start (hour 0-23):" 
begin
 DBMS_SCHEDULER.CREATE_JOB (
   job_name              => 'PURGE_APEX_MAIL_LOG_JOB',
   job_type              => 'PLSQL_BLOCK',
   job_action            => 
'begin 
   apex_util.set_security_group_id(apex_util.find_security_group_id(''&WKS''));
   apex_tools.purge_apex_mail_log(''&WKS'',trunc(sysdate)-&DAYS);
   commit;
 end;',
   start_date            => trunc(sysdate)+1+1/(24/&HOUR),
   repeat_interval       => 'FREQ=DAILY',
   enabled               => true,
   comments              => lower('purge &wks workspace mail log')
 );
end;

/