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;

How to Utilize APEX Navigation List Into an Object Security

Long time ago before APEX 5 release, i am prefer using tree as main navigation. Now APEX 5 offer us more awesome style using list as main navigation.

Ok let me show you to adopt this APEX view as authorization scheme to view/access menu base on role granted by main navigation menu which is APEX_APPLICATION_LIST_ENTRIES

1. Display APEX_APPLICATION_LIST_ENTRIES as tree
• The Query

 select case when connect_by_isleaf = 1 then 0
            when level = 1             then 1
            else                           -1
        end as status
      , level
      , entry_text as title
      , entry_image as icon
      , list_entry_id as value
      , null as tooltip
      , null as link
   from APEX_APPLICATION_LIST_ENTRIES
  where application_name = 'APPS_NAME'
    and list_name = 'List name'
  start with list_entry_parent_id is null   
connect by 
  prior list_entry_id = list_entry_parent_id
  order siblings by display_sequence

• Use same icon as List
change this : Region Attributes >> Settings >> Icon Type : fa
• Display List as Tree

2. Use on role entry
3. Create Authorization scheme base on role
4. Use Authorization on pages, list