with 
DEPT as (
  select/*+ no_merge */ * from (
   select 'a1' as DCODE, 'dept 1' as DNAME from dual union all
   select 'a2' as DCODE, 'dept 2' as DNAME from dual union all
   select 'a3' as DCODE, 'dept 3' as DNAME from dual
  )
),
EPT as (
  select/*+ no_merge */ * from (
   select 'a2,a3'    as DCODES, 'ept 1' as ENAME from dual union all
   select 'a1' as DCODES, 'ept 2' as ENAME from dual union all
   select 'a3,a2' as DCODES, 'ept 3' as ENAME from dual
  )
)
select e.ENAME,d.DNAMES
  from EPT e
  outer apply 
         (select 
           listagg(dd.DNAME, '; ') within group (order by dd.N) as DNAMES
            from (select d.DNAME, p.LVL as N
                    from (select level as LVL , regexp_substr(e.DCODES, '[^,]+', 1, level) as DCODE
                            from dual
                         connect by regexp_substr(e.DCODES, '[^,]+', 1, level) is not null) p
                    join DEPT d on d.DCODE = p.DCODE
                 ) dd
         ) d;

ENAME DNAMES
----- ------------------------------
ept 1 dept 2; dept 3
ept 2 dept 1
ept 3 dept 3; dept 2