Query to get a sorted list of the complete (root to child) path of an Apex list.

The case statement to determine the menu_target is not bulletproof, but it works.
Even though it should resolve to a page_id, the entry_target might contain other (javascript) code which I have not dealt with here.
Therefore I left it as a varchar2, and then outer joined it to the application_pages view.
It handles these types of targets:
  f?p=&APP_ID.:1000:&SESSION.::NO::::
and
  javascript:window.open('f?p=1234:1000:&APP_SESSION.::', '_blank')

Note that the query does not take the application_id into account - shouldn't be a problem in most cases.

Page

Name
	Page ID	1021
	Name	Apex - Lists - Hierarchy

Region

Identification
	Sequence	10
	Title	Hierarchy
	Type	Interactive Report
Source
	Source Type	DYNAMIC_QUERY
	Region Source	with m as ( select a.application_id , ltrim( sys_connect_by_path(to_char(a.display_sequence,'fm00000'), ' ^ '), ' ^ ' ) as path_sort , ltrim( sys_connect_by_path(a.entry_text, ' ^ '), ' ^ ' ) as path_text , a.entry_text , a.display_sequence , level as lvl , a.entry_target , ( case when a.entry_target like 'f?p=%' then regexp_substr(a.entry_target, '[^:]+', 1, 2) when a.entry_target like 'javascript:window.open(''f?p%' then regexp_substr(a.entry_target, '[^:=]+', 1, 4) when a.entry_target = '#' then null else a.entry_target end ) as menu_target from apex_application_list_entries a where a.application_id = nvl(v('APP_ID'), 25384) and a.list_name = 'Navigation Menu' start with a.list_entry_parent_id is null connect by prior a.list_entry_id = a.list_entry_parent_id ) select m.path_sort , m.path_text , m.entry_text , m.display_sequence , m.lvl , m.entry_target , m.menu_target , pag.page_id , pag.page_name from m left join apex_application_pages pag on (pag.application_id = m.application_id and trim(pag.page_id) = m.menu_target) order by m.path_sort