dinsdag 22 januari 2013

Using APEX send-mail, from outside APEX

Some time ago I was asked to write a piece of code that would use APEX send mail functionality, but without the APEX session (from a database job, if I recall correctly)

It turns out to be really simple, just set an APEX session, and make sure that the database-user is connected to an APEX workspace. The code is as follow:



create or replace procedure my_send_mail( p_to             in varchar2
                                        , p_from           in varchar2
                                        , p_subject        in varchar2
                                        , p_message        in varchar2
                                        , p_apex_workspace in varchar2) 
is 
 l_workspace_id    number := null;
begin
 --
 -- This procedure is possibly called from outside an APEX session. 
 -- Take note: one is required to set-up an session, before APEX_MAIL.SEND
 -- can be used. Make sure that the schema using this procedure is
 -- connected to the workpace.
 -- 
 -- Ignoring the above will raise 
 -- ORA-20001: This procedure must be invoked from within an 
 -- application session
 --
  l_workspace_id := 
    apex_util.find_security_group_id (p_workspace => p_apex_workspace);
 --
    apex_util.set_security_group_id  (p_security_group_id => l_workspace_id);
 --
  apex_mail.send(
        p_to        => p_to,
        p_from      => p_from,
        p_subj      => p_subject,
        p_body      => p_message);
 --
  apex_mail.push_queue;
end my_send_mail;
/
Calling the above procedure will give satisfying results in any PL/SQL session, as long as the schema is connected to an APEX workspace.

Geen opmerkingen:

Een reactie posten