Rabu, 29 Maret 2017

Query APEX Metadata out of APEX Context

For example you need to query on sql developer or execute apex_email from oracle job

example:
Select *
  from APEX_MAIL_QUEUE;

result : no row selected
Solution : Create procedure to mimic APEX context
create or replace procedure set_apex_context (
  p_app_id in number 
) as 
begin
 FOR c1 IN (
   SELECT workspace_id
    FROM apex_applications
   WHERE application_id = p_app_id
  )
 LOOP
   apex_util.set_security_group_id(p_security_group_id => c1.workspace_id);
 END LOOP;
end set_apex_context;

How to use :
before execute query run the procedure:
execute set_apex_context (140); --i40 is app id 
Select *
  from APEX_MAIL_QUEUE;