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.

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!

Source Article from http://dickdral.blogspot.com/2016/12/using-apexmail-outside-of-apex.html

Leave a Reply