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? ).

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.


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
    return ( g_aut_code ); 

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

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

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.


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

  sys.htp.p('Mails sent.');

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

Happy Apexing!

Source Article from

Leave a Reply