Subcursors

[Bimalaggarwal]

Do we have something called Subcursors (or called by another name, I have an example below for your reference) in any new Oracle version? One of my colleagues has come up with the following source, but we cannot simply compile it on oracle database (perhaps it is supported by some other databases).
------------
CREATE OR REPLACE procedure emp_report(p_locid number) is
TYPE refcursor is ref cursor;

cursor c1 is
select l.city,
cursor(select d.department_name,
cursor(select e.last_name
FROM employees e
WHERE e.department_id = d.department_id) as ename
FROM departments d where l.location_id = d.location_id) dname
FROM locations l
WHERE l.location_id = p_locid;

loccur refcursor;
deptcur refcursor;
empcur refcursor;

V_city locations.city%type;
V_dname departments.department_name%type;
V_ename employees.last_name%type;

BEGIN
OPEN c1;
LOOP
FETCH C1 INTO v_city, loccur;
EXIT WHEN c1%notfound;

LOOP
FETCH loccur INTO v_dname, deptcur;
EXIT WHEN loccur%notfound;
LOOP
FETCH deptcur into v_ename;
EXIT WHEN deptcur%notfound;
DBMS_OUTPUT.PUT_LINE(v_city ||' '||v_dname||' '||v_ename);
END LOOP;
END LOOP;
END LOOP;
close c1;
NULL;
END;
------------

I am writing the database objects to support the above source for your convenience:

create table locations
(city varchar2(49)
,location_id number
)
;

create table departments
( department_name varchar2(49)
, department_id number
,location_id number
)
;

create table employees
(last_name varchar2(49)
,department_id number
);

-------------------------------------------
insert into employees values ('bimal1',1);
insert into employees values ('bimal2',2);
insert into employees values ('bimal3',3);
insert into employees values ('bimal4',4);
insert into employees values ('bimal5',5);
insert into employees values ('bimal6',6);
insert into employees values ('bimal7',7);
-------------------------------------------
insert into departments values ('dept1',1,11);
insert into departments values ('dept2',2,22);
insert into departments values ('dept3',3,33);
insert into departments values ('dept4',4,44);
insert into departments values ('dept5',5,55);
insert into departments values ('dept6',6,66);
insert into departments values ('dept7',7,77);

------------------------------------------
insert into locations values ('city1',11);
insert into locations values ('city2',22);
insert into locations values ('city3',33);
insert into locations values ('city4',44);
insert into locations values ('city5',55);
insert into locations values ('city6',66);
insert into locations values ('city7',77);

 

[sem]

It's completely valid Oracle 9i syntax.

Regards, Dima