Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

with 
  resources (parent_resource, child_resource, resource_, resource_type) as (
    select 'DE Hospital', 'DE Section',     'DE Lab',        'Lab'     from dual union all
    select 'DE Lab',      'DF SubSection',  'DF Section',    'Section' from dual union all
    select 'DE Section',  'DE bench',       'DF SubSection', 'Bench'   from dual union all
    select 'DE Section',  'DF bench',       'DF SubSection', 'Bench'   from dual union all
    select 'DE Section',  'DG bench',       'DF SubSection', 'Bench'   from dual ),
  orders(order_id, resource_type, resource_) as (
    select 12345, 'SubSection', 'DF SubSection' from dual union all
    select 23456, 'Bench',      'DG bench'      from dual union all
    select 34567, 'Section',    'DE Section'    from dual ),
  hierarchy as (
    select r.*, level, connect_by_root(child_resource) root
      from resources r
      connect by prior resource_ = child_resource 
              or resource_ = prior parent_resource) 
select order_id, root, 
       max(case h.resource_type when 'Lab' then h.parent_resource end) hospital,
       max(case h.resource_type when 'Lab' then h.resource_ end) lab,
       max(case h.resource_type when 'Lab' then h.child_resource end) section,
       max(case h.resource_type when 'Section' then h.child_resource end) subsection,
       max(case h.resource_type when 'Bench' then h.child_resource end) bench
  from orders o join hierarchy h on h.root = o.resource_
  group by order_id, root order by order_id

ORDER_ID	ROOT	HOSPITAL	LAB	SECTION	SUBSECTION	BENCH
12345	DF SubSection	DE Hospital	DE Lab	DE Section	DF SubSection	null
23456	DG bench	DE Hospital	DE Lab	DE Section	DF SubSection	DG bench
34567	DE Section	DE Hospital	DE Lab	DE Section	null	null