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.
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.
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.
Create a page in Apex with
Page Number : 9000 ( for this example )
Authentication : Page is public
Create a page item
Name : P9000_CODE
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
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)