CREATE OR REPLACE VIEW DEMO_DEPT_LOV AS
SELECT d.deptno "NO",
d.dname "NAME",
d.loc "LOCATION",
listagg(e.ename, ', ') within GROUP(ORDER BY e.empno) "MANAGER"
FROM scott.dept d, scott.emp e
WHERE e.job(+) = 'MANAGER'
AND e.deptno(+) = d.deptno
GROUP BY d.deptno, d.dname, d.loc;