Oracle APEX Navigation Menu Query

query-23ai.sql

                             -- regexp_replace to remove badges with count (Customers [19])
with list_entries as (select regexp_replace(entry_text, ' \[&[A-Z]+\.\]') as title
                             , connect_by_root entry_image as icon
                             , regexp_substr( entry_target, '&APP_ID\.:([0-9]+):&', 1, 1, null, 1 ) as page_id
                             -- build path like Admin > Code Tables > Status
                             , substr( sys_connect_by_path( regexp_replace(entry_text, ' \[&[A-Z]+\.\]'), ' > ' ), 4 ) as path
                          from apex_application_list_entries
                         where application_id = :APP_ID
                           and list_name = 'Application Navigation'
                           and entry_target is not null
                           and (condition_type_code is null or condition_type_code = 'USER_IS_NOT_PUBLIC_USER')
                           -- remove if you are using older apex releases
                           and working_copy_name is null
                           -- might only work in 23c because the = true
                           and ( authorization_scheme is null or apex_authorization.is_authorized(authorization_scheme) = true )
                         start with list_entry_parent_id is null
                       connect by prior list_entry_id = list_entry_parent_id)
select title
     , icon
     , path
     , apex_page.get_url( p_page => page_id ) as target
  from list_entries
 where page_id is not null

query-pre23ai.sql

with
  function is_authorized_yn(p_authorization_name in varchar2) 
    return varchar2 
  is
    l_res boolean;
  begin
    l_res := apex_authorization.is_authorized(p_authorization_name);

    if l_res then
      return 'Y';
    end if;
      
    return 'N';
  end;
select title
     , icon
     , path
     , apex_page.get_url( p_page => page_id ) as target
  from (
                              -- regexp_replace to remove badges with count (Customers [19])
                        select regexp_replace(entry_text, ' \[&[A-Z]+\.\]') as title
                             , connect_by_root entry_image as icon
                             , regexp_substr( entry_target, '&APP_ID\.:([0-9]+):&', 1, 1, null, 1 ) as page_id
                             -- build path like Admin > Code Tables > Status
                             , substr( sys_connect_by_path( regexp_replace(entry_text, ' \[&[A-Z]+\.\]'), ' > ' ), 4 ) as path
                          from apex_application_list_entries
                         where application_id = :APP_ID
                           and list_name = 'Application Navigation'
                           and entry_target is not null
                           and (condition_type_code is null or condition_type_code = 'USER_IS_NOT_PUBLIC_USER')
                           -- remove if you are using older apex releases
                           and working_copy_name is null
                           -- might only work in 23c because the = true
                           and ( authorization_scheme is null or is_authorized_yn(authorization_scheme) = 'Y' )
                         start with list_entry_parent_id is null
                       connect by prior list_entry_id = list_entry_parent_id
  )
 where page_id is not null