Well, I went back and dug up the function I wrote. Apparently I was feeling particularly ambitious that day, because I not only wrote the function, I documented it. 
As far as I remember, this was a “throw-away” example used in a Designer training class when this very issue was brought up. It should serve as an example of how you can dynamically flatten a recursive structure.
This code was written for Oracle 7 but should work for any version of Oracle. This code uses an old version of a BusinessObjects training database which was an HR system. The table structure for the table being processed by this function is shown at the end of this post.
Dave
create or replace function report_to (start_emp char) return varchar2
is begin
declare
-- *************************************************************************
-- Function: Report_To
-- Arguements: Report_To (Employee ID)
-- Usage
-- select report_to(employees.employee_id)
-- from employees
--
-- Author Information
-- Dave Rathbun
-- Integra Solutions
-- Dallas, TX USA
-- *************************************************************************
--
-- Description
-- This function is used to construct a list of "report to's" using the
-- company hierarchy. Each employee record has a manager id that references
-- the person that the employee reports to. Using the Oracle "connect by"
-- facility, this function traverses the hierarchy and prints one line of
-- output. For example, if VANGELOS reports to JANES, and JANES reports to
-- BARRY, the output from the function would be:
-- VANGELOS works for JANES works for BARRY
--
-- Special Cases Handled:
-- 1. employee does not report to anyone
-- In this case, the function returns the employee name followed by the
-- phrase ' is the TOP DOG!'
--
-- *************************************************************************
v_emp_id employees.employee_id%type; -- obtain data types from table
v_last_name employees.last_name%type; -- for maximum code flexibility
v_once_flag boolean := false; -- flag for "one time" top boss
v_report_list varchar2(255); -- variable to build list
-- The cursor below is used to traverse the structure of the company
-- hierarchy, using the Oracle "connect by prior" facility. The starting
-- employee id is passed in to the function as a parameter.
CURSOR bossman IS
select rtrim(last_name) from employees
connect by prior manager_id = employee_id
start with employee_id = v_emp_id;
begin
v_emp_id := start_emp; -- record starting emp id
v_report_list := null; -- set list to null to start
OPEN bossman; -- Open the cursor
LOOP
FETCH bossman INTO v_last_name; -- fetch a value, and exit if
EXIT WHEN bossman%NOTFOUND; -- there is no next value found
IF v_report_list IS NULL then -- if the list is null, then this
v_report_list := rtrim(v_last_name); -- is the first name found
v_once_flag := true; -- set once flag
ELSE
v_once_flag := false; -- else, add to list
v_report_list := v_report_list || ' works for ' ||
rtrim(v_last_name);
END IF;
end LOOP;
if v_once_flag then -- if only one name processed...
v_report_list := v_report_list || ' is the TOP DOG!';
end if;
return v_report_list; -- return name list constructed
end;
end;
/
Table Structure
CREATE TABLE employees (
employee_id char(3) not null,
last_name char(20) not null,
first_name char(15) not null,
birth_date date null,
address char(35) null,
zip_code char(5) null,
city char(25) null,
telephone char(12) null,
level_id char(1) null,
start_date date null,
position_id char(2) null,
department_id integer null,
manager_id char(3) null,
country_id integer null,
evaluation char(20) null,
PRIMARY KEY (employee_id),
FOREIGN KEY (level_id) REFERENCES bolookup.education (level_id),
FOREIGN KEY (position_id) REFERENCES bolookup.positions (position_id),
FOREIGN KEY (department_id) REFERENCES bolookup.departments (department_id),
FOREIGN KEY (manager_id) REFERENCES employees (employee_id),
FOREIGN KEY (country_id) REFERENCES countries (country_id));
Table Data
/* Add employees */
INSERT INTO employees values (
'T10','BARRY','JOHN','15-NOV-48','416 EAST WASHINGTON','75043','GARLAND',
'214-934-6600','7','13-APR-88','T1', NULL ,NULL, 1, NULL);
commit;
INSERT INTO employees values (
'P73','GETZ','STAN','12-FEB-41','2543 ROCKY POINT DRIVE','75016','IRVING',
'214-978-4245','6','12-JAN-71','L1',4,'T10',1, NULL);
commit;
INSERT INTO employees values (
'K88','NGO','DAVID','10-AUG-49','3219 ESTERS','75013','CARROLTON',
'214-463-8941','5','11-AUG-80','D1',1,'T10',65,'AVERAGE');
commit;
INSERT INTO employees values (
'Z67','IOUNGOS','PEDRO','12-MAR-35','3250 FULLER DRIVE','75044','IRVING',
'214-570-3470','1','16-OCT-63','D1',3,'T10',34,'EXCELLENT');
commit;
INSERT INTO employees values (
'U77','JANES','QUINCY','10-AUG-59','9187 Main','10014','LONG ISLAND',
'607-293-0213','3','04-DEC-87','D1',2,'T10',44,'VERY GOOD');
commit;
INSERT INTO employees values (
'T56','ROSO','LOUIS','12-FEB-69','502 NORTHWEST HIGHWAY','77002','HOUSTON',
'713-617-2242','2','16-MAR-91','L2',4,'P73',1,'AVERAGE');
commit;
INSERT INTO employees values (
'P88','RICHARDS','KEITH','20-FEB-52','101 STOCKTON', '75043','GARLAND',
'214-286-9392','6','16-MAR-83','L1',4,'P73',44,'AVERAGE');
commit;
INSERT INTO employees values (
'B55','OPLIN','JEAN','03-OCT-63','2801 TOWNSGATE ROAD','75012','RICHARDSON',
'214-373-2288','4','01-MAY-87','F1',1,'K88',1,'POOR');
commit;
INSERT INTO employees values (
'X33','WAITS','THOMAS','01-FEB-73','1661 WALDROP','76011','FORT WORTH',
'214-469-7120','1','12-MAR-91','F2',1,'K88',1,'AVERAGE');
commit;
INSERT INTO employees values (
'G56','GILBERTO','EDWARD','01-AUG-64','7240 MOOREFIELD','75065','DALLAS',
'214-765-8941','1','13-MAY-82','F1',1,'K88',39,'ABOVE AVERAGE');
commit;
INSERT INTO employees values (
'U11','TOLEDO','RICHARD','12-JUN-68','2760 CENTRAL','75035','DALLAS',
'214-715-6238','2','27-JUL-89','P1',3,'Z67',34,'VERY GOOD');
commit;
INSERT INTO employees values (
'B52','WILSON','CINDY','13-MAR-44','60 BOSTON STREET','75070','COPPELL',
'214-741-0012','1','13-DEC-78','P1',3,'Z67',1,'AVERAGE');
commit;
INSERT INTO employees values (
'A45','BENJORI','GEORGE','15-DEC-53','1203 STATE STREET','10012','NEW YORK',
'607-726-2442','3','16-MAR-76','M1',2,'U77',39,'AVERAGE');
commit;
INSERT INTO employees values (
'J88','VANGELOS','RENE','12-AUG-65','9400 WEST 7TH','10017','NEW YORK',
'607-257-4559','4','22-MAR-90','M1',2,'U77',33,'EXCELLENT');
commit;
INSERT INTO employees values (
'F55','NYGUYEN','DONALD','01-NOV-70','1020 NORTH PARK','11000','LONG ISLAND',
'607-720-5621','3','16-APR-93','M3',2,'J88',852,'ABOVE AVERAGE');
commit;
INSERT INTO employees values (
'R40','BRUBECK','DAVE','12-APR-68','1935 DAVCOR','11012','RIDGEMAR',
'607-221-9238','2','03-FEB-91','M3',2,'J88',1,'AVERAGE');
commit;
INSERT INTO employees values (
'H11','VAUGHANOFF','SARAH','03-OCT-60','351 PHELPS COURT','11065','NEW YORK',
'607-718-1253','3','06-DEC-87','M2',2,'J88',1,'AVERAGE');
commit;
INSERT INTO employees values (
'U55','ROGERS','TERRY','09-SEP-33','4500 WEST PARKWAY','11022','BROOKLYN',
'607-791-9043','1','04-DEC-56','M2',2,'A45',33,'AVERAGE');
commit;
INSERT INTO employees values (
'P90','DEVISSE','EMILE','10-OCT-50','413 STATTEN','11019','NEW YORK',
'607-341-5160','1','23-DEC-71','M2',2,'A45',33,'POOR');
Dave Rathbun 
(BOB member since 2002-06-06)
(BOB member since 2002-08-08)
(BOB member since 2002-06-06)
(BOB member since 2003-02-14)
(BOB member since 2002-06-20)