APEX

Hi,

I am calling APEX_MAIL.SEND through a block of PL/SQL that runs as a batch job using DBMS_SCHEDULER.

In order to do this we know that we need to set the security group id of one of the apex workspaces before we can call APEX_MAIL from within the database.

This is working well when I run as the SYSTEM user but does not work when I run under another user called TEST_DBA.

To debug this I ran the following block:

SET SERVEROUTPUT ON;

DECLARE
    l_workspace_id        NUMBER;
BEGIN
    l_workspace_id := apex_util.find_security_group_id (p_workspace => 'MY-WORKSPACE');  
    dbms_output.put_line('l_workspace_id :'||'l_workspace_id);
    apex_util.set_security_group_id (p_security_group_id => l_workspace_id);
END;
/

When I run this as SYSTEM I see a workspace ID returned as expected (and I can send emails) from those sessions.

l_workspace_id :3500338738936521

PL/SQL procedure successfully completed.

When I run this as my own TEST_DBA user I see that a null value has been returned from apex_util.find_security_group_id:

l_workspace_id :

PL/SQL procedure successfully completed.

This means that when I try to call APEX_MAIL.SEND from the SYSTEM user it works nicely but when I try to call it from my TEST_DBA user I get the error ORA-20001: This procedure must be invoked from within an application session.

Can anybody think of a reason why apex_util.find_security_group_id returns a valid value when invoked as the system user compared to when I run it from another user?

The setup for the user in question is:

GRANT CONNECT TO TEST_DBA;
GRANT CONNECT, RESOURCE, DBA TO TEST_DBA;
GRANT CREATE SESSION, GRANT ANY PRIVILEGE TO TEST_DBA;
GRANT UNLIMITED TABLESPACE TO TEST_DBA;

I also tried explicitly adding…

GRANT EXECUTE ON APEX_UTIL TO TEST_DBA;

….but this had no effect.

I'm a bit stumped really!

Thanks in advance for any help or ideas!