Using APEX_MAIL outside of Apex

Update: This post applies to Oracle schema's that are not assigned to an Apex workspace. When assigned you can use the solution setting the Apex security ID. 

To send mail from your application you can use the package APEX_MAIL. I had created a package to send daily status mails and everything worked fine when called from within the application. So I thought I was almost ready... Then I tried to call the package from SQL Developer :

Error in Send daily mail: ORA-20001: This procedure must be invoked from within an application session.

A Google search quickly revealed the solution. The Apex workspace ID should be set.
Alas, this did not work ( anymore? ).
Update: This solution did not work because the Oracle schema was not asssigned to an Apex workspace.

I did not want to rewrite the logic so I had to figure out a way to call the package from within the Apex context.
It can be done using a public Apex page sending the mail. This page is called from PL/SQL using UTL_HTTP. The use of the page is restricted by using a secret parameter.
Read on to see how it works in detail.

Database

In the database we have a package mail_pck to send the mails. We add the following code to this package:

create or replace package body mail_pck is

  -- autorisation code 
  -- NB do not use special characters for URL's like '?', '&' etc.
  g_aut_code      varchar2(100) := 'secret_string';  
                                  
...

  procedure send_mails_using_apex_mail is
...

  -- return the autorisation code
  function get_aut_code  return varchar2 is
  begin
    return ( g_aut_code ); 
  end;

  -- check the autorisation code
  function check_aut_code ( p_code in varchar2) return boolean is
  begin
    return ( nvl(p_code,'x') = g_aut_code ); 
  end;

  -- calls Apex page from which mail is sent
  procedure batch_send_mails is
    l_url       varchar2(1000) := null;
    l_result    varchar2(4000) := null;
  begin
    l_url    := alg_pck.get_parameter('SERVER_URL')||'/ords/f?p='
                ||alg_pck.get_parameter('APP_ID')
                ||':9000:0::::P9000_CODE:'||get_aut_code;
    l_result := utl_http.request(l_url);
  end;

end mail_pck;

The schema for this package should be granted to execute utl_http.
An ACL should be created to access the Apex server. This ACL should be granted to the schema.

Apex

Create a page in Apex with
Page Number : 9000 ( for this example )
Authentication : Page is public

Create a page item
Name : P9000_CODE
Type: Hidden

Create a branch
This branch fires a redirect when the code is not correct
Name: To login page when code is not valid
Process Point: Before header
Type: Page or URL (Redirect )
Target page: LOGIN_DESKTOP
Condition Type: PL/SQL Expression
PL/SQL Expression: not mail_pck.check_aut_code(:P9000_CODE)


Create a PL/SQL region
This PL/SQL region performs the sending of the mails
Name: Send daily mail
Code:

begin
  mail_pck.send_mails_using_apex_mail;
  sys.htp.p('Mails sent.');
end;

Condition Type: PL/SQL Expression
PL/SQL Expression: mail_pck.check_aut_code(:P9000_CODE)


Happy Apexing!