GROUP BY a CURSOR expression

In Oracle 9.2, is it possible to GROUP BY a CURSOR expression? For example, I want to generate a dataset for a department, its maximum salary, and a list of all employees:

without employees cursor:
 select d.deptno, max(outer_emp.sal)
 from dept d, emp outer_emp
 where d.deptno = outer_emp.deptno
 group by d.deptno

works fine

add the cursor:
select d.deptno, max(outer_emp.sal),

      cursor(select * from emp e where e.deptno = d.deptno)  from dept d, emp outer_emp
 where d.deptno = outer_emp.deptno
 group by d.deptno, cursor(select * from emp e where e.deptno = d.deptno);

yields a missing expression error.

I wanted a cursor with:
dept 1, maxsal 2000, (collection of emps) dept 2, maxsal 3000, (collection of emps)

Is this possible? I can work around by making a "get_max_sal_for_dept()" function, eliminating the need for the the group by. However, a single statement with a group by seems to be a more elegant solution ... if its possible.

TIA
ggb Received on Thu Oct 14 2004 - 16:16:01 CDT